100000 行级别数据的 Excel 导入优化之路
excel
依赖
读入和导出
public class Excel {
public static void main(String[] args) throws Exception {
List<Map<String, String>> list = readExcel("E:/excelTest.xlsx");
list.forEach(cell -> System.out.println(cell));
String[] head = new String[]{"代偿编号", "姓名", "性别", "日期", "身份证号"};
OutputStream out = new FileOutputStream("E:/excel.xlsx");
writeExcel(list, head, out);
}
public static void writeExcel(List<Map<String, String>> list, String[] head, OutputStream out) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("学生信息");
XSSFRow firstRow = sheet.createRow(0);
for (int i = 0; i < head.length; i++) {
XSSFCell cell = firstRow.createCell(i);
cell.setCellValue(head[i]);
}
for (int i = 0; i < list.size(); i++) {
XSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < head.length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellValue(list.get(i).get(head[j]));
}
}
workbook.write(out);
}
public static List<Map<String, String>> readExcel(String path) throws Exception {
List<Map<String, String>> list = new ArrayList<>();
InputStream inputStream = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheet("Sheet2");
Row firstRow = sheet.getRow(0);
int physicalNumberOfCells = firstRow.getPhysicalNumberOfCells();
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < physicalNumberOfRows; i++) {
Map<String, String> map = new HashMap<>();
Row row = sheet.getRow(i);
for (int j = 0; j < physicalNumberOfCells; j++) {
Cell cell = row.getCell(j);
int cellType = cell.getCellType();
String val = "";
if (cellType == Cell.CELL_TYPE_STRING) {
val = cell.getStringCellValue();
} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
Date dateCellValue = cell.getDateCellValue();
val = dateFormat(dateCellValue);
} else {
double numericCellValue = cell.getNumericCellValue();
val = ((Integer)new Double(numericCellValue).intValue()).toString();
}
}
map.put(firstRow.getCell(j).getStringCellValue(), val);
}
list.add(map);
}
return list;
}
private static String dateFormat(Date dateCell) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
return format.format(dateCell);
}
}
word导出(基于freamwork)
依赖
编辑word模板,另存为xml(表格数据需要手动修改xml文件)
测试代码
public class Word {
public static void main(String[] args) throws Exception {
Configuration configuration = new Configuration();
configuration.setDefaultEncoding("UTF-8");
configuration.setClassForTemplateLoading(Word.class,"/com/javasm/word");
Template template = configuration.getTemplate("购房合同.xml");
Map<String,String> map = new HashMap<>();
map.put("username","卡卡西");
map.put("IDCard","610548199604051412");
map.put("price","4836452.00");
map.put("date","2020.8.11");
Writer out = new FileWriter("E:/购房合同.docx");
template.process(map,out);
}
}