packagecom.li.controller;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;public classWriteExcel {private static final String EXCEL_XLS = "xls";private static final String EXCEL_XLSX = "xlsx";public static voidmain(String[] args) {
Map dataMap=new HashMap();
dataMap.put("BankName", "BankName");
dataMap.put("Addr", "Addr");
dataMap.put("Phone", "Phone");
List list=new ArrayList();
list.add(dataMap);
writeExcel(list,3, "D:/writeExcel.xlsx");
}public static void writeExcel(List dataList, intcloumnCount,String finalXlsxPath){
OutputStream out= null;try{//获取总列数
int columnNumCount =cloumnCount;//读取Excel文档
File finalXlsxFile = newFile(finalXlsxPath);
Workbook workBook=getWorkbok(finalXlsxFile);//sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);/*** 删除原有数据,除了属性列*/
int rowNumber = sheet.getLastRowNum(); //第一行从0开始算
System.out.println("原始数据总行数,除属性列:" +rowNumber);for (int i = 1; i <= rowNumber; i++) {
Row row=sheet.getRow(i);
sheet.removeRow(row);
}//创建文件输出流,输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
out = newFileOutputStream(finalXlsxPath);
workBook.write(out);/*** 往Excel中写新数据*/
for (int j = 0; j < dataList.size(); j++) {//创建一行:从第二行开始,跳过属性列
Row row = sheet.createRow(j + 1);//得到要插入的每一条记录
Map dataMap =dataList.get(j);
String name= dataMap.get("BankName").toString();
String address= dataMap.get("Addr").toString();
String phone= dataMap.get("Phone").toString();for (int k = 0; k <= columnNumCount; k++) {//在一行内循环
Cell first = row.createCell(0);
first.setCellValue(name);
Cell second= row.createCell(1);
second.setCellValue(address);
Cell third= row.createCell(2);
third.setCellValue(phone);
}
}//创建文件输出流,准备输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
out = newFileOutputStream(finalXlsxPath);
workBook.write(out);
}catch(Exception e) {
e.printStackTrace();
}finally{try{if(out != null){
out.flush();
out.close();
}
}catch(IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}/*** 判断Excel的版本,获取Workbook
*@paramin
*@paramfilename
*@return*@throwsIOException*/
public static Workbook getWorkbok(File file) throwsIOException{
Workbook wb= null;
FileInputStream in= newFileInputStream(file);if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003
wb = newHSSFWorkbook(in);
}else if(file.getName().endsWith(EXCEL_XLSX)){ //Excel 2007/2010
wb = newXSSFWorkbook(in);
}returnwb;
}
}