[关闭]
@TedZhou 2020-10-15T02:32:40.000000Z 字数 3788 阅读 338

Java poi 读写excel工具类

java poi


依赖

  1. <dependency>
  2. <groupId>org.apache.poi</groupId>
  3. <artifactId>poi</artifactId>
  4. <version>3.14</version>
  5. </dependency>
  6. <dependency>
  7. <groupId>org.apache.poi</groupId>
  8. <artifactId>poi-ooxml</artifactId>
  9. <version>3.14</version>
  10. </dependency>

WorkbookUtils.java

  1. import java.io.FileInputStream;
  2. import java.io.FileNotFoundException;
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  6. import org.apache.poi.hssf.usermodel.HSSFFont;
  7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  8. import org.apache.poi.hssf.util.HSSFColor;
  9. import org.apache.poi.ss.usermodel.Cell;
  10. import org.apache.poi.ss.usermodel.CellStyle;
  11. import org.apache.poi.ss.usermodel.DataFormatter;
  12. import org.apache.poi.ss.usermodel.DateUtil;
  13. import org.apache.poi.ss.usermodel.Font;
  14. import org.apache.poi.ss.usermodel.FormulaEvaluator;
  15. import org.apache.poi.ss.usermodel.Row;
  16. import org.apache.poi.ss.usermodel.Sheet;
  17. import org.apache.poi.ss.usermodel.Workbook;
  18. import org.apache.poi.ss.util.WorkbookUtil;
  19. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  20. import org.apache.tomcat.util.http.fileupload.IOUtils;
  21. public class WorkbookUtils extends WorkbookUtil {
  22. // 读取excel文件
  23. public static Workbook readExcel(String filePath) {
  24. Workbook wb = null;
  25. if (filePath == null) {
  26. return null;
  27. }
  28. String extString = filePath.substring(filePath.lastIndexOf("."));
  29. InputStream is = null;
  30. try {
  31. is = new FileInputStream(filePath);
  32. if (".xls".equals(extString)) {
  33. return wb = new HSSFWorkbook(is);
  34. } else if (".xlsx".equals(extString)) {
  35. return wb = new XSSFWorkbook(is);
  36. } else {
  37. return wb = null;
  38. }
  39. } catch (FileNotFoundException e) {
  40. e.printStackTrace();
  41. } catch (IOException e) {
  42. e.printStackTrace();
  43. } finally {
  44. IOUtils.closeQuietly(is);
  45. }
  46. return wb;
  47. }
  48. //读取单元格
  49. public static Object getCellFormatValue(Cell cell, FormulaEvaluator formulaEvaluator) {
  50. if (cell == null) {
  51. return null;
  52. }
  53. Object cellValue = null;
  54. // 判断cell类型
  55. int cellType = cell.getCellType();
  56. if (cellType == Cell.CELL_TYPE_FORMULA) {
  57. cellType = formulaEvaluator.evaluateFormulaCell(cell);
  58. }
  59. switch (cellType) {
  60. case Cell.CELL_TYPE_STRING:
  61. cellValue = cell.getRichStringCellValue().getString();
  62. break;
  63. case Cell.CELL_TYPE_NUMERIC:
  64. if (DateUtil.isCellDateFormatted(cell)) {// 判断cell是否为日期格式
  65. cellValue = cell.getDateCellValue();
  66. break;
  67. }
  68. DataFormatter dataFormatter = new DataFormatter();
  69. cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
  70. break;
  71. case Cell.CELL_TYPE_BOOLEAN:
  72. cellValue = cell.getBooleanCellValue();
  73. break;
  74. default:
  75. cellValue = "";
  76. }
  77. return cellValue;
  78. }
  79. // 设置报表头样式
  80. public static CellStyle createHeadSytle(Workbook workbook) {
  81. CellStyle style1 = workbook.createCellStyle();// cell样式
  82. // 设置单元格背景色,设置单元格背景色以下两句必须同时设置
  83. style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
  84. style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
  85. // 设置单元格上、下、左、右的边框线
  86. style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  87. style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  88. style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
  89. style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
  90. Font font1 = workbook.createFont();// 创建一个字体对象
  91. font1.setBoldweight((short) 10);// 设置字体的宽度
  92. font1.setFontHeightInPoints((short) 10);// 设置字体的高度
  93. font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
  94. style1.setFont(font1);// 设置style1的字体
  95. // style1.setWrapText(true);// 设置自动换行
  96. style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
  97. style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
  98. return style1;
  99. }
  100. // 设置报表体样式
  101. public static CellStyle createCellStyle(Workbook wb) {
  102. // 设置style1的样式,此样式运用在第二行
  103. CellStyle style1 = wb.createCellStyle();// cell样式
  104. // 设置单元格上、下、左、右的边框线
  105. style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  106. style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  107. style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
  108. style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
  109. // style1.setWrapText(true);// 设置自动换行
  110. style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向)
  111. style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
  112. return style1;
  113. }
  114. }
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注