Java关于数据表poi操作
导入pom依赖
<!-- 导入依赖-->
<!-- xls 03-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- xls 07-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- 日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.9.9</version>
</dependency>
<!-- test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
</dependency>
创建个文件测试一下
public class ExcelUtil { String PATH = "D:\\JavaClass\\mydemo\\src"; @Test public void testWrte() throws IOException { // 创建一个工作簿 // Workbook workbook = new HSSFWorkbook(); // 03 Workbook workbook = new XSSFWorkbook(); // 07 // 创建一个工作表 Sheet sheet = workbook.createSheet("统计表"); // 创建一行 Row row1 = sheet.createRow(0); // 创建一个单元格 Cell cell1 = row1.createCell(0); // 填入数据 cell1.setCellValue("新增666"); // 2 ,1 Row row2 = sheet.createRow(1); Cell cell2 = row2.createCell(0); String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell2.setCellValue(s); // 生成一张表 FileOutputStream fileOutputStream = null; fileOutputStream = new FileOutputStream(PATH + "单元测试.xls"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); System.out.println("生成完成"); } }
运行文件发现项目的根目录下会出现一个单元测试.xls的文件
读取xls文件的数据
@Test public void testRead() throws IOException{ // 读表数据 FileInputStream inputStream = new FileInputStream(PATH + "单元测试.xls"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.getSheet("统计表"); Row row = sheet.getRow(0); Cell cell = row.getCell(0); System.out.println(cell.getStringCellValue()); inputStream.close(); }
表格工具类
package com.dome.common.utils; import com.dome.common.utils.model.ExcelCM; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.springframework.stereotype.Component; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.*; /** * @author YinXiaoWei * @date 2020/11/18 17:32 * excel工具类 */ @Slf4j @Component public class ExcelUtil { /** * 导出 excel文件 * @param list 表格头文件 * @param map 表格内容 * @param sheetName 工作表名称 * @param fileName 文件名 * @param response */ public static void writeExcel(List<ExcelCM> list, List<Map<String, Object>> map, String sheetName, String fileName, HttpServletResponse response){ Workbook workBook = new XSSFWorkbook(); Sheet sheet = workBook.createSheet(sheetName); Drawing p = sheet.createDrawingPatriarch(); //表头样式 CellStyle style = workBook.createCellStyle(); // 字体 Font font = workBook.createFont(); style.setFont(font); style.setFont(font); // 居中 style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.ALIGN_CENTER); // 自适应列宽 List<Integer> columnLengthList = new ArrayList<>(15); // 创建表头 if (!list.isEmpty()) { Row rowTitle = sheet.createRow(0); for (int i = 0; i < list.size(); i++) { Cell cell = rowTitle.createCell(i); cell.setCellValue(list.get(i).getName()); // 批注 Comment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(list.get(i).getField().trim())); cell.setCellComment(comment); columnLengthList.add(list.get(i).getName().length()*2); } } // 表格内容 if (!map.isEmpty()) { for (int h = 0; h < map.size(); h++) { Row row = sheet.createRow(h + 1); for (int w = 0; w < list.size(); w++) { Cell cell = row.createCell(w); String field = list.get(w).getField(); String value = map.get(h).get(field) == null ? "" : map.get(h).get(field).toString(); if (value != null){ cell.setCellValue(value); } //列宽最大值保存 Integer width = value.length(); //中文占2个字符 if(!isEnglish(value)){ width = width*2; } if(columnLengthList.get(w) <= width){ columnLengthList.set(w,width); } } } } //列宽自适应 for (int colu = 0; colu < columnLengthList.size(); colu++) { sheet.setColumnWidth(colu, columnLengthList.get(colu) * 350); } try { outputExcel(response, fileName, workBook); } catch (Exception e) { log.error("表格导出失败"+ e); } } /** * 读文件 * @param file * @return * @throws IOException */ public static Map<String, List<String>> readExcel(MultipartFile file) throws IOException { InputStream inputStream = file.getInputStream(); if (inputStream == null){ throw new RuntimeException("读取表格失败"); } // 读取数据集 Map<String, List<String>> map = new HashMap<>(15); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); // 获取标题内容 Row rowTitle = sheet.getRow(0); List<String> list = new ArrayList<>(15); if (rowTitle != null){ int number = rowTitle.getPhysicalNumberOfCells(); for (int i = 0; i < number; i++) { Cell cell = rowTitle.getCell(i); if(cell.getCellComment() != null){ String value = cell.getCellComment().getString().toString(); list.add(value); map.put(value, new ArrayList<String>()); } } } // 表格内容 int rowNum = rowTitle.getPhysicalNumberOfCells(); int rows = sheet.getPhysicalNumberOfRows(); for (int j = 1; j < rows; j++) { Row row = sheet.getRow(j); if (row != null){ // 获取列 for (int k = 0; k < rowNum; k++) { Cell cell = row.getCell(k); // 匹配列的类型数据 if (cell != null){ int cellType = cell.getCellType(); String cellValue = ""; switch (cellType){ // string case XSSFCell.CELL_TYPE_STRING: System.out.println(cellType +"="+"String"); cellValue = cell.getStringCellValue(); break; // BOOLEAN case XSSFCell.CELL_TYPE_BOOLEAN: System.out.println(cellType +"="+"BOOLEAN"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; // BLANK case XSSFCell.CELL_TYPE_BLANK: System.out.println(cellType +"="+"BLANK"); break; // NUMERIC （数字 日期） case XSSFCell.CELL_TYPE_NUMERIC: System.out.println(cellType +"="+"NUMERIC"); if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期 Date dateCellValue = cell.getDateCellValue(); cellValue = dateCellValue.toString(); }else { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cellValue = cell.toString(); } break; // ERROR case XSSFCell.CELL_TYPE_ERROR: System.out.println(cellType +"="+"ERROR"); break; default: break; } List<String> list1 = map.get(list.get(k)); list1.add(cellValue); map.put(list.get(k), list1); } } } } if (!map.isEmpty()) { return map; } return null; } /** * 输出表格 * @param response * @param fileName * @throws Exception */ private static void outputExcel(HttpServletResponse response, String fileName, Workbook workbook) throws UnsupportedEncodingException { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8")); try { // 输出流 OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { log.error("输入表格失败" + e); } } /** * 判断英文 * @param cellValue * @return */ private static boolean isEnglish(String cellValue){ return cellValue.matches("^[a-zA-Z0-9]*"); } /** * 读取数据 * @param fieldName * @param o * @return */ private static Object getFieldValueByName(String fieldName, Object o) { try { String firstLetter = fieldName.substring(0, 1).toUpperCase(); String getter = "get" + firstLetter + fieldName.substring(1); Method method = o.getClass().getMethod(getter, new Class[] {}); Object value = method.invoke(o, new Object[] {}); return value; } catch (Exception e) { log.error("读取数据失败！" + e); } return null; } }
