下载地址http://poi.apache.org/
org.apache.poi poi 3.12 org.apache.poi poi-ooxml 3.12
package com.zns.bean;public class Person { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Person(int id, String name) { super(); this.id = id; this.name = name; } public Person() { super(); // TODO Auto-generated constructor stub } @Override public String toString() { return "Person [id=" + id + ", name=" + name + "]"; }}
package com.zns.test;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.zns.bean.Person;public class Test2 { public static void main(String[] args) throws Exception { // func1(); // func2(); // func3(); } //导出到excel private static void func1() { Date currentTime = new Date(); SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss"); String dateStr = formatter.format(currentTime); // 导出xls用HSSFWorkbook,导出xlsx用XSSFWorkbook // String outFileName = dateStr + ".xls"; // Workbook wb = new HSSFWorkbook(); String outFileName = dateStr + ".xlsx"; Workbook wb = new XSSFWorkbook(); Sheet sheet1 = (Sheet) wb.createSheet("sheet001"); Row row = (Row) sheet1.createRow(0); String[] headers = { "编号", "姓名" }; for (int i = 0; i < headers.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(headers[i]); } List list = new ArrayList (); list.add(new Person(1, "张三")); list.add(new Person(2, "李四")); for (int i = 0; i < list.size(); i++) { row = sheet1.createRow((int) i + 1); Person obj = (Person) list.get(i); row.createCell(0).setCellValue(obj.getId()); row.createCell(1).setCellValue(obj.getName()); } try { FileOutputStream fout = new FileOutputStream("D:/" + outFileName); wb.write(fout); fout.close(); System.out.println("导出成功..."); } catch (Exception e) { e.printStackTrace(); } } //从excel导入 private static void func2() throws FileNotFoundException, IOException { // InputStream is = new FileInputStream("D://test.xls"); // Workbook wb = new HSSFWorkbook(is); InputStream is = new FileInputStream("D://test.xlsx"); Workbook wb = new XSSFWorkbook(is); List list = new ArrayList (); Sheet sheet1 = (Sheet) wb.getSheetAt(0); for (int rowNum = 1; rowNum <= sheet1.getLastRowNum(); rowNum++) { Row row = (Row) sheet1.getRow(rowNum); if (row != null) { Person obj = new Person(); Cell cell0 = row.getCell(0); cell0.setCellType(Cell.CELL_TYPE_STRING); Cell cell1 = row.getCell(1); obj.setId(Integer.parseInt(cell0.getStringCellValue())); obj.setName(cell1.getStringCellValue()); list.add(obj); } } System.out.println(list); } //从excel模板获取内容 再导出 private static void func3() throws IOException, FileNotFoundException { // excel模板路径 File fi = new File("D:\\test.xls"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi)); // 读取excel模板 HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取了模板内sheet0 HSSFSheet sheet = wb.getSheetAt(0); // 在相应的单元格进行赋值 HSSFCell cell = sheet.getRow(1).getCell(3); cell.setCellValue("aa"); HSSFCell cell2 = sheet.getRow(3).getCell(3); cell2.setCellValue("bb"); HSSFCell cell3 = sheet.getRow(0).getCell(0); cell3.setCellValue("cc"); // 修改模板内容导出新文件 FileOutputStream out = new FileOutputStream("D:/export.xls"); wb.write(out); out.close(); }}