分享一个操作excel的工具类，这个工具分3个部分，
ExcelUtil.java //工具类
ExcelSheetPO.java //实体对象
ExcelVersion.java //版本枚举
可以把excel数据读取到内存中，转换为 List<List<Object>>，也可以根据List<List<Object>>转化成excel文件
依赖的jar包是poi，有用到spring框架的集合工具类，不能依赖的可以动手改改
|
1 2 3 4 5 6 7 8 |
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> </dependency> |
ExcelUtil.java
|
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 |
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.FormulaEvaluator; 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.ss.util.CellRangeAddress; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.zkingsoft.common.tools.FileUtil; /** * excel工具类 提供读取和写入excel的功能 * * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 */ public class ExcelUtil { /** * 标题样式 */ private final static String STYLE_HEADER = "header"; /** * 表头样式 */ private final static String STYLE_TITLE = "title"; /** * 数据样式 */ private final static String STYLE_DATA = "data"; /** * 存储样式 */ private static final HashMap<String, CellStyle> cellStyleMap = new HashMap<>(); /** * 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型 * * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 * @param file * @param rowCount * @param columnCount * @return * @throws FileNotFoundException * @throws IOException */ public static List<ExcelSheetPO> readExcel(File file, Integer rowCount, Integer columnCount) throws FileNotFoundException, IOException { // 根据后缀名称判断excel的版本 String extName = FileUtil.getFileExtName(file); Workbook wb = null; if (ExcelVersion.V2003.getSuffix().equals(extName)) { wb = new HSSFWorkbook(new FileInputStream(file)); } else if (ExcelVersion.V2007.getSuffix().equals(extName)) { wb = new XSSFWorkbook(new FileInputStream(file)); } else { // 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式 throw new IllegalArgumentException("Invalid excel version"); } // 开始读取数据 List<ExcelSheetPO> sheetPOs = new ArrayList<>(); // 解析sheet for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); List<List<Object>> dataList = new ArrayList<>(); ExcelSheetPO sheetPO = new ExcelSheetPO(); sheetPO.setSheetName(sheet.getSheetName()); sheetPO.setDataList(dataList); int readRowCount = 0; if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) { readRowCount = sheet.getPhysicalNumberOfRows(); } else { readRowCount = rowCount; } // 解析sheet 的行 for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) { Row row = sheet.getRow(j); if (row == null) { continue; } if (row.getFirstCellNum() < 0) { continue; } int readColumnCount = 0; if (columnCount == null || columnCount > row.getLastCellNum()) { readColumnCount = (int) row.getLastCellNum(); } else { readColumnCount = columnCount; } List<Object> rowValue = new LinkedList<Object>(); // 解析sheet 的列 for (int k = 0; k < readColumnCount; k++) { Cell cell = row.getCell(k); rowValue.add(getCellValue(wb, cell)); } dataList.add(rowValue); } sheetPOs.add(sheetPO); } return sheetPOs; } private static Object getCellValue(Workbook wb, Cell cell) { Object columnValue = null; if (cell != null) { DecimalFormat df = new DecimalFormat("0");// 格式化 number // String // 字符 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: columnValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { columnValue = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { columnValue = nf.format(cell.getNumericCellValue()); } else { columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: columnValue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: columnValue = ""; break; case Cell.CELL_TYPE_FORMULA: // 格式单元格 FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(cell); CellValue cellValue = evaluator.evaluate(cell); columnValue = cellValue.getNumberValue(); break; default: columnValue = cell.toString(); } } return columnValue; } /** * 在硬盘上写入excel文件 * * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 * @param version * @param excelSheets * @param filePath * @throws IOException */ public static void createWorkbookAtDisk(ExcelVersion version, List<ExcelSheetPO> excelSheets, String filePath) throws IOException { FileOutputStream fileOut = new FileOutputStream(filePath); createWorkbookAtOutStream(version, excelSheets, fileOut, true); } /** * 把excel表格写入输出流中，输出流会被关闭 * * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 * @param version * @param excelSheets * @param outStream * @param closeStream * 是否关闭输出流 * @throws IOException */ public static void createWorkbookAtOutStream(ExcelVersion version, List<ExcelSheetPO> excelSheets, OutputStream outStream, boolean closeStream) throws IOException { if (CollectionUtils.isNotEmpty(excelSheets)) { Workbook wb = createWorkBook(version, excelSheets); wb.write(outStream); if (closeStream) { outStream.close(); } } } private static Workbook createWorkBook(ExcelVersion version, List<ExcelSheetPO> excelSheets) { Workbook wb = createWorkbook(version); for (int i = 0; i < excelSheets.size(); i++) { ExcelSheetPO excelSheetPO = excelSheets.get(i); if (excelSheetPO.getSheetName() == null) { excelSheetPO.setSheetName("sheet" + i); } // 过滤特殊字符 Sheet tempSheet = wb.createSheet(WorkbookUtil.createSafeSheetName(excelSheetPO.getSheetName())); buildSheetData(wb, tempSheet, excelSheetPO, version); } return wb; } private static void buildSheetData(Workbook wb, Sheet sheet, ExcelSheetPO excelSheetPO, ExcelVersion version) { sheet.setDefaultRowHeight((short) 400); sheet.setDefaultColumnWidth((short) 10); createTitle(sheet, excelSheetPO, wb, version); createHeader(sheet, excelSheetPO, wb, version); createBody(sheet, excelSheetPO, wb, version); } private static void createBody(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) { List<List<Object>> dataList = excelSheetPO.getDataList(); for (int i = 0; i < dataList.size() && i < version.getMaxRow(); i++) { List<Object> values = dataList.get(i); Row row = sheet.createRow(2 + i); for (int j = 0; j < values.size() && j < version.getMaxColumn(); j++) { Cell cell = row.createCell(j); cell.setCellStyle(getStyle(STYLE_DATA, wb)); cell.setCellValue(values.get(j).toString()); } } } private static void createHeader(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) { String[] headers = excelSheetPO.getHeaders(); Row row = sheet.createRow(1); for (int i = 0; i < headers.length && i < version.getMaxColumn(); i++) { Cell cellHeader = row.createCell(i); cellHeader.setCellStyle(getStyle(STYLE_HEADER, wb)); cellHeader.setCellValue(headers[i]); } } private static void createTitle(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) { Row titleRow = sheet.createRow(0); Cell titleCel = titleRow.createCell(0); titleCel.setCellValue(excelSheetPO.getTitle()); titleCel.setCellStyle(getStyle(STYLE_TITLE, wb)); // 限制最大列数 int column = excelSheetPO.getDataList().size() > version.getMaxColumn() ? version.getMaxColumn() : excelSheetPO.getDataList().size(); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1)); } private static CellStyle getStyle(String type, Workbook wb) { if (cellStyleMap.containsKey(type)) { return cellStyleMap.get(type); } // 生成一个样式 CellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setWrapText(true); if (STYLE_HEADER == type) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font = wb.createFont(); font.setFontHeightInPoints((short) 16); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); } else if (STYLE_TITLE == type) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); } else if (STYLE_DATA == type) { style.setAlignment(HSSFCellStyle.ALIGN_LEFT); Font font = wb.createFont(); font.setFontHeightInPoints((short) 12); style.setFont(font); } cellStyleMap.put(type, style); return style; } private static Workbook createWorkbook(ExcelVersion version) { switch (version) { case V2003: return new HSSFWorkbook(); case V2007: return new XSSFWorkbook(); } return null; } } |
ExcelSheetPO.java
|
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 |
package com.zkingsoft.common.tools.excel; import java.util.List; /** * 定义表格的数据对象 * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 */ public class ExcelSheetPO { /** * sheet的名称 */ private String sheetName; /** * 表格标题 */ private String title; /** * 头部标题集合 */ private String[] headers; /** * 数据集合 */ private List<List<Object>> dataList; public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String[] getHeaders() { return headers; } public void setHeaders(String[] headers) { this.headers = headers; } public List<List<Object>> getDataList() { return dataList; } public void setDataList(List<List<Object>> dataList) { this.dataList = dataList; } public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } } |
ExcelVersion.java
|
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 |
package com.zkingsoft.common.tools.excel; /** * excel版本枚举 * * @author JIANGYOUYAO * @email 935090232@qq.com * @date 2017年12月20日 */ public enum ExcelVersion { /** * 虽然V2007版本支持最大支持1048575 * 16383 ， * V2003版支持65535*255 * 但是在实际应用中如果使用如此庞大的对象集合会导致内存溢出， * 因此这里限制最大为10000*100，如果还要加大建议先通过单元测试进行性能测试。 * 1000*100 全部导出预计时间为27s左右 */ V2003(".xls", 10000, 100), V2007(".xlsx", 100, 100); private String suffix; private int maxRow; private int maxColumn; ExcelVersion(String suffix, int maxRow, int maxColumn) { this.suffix = suffix; this.maxRow = maxRow; this.maxColumn = maxColumn; } public String getSuffix() { return this.suffix; } public int getMaxRow() { return maxRow; } public void setMaxRow(int maxRow) { this.maxRow = maxRow; } public int getMaxColumn() { return maxColumn; } public void setMaxColumn(int maxColumn) { this.maxColumn = maxColumn; } public void setSuffix(String suffix) { this.suffix = suffix; } } |