原!操作 excel 03/07

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381

参考 所用jar包：/*  * Project: fusion-may-open-fusion-base  *  * File Created at 2018年4月28日  *  * Copyright 2016 CMCC Corporation Limited.  * All rights reserved.  *  * This software is the confidential and proprietary information of  * ZYHY Company. ("Confidential Information").  You shall not  * disclose such Confidential Information and shall use it only in  * accordance with the terms of the license.  */ package com.cmcc.open.ss.util;   import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;   import javax.servlet.http.HttpServletResponse;   import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook;   import com.cmcc.open.framework.utils.TimeUtil;   /**  * @Type ExcelHelper.java  * @Desc 支持excel 03/07版本  * @author xxx  * @date 2018年4月28日 上午11:20:49  * @version  */ public class ExcelHelper {       /**      * 读取excel文件内容(适合 第一行为大标题，第二行是列名行)      * @param f      * @return      */     public static List<List<Map<String, String>>> readExcelContent(File f, String fileName) {           String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());         InputStream is = null;         Workbook wb = null;         List<List<Map<String, String>>> result = new ArrayList<List<Map<String, String>>>();//对应excel文件          try {             is = new FileInputStream(f.getAbsoluteFile());             if (fileType.equals("xls")) {                 wb = new HSSFWorkbook(is);             } else if (fileType.equals("xlsx")) {                 wb = new XSSFWorkbook(f.getAbsoluteFile());             }               int sheetSize = wb.getNumberOfSheets();             for (int i = 0; i < sheetSize; i++) {//遍历sheet页                  Sheet sheet = wb.getSheetAt(i);                 List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();//对应sheet页                  List<String> titles = new ArrayList<String>();//放置所有的标题                    int rowSize = sheet.getLastRowNum() + 1;                 for (int j = 0; j < rowSize; j++) {//遍历行                      Row row = sheet.getRow(j);                     if (row == null) {//略过空行                          continue;                     }                     if (j == 0) {                         continue;//第一行 大标题                     }                     int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列                      if (j == 1) {//第二行  标题行  (列名行)                         for (int k = 0; k < cellSize; k++) {                             Cell cell = row.getCell(k);                             titles.add(cell.toString());                         }                     } else {//其他行是数据行                          Map<String, String> rowMap = new HashMap<String, String>();//对应一个数据行                          for (int k = 0; k < titles.size(); k++) {                             Cell cell = row.getCell(k);                             String key = titles.get(k).trim();                             String value = null;                             if (cell != null) {                                 int cellType = cell.getCellType();                                 switch (cellType) {                                     case Cell.CELL_TYPE_STRING: // 文本                                          value = cell.getRichStringCellValue().getString().trim();                                         break;                                     case Cell.CELL_TYPE_NUMERIC: // 数字、日期                                          if (DateUtil.isCellDateFormatted(cell)) {                                             value = TimeUtil.getFormatDateTime(                                                     cell.getDateCellValue(),                                                     TimeUtil.TIME_FORMAT2_24);                                         } else {                                             cell.setCellType(Cell.CELL_TYPE_STRING);                                             value = String.valueOf(cell.getRichStringCellValue()                                                     .getString().trim());                                         }                                         break;                                     case Cell.CELL_TYPE_BOOLEAN: // 布尔型                                          value = String.valueOf(cell.getBooleanCellValue());                                         break;                                     case Cell.CELL_TYPE_BLANK: // 空白                                          value = "";                                         //                                        value = cell.getStringCellValue();                                         break;                                     case Cell.CELL_TYPE_ERROR: // 错误                                          value = "错误#";                                         break;                                     case Cell.CELL_TYPE_FORMULA: // 公式                                          // 得到对应单元格的公式                                          //cellValue = cell.getCellFormula() + "#";                                          // 得到对应单元格的字符串                                          cell.setCellType(Cell.CELL_TYPE_STRING);                                         value = String                                                 .valueOf(cell.getRichStringCellValue().getString());                                         break;                                     default:                                         value = "#";                                 }                                 //                                value = cell.toString().trim();                             }                             rowMap.put(key, value);                         }                         sheetList.add(rowMap);                     }                 }                 result.add(sheetList);             }           } catch (Exception e) {             e.printStackTrace();         } finally {             try {                 if (wb != null) {                     wb.close();                 }                 if (is != null) {                     is.close();                 }             } catch (Exception e2) {                 e2.printStackTrace();             }         }           return result;     }       /**      *      * @param title 第一行 例如：码号模板xxx      * @param header 标题行      * @param list 数据集合      */     public static HSSFWorkbook writeExcel(String title, String[] header, List<String[]> dataList) {           HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象         try {             HSSFSheet sheet = workbook.createSheet("Sheet1"); // 创建工作表             // 产生表格标题行             HSSFRow rowm = sheet.createRow(0);             HSSFCell bigTitle = rowm.createCell(0);               //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】             HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象             HSSFCellStyle style = getStyle(workbook); //单元格样式对象               sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (header.length - 1)));             bigTitle.setCellStyle(columnTopStyle);             bigTitle.setCellValue(title);               // 定义所需列数             int columnNum = header.length;             HSSFRow rowRowName = sheet.createRow(1); // 在索引1的位置创建行(最顶端的行开始的第二行)               // 将列头设置到sheet的单元格中             for (int n = 0; n < columnNum; n++) {                 HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格                 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型                 HSSFRichTextString text = new HSSFRichTextString(header[n]);                 cellRowName.setCellValue(text); //设置列头单元格的值                 //                cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式                 cellRowName.setCellStyle(style); //设置列头单元格样式                 sheet.setColumnWidth(n, 18 * 256); //设置列宽             }               //将查询出的数据设置到sheet对应的单元格中             for (int i = 0; i < dataList.size(); i++) {                   String[] strArr = dataList.get(i);//遍历每个对象                 HSSFRow row = sheet.createRow(i + 2);//创建所需的行数                 HSSFCell numCell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);//序号列                 numCell.setCellValue(i + 1);                 numCell.setCellStyle(style); //设置单元格样式                   for (int j = 0; j < strArr.length; j++) {                     HSSFCell cell = null; //设置单元格的数据类型                     cell = row.createCell(j + 1, HSSFCell.CELL_TYPE_STRING);                     if (strArr[j] != null) {                         cell.setCellValue(strArr[j].toString()); //设置单元格的值                     }else{                         cell.setCellValue(""); //设置单元格的值                     }                     cell.setCellStyle(style); //设置单元格样式                 }             }             //让列宽随着导出的列长自动适应             /*for (int colNum = 0; colNum < columnNum; colNum++) {                 int columnWidth = sheet.getColumnWidth(colNum) / 256;                 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {                     HSSFRow currentRow;                     //当前行未被使用过                     if (sheet.getRow(rowNum) == null) {                         currentRow = sheet.createRow(rowNum);                     } else {                         currentRow = sheet.getRow(rowNum);                     }                     if (currentRow.getCell(colNum) != null) {                         HSSFCell currentCell = currentRow.getCell(colNum);                         if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {                             int length = currentCell.getStringCellValue().getBytes().length;                             if (columnWidth < length) {                                 columnWidth = length;                             }                         }                     }                 }                 if (colNum == 0) {                     sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);                 } else {                     sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);                 }             }*/           } catch (Exception e) {             e.printStackTrace();         }           return workbook;     }       /*      * 列头单元格样式      */     public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {           // 设置字体         HSSFFont font = workbook.createFont();         //设置字体大小         font.setFontHeightInPoints((short) 11);         //字体加粗         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //设置字体名字         font.setFontName("Courier New");         //设置样式;         HSSFCellStyle style = workbook.createCellStyle();         //设置底边框;         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);         //设置底边框颜色;          style.setBottomBorderColor(HSSFColor.BLACK.index);         //设置左边框;           style.setBorderLeft(HSSFCellStyle.BORDER_THIN);         //设置左边框颜色;         style.setLeftBorderColor(HSSFColor.BLACK.index);         //设置右边框;         style.setBorderRight(HSSFCellStyle.BORDER_THIN);         //设置右边框颜色;         style.setRightBorderColor(HSSFColor.BLACK.index);         //设置顶边框;         style.setBorderTop(HSSFCellStyle.BORDER_THIN);         //设置顶边框颜色;          style.setTopBorderColor(HSSFColor.BLACK.index);         //在样式用应用设置的字体;          style.setFont(font);         //设置自动换行;         style.setWrapText(false);         //设置水平对齐的样式为居中对齐;          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);         //设置垂直对齐的样式为居中对齐;         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);           return style;       }       /*      * 列数据信息单元格样式     */     public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {         // 设置字体         HSSFFont font = workbook.createFont();         //设置字体大小         //font.setFontHeightInPoints((short)10);         //字体加粗         //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //设置字体名字         font.setFontName("Courier New");         //设置样式;         HSSFCellStyle style = workbook.createCellStyle();         //设置底边框;         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);         //设置底边框颜色;          style.setBottomBorderColor(HSSFColor.BLACK.index);         //设置左边框;           style.setBorderLeft(HSSFCellStyle.BORDER_THIN);         //设置左边框颜色;         style.setLeftBorderColor(HSSFColor.BLACK.index);         //设置右边框;         style.setBorderRight(HSSFCellStyle.BORDER_THIN);         //设置右边框颜色;         style.setRightBorderColor(HSSFColor.BLACK.index);         //设置顶边框;         style.setBorderTop(HSSFCellStyle.BORDER_THIN);         //设置顶边框颜色;          style.setTopBorderColor(HSSFColor.BLACK.index);         //在样式用应用设置的字体;          style.setFont(font);         //设置自动换行;         style.setWrapText(false);         //设置水平对齐的样式为居中对齐;          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);         //设置垂直对齐的样式为居中对齐;         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);           return style;       }       /**      * 输出excel文件流      * @param response      * @param fileName      * @param workbook      */     public static void exportExcel(HttpServletResponse response, String fileName,                                    HSSFWorkbook workbook) {         OutputStream out = null;         try {             response.setContentType("application/octet-stream");             response.setHeader("Content-disposition ",                     "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));             out = response.getOutputStream();             workbook.write(out);             out.flush();             out.close();         } catch (Exception e) {             e.printStackTrace();         } finally {             try {                 if (out != null) {                     out.close();                 }                 if (workbook != null) {                     workbook.close();                 }             } catch (Exception e2) {                 e2.printStackTrace();             }         }       }   }

　　poi-3.11.jar
　　poi-ooxml-3.11.jar
　　poi-ooxml-schemas-3.11.jar