JAVA 读写Excel-白红宇

JAVA 读写Excel

阅读量：5308 次

发布时间：2019-06-14

本文共 7839 字，大约阅读时间需要 26 分钟。

ExcelUtil.java

1 package pers.kangxu.datautils.utils;  2   3 import java.io.File;  4 import java.io.FileInputStream;  5 import java.io.FileOutputStream;  6 import java.io.IOException;  7 import java.io.InputStream;  8 import java.util.ArrayList;  9 import java.util.HashMap; 10 import java.util.Iterator; 11 import java.util.List; 12 import java.util.Map; 13  14 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 15 import org.apache.poi.hssf.usermodel.HSSFFont; 16 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 17 import org.apache.poi.ss.usermodel.Cell; 18 import org.apache.poi.ss.usermodel.CellStyle; 19 import org.apache.poi.ss.usermodel.CellValue; 20 import org.apache.poi.ss.usermodel.Font; 21 import org.apache.poi.ss.usermodel.FormulaEvaluator; 22 import org.apache.poi.ss.usermodel.Row; 23 import org.apache.poi.ss.usermodel.Sheet; 24 import org.apache.poi.ss.usermodel.Workbook; 25 import org.apache.poi.ss.util.CellRangeAddress; 26  27 /** 28  *  29  *  30  *    excel 工具 31  *  32  * @author kangxu 33  * 34  */ 35 public class ExcelUtil { 36      37     /** 38      * 导出 excel 39      * @param filePath  文件全路径 40      * @param sheetName  sheet页名称 41      * @param sheetIndex 当前sheet下表  从0开始 42      * @param fileHeader 头部 43      * @param datas 内容 44      */ 45     public static void writeExcel(String filePath,String sheetName, 46                                     int sheetIndex, 47                                     String[] fileHeader, 48                                     List
     
       datas){ 49         // 创建工作簿 50         Workbook wb = new HSSFWorkbook(); 51         // 创建工作表 sheet 52         Sheet s = wb.createSheet(); 53          54         wb.setSheetName(sheetIndex, sheetName); 55          56         Row r =  s.createRow(0); 57         Cell c = null; 58         Font font = null;   59         CellStyle styleHeader = null;  60         CellStyle styleContent = null; 61          62          63         //粗体   64         font = wb.createFont();   65         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   66         // 设置头样式 67         styleHeader = wb.createCellStyle();   68         styleHeader.setFont(font);  69         styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框     70         styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框     71         styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框     72         styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  73         // 设置内容样式 74         styleContent = wb.createCellStyle(); 75         styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框     76         styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框     77         styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框     78         styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  79          80          81         //设置头 82         for(int i=0;i
      
       > readExcel(String filePath,int sheetIndex){133         List
       
        
         > mapList = new ArrayList
         
          
           >();134 // 头135 List
           
             list = new ArrayList
            
             ();136 // 137 138 int cnt = 0;139 int idx = 0;140 141 try { 142 InputStream input = new FileInputStream(filePath); //建立输入流 143 Workbook wb = null; 144 145 wb = new HSSFWorkbook(input); 146 147 // 获取sheet页148 Sheet sheet = wb.getSheetAt(sheetIndex); 149 150 Iterator
             
               rows = sheet.rowIterator(); 151 while (rows.hasNext()) { 152 Row row = rows.next();153 Iterator
              
                cells = row.cellIterator(); 154 155 Map
               
                 map = new HashMap
                
                 ();156 157 if(cnt == 0){ // 将头放进list中158 while (cells.hasNext()) { 159 Cell cell = cells.next(); 160 if(isContainMergeCell(sheet)){161 cancelMergeCell(sheet);162 }163 list.add(getStringCellValue(cell));164 }165 cnt ++;166 continue;167 168 }else {169 while (cells.hasNext()) { 170 Cell cell = cells.next(); 171 if(isContainMergeCell(sheet)){172 cancelMergeCell(sheet);173 }174 // 区别相同的头175 list = ListUtil.changeSameVal(list); 176 map.put(list.get(idx++), getStringCellValue(cell));177 }178 }179 idx = 0;180 mapList.add(map);181 182 } 183 return mapList;184 } catch (IOException ex) { 185 ex.printStackTrace(); 186 }187 return null;188 189 }190 191 /**192 * 合并单元格193 * @param sheet 当前sheet页194 * @param firstRow 开始行195 * @param lastRow 结束行196 * @param firstCol 开始列197 * @param lastCol 结束列198 */199 public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){200 if(sheet == null){201 return -1;202 }203 return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));204 }205 206 207 /**208 * 取消合并单元格209 * @param sheet210 * @param idx211 */212 public static void cancelMergeCell(Sheet sheet){213 int sheetMergeCount = sheet.getNumMergedRegions();214 for(int idx = 0; idx < sheetMergeCount;){215 CellRangeAddress range = sheet.getMergedRegion(idx);216 217 String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());218 // 取消合并单元格219 sheet.removeMergedRegion(idx);220 221 for(int rownum=range.getFirstRow();rownum
                 
                  = firstRow && row <= lastRow){257 if(column >= firstColumn && column <= lastColumn){ 258 return true; 259 } 260 }261 262 i++;263 }264 return false;265 }266 267 /**268 * 判断sheet页中是否含有合并单元格269 * @param sheet270 * @return271 */272 public static boolean isContainMergeCell(Sheet sheet){273 if(sheet == null){274 return false;275 }276 return sheet.getNumMergedRegions()>0 ? true : false;277 }278 279 /**280 * 获取指定合并单元的值281 * @param sheet282 * @param row283 * @param column284 * @return285 */286 public static String getMergeCellValue(Sheet sheet,287 int row ,int column){288 289 int sheetMergeCount = sheet.getNumMergedRegions();290 for(int i = 0; i < sheetMergeCount;){291 CellRangeAddress range = sheet.getMergedRegion(i);292 293 int firstColumn = range.getFirstColumn(); 294 int lastColumn = range.getLastColumn(); 295 int firstRow = range.getFirstRow(); 296 int lastRow = range.getLastRow();297 if(row >= firstRow && row <= lastRow){298 if(column >= firstColumn && column <= lastColumn){ 299 Row fRow = sheet.getRow(firstRow); 300 Cell fCell = fRow.getCell(firstColumn);301 302 return getStringCellValue(fCell) ; 303 } 304 }305 306 i++;307 }308 309 return null;310 }311 312 /**313 * 获取单元格的值314 * @param cell315 * @return316 */317 public static String getStringCellValue(Cell cell) { 318 String strCell = ""; 319 if(cell==null) return strCell; 320 switch (cell.getCellType()) { 321 case Cell.CELL_TYPE_STRING:322 strCell = cell.getRichStringCellValue().getString().trim(); 323 break; 324 case Cell.CELL_TYPE_NUMERIC: 325 strCell = String.valueOf(cell.getNumericCellValue()); 326 break; 327 case Cell.CELL_TYPE_BOOLEAN: 328 strCell = String.valueOf(cell.getBooleanCellValue()); 329 break; 330 case Cell.CELL_TYPE_FORMULA: 331 FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); 332 evaluator.evaluateFormulaCell(cell); 333 CellValue cellValue = evaluator.evaluate(cell); 334 strCell = String.valueOf(cellValue.getNumberValue()) ; 335 break; 336 default: 337 strCell = ""; 338 } 339 return strCell; 340 }341 342 }

调用方式如下

ExcelUtilTester.java

1 package pers.kangxu.datautils.test; 2  3 import java.util.ArrayList; 4 import java.util.List; 5  6 import pers.kangxu.datautils.utils.ExcelUtil; 7  8 public class ExcelUtilTester { 9 10     public static void main(String[] args) {11         List
     
       datas = new ArrayList
      
       ();12         datas.add(new String[]{"狗熊","母","250"});13         datas.add(new String[]{"猪粮","不明","251"});14         //ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);15         16         System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));17         18     }19 }

转载于:https://www.cnblogs.com/kangxu/p/6232587.html

你可能感兴趣的文章

【Java】剑指offer(53-2) 0到n-1中缺失的数字