java写入excel_使用java进行excel读取和写入

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;

}

}