这个东西很容易懂，不是特别难，难就难在一些复杂的计算和Excel格式的调整上。
近期写了一个小列子，放上来便于以后使用。
POI.jar下载地址：http://mirror.bit.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip
Entity 实体类
package com.test2; import java.util.Date; public class User { private int id; private String username; private String password; private String sex; private int age; private Date birth; public int getId() { return id; } public String getUsername() { return username; } public String getPassword() { return password; } public String getSex() { return sex; } public int getAge() { return age; } public Date getBirth() { return birth; } public void setId(int id) { this.id = id; } public void setUsername(String username) { this.username = username; } public void setPassword(String password) { this.password = password; } public void setSex(String sex) { this.sex = sex; } public void setAge(int age) { this.age = age; } public void setBirth(Date birth) { this.birth = birth; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", password=" + password + ", sex=" + sex + ", age=" + age + ", birth=" + birth + "]"; } public User() {}; public User(int id, String username, String password, String sex, int age, Date birth) { super(); this.id = id; this.username = username; this.password = password; this.sex = sex; this.age = age; this.birth = birth; } }
Excel 导出：
package com.test2; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; 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.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; public class ExportExcel { public static void main(String[] args) { try { // 创建一个工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 5); HSSFCellStyle headStyle = getStyle(workbook,(short) 15); HSSFCellStyle colStyle = getStyle(workbook,(short) 12); //新建一个excel页签 HSSFSheet createSheet = workbook.createSheet("用户信息列表"); // 将合并表格的对象添加页签中 createSheet.addMergedRegion(cellRangeAddress); // 设置单元格的默认宽度 createSheet.setDefaultColumnWidth(25); // 创建一行 HSSFRow row0 = createSheet.createRow(0); HSSFCell cell0 = row0.createCell(0); // 添加标题样式 cell0.setCellStyle(headStyle); // 添加标题 cell0.setCellValue("用户信息列表"); //设置列的标题 String [] titles = {"id","用户名","密码","年龄","性别","生日"}; HSSFRow row1 = createSheet.createRow(1); // 循环往excel中添加列标题 for (int i = 0; i < titles.length; i++) { HSSFCell cell1 = row1.createCell(i); cell1.setCellStyle(colStyle); cell1.setCellValue(titles[i]); } List<User> userList = new ArrayList<User>(); userList.add(new User(1,"zhangsan1","123","男",21,new Date())); userList.add(new User(2,"zhangsan2","456","男",21,new Date())); userList.add(new User(3,"zhangsan3","789","女",21,new Date())); userList.add(new User(4,"zhangsan4","000","男",21,new Date())); for (int i = 0; i < userList.size(); i++) { //创建第三行 HSSFRow row2 = createSheet.createRow(i + 2); HSSFCell cell_0 = row2.createCell(0); cell_0.setCellValue(userList.get(i).getId()); HSSFCell cell_1 = row2.createCell(1); cell_1.setCellValue(userList.get(i).getUsername()); HSSFCell cell_2 = row2.createCell(2); cell_2.setCellValue(userList.get(i).getPassword()); HSSFCell cell_3 = row2.createCell(3); cell_3.setCellValue(userList.get(i).getAge()); HSSFCell cell_4 = row2.createCell(4); cell_4.setCellValue(userList.get(i).getSex()); HSSFCell cell_5 = row2.createCell(5); cell_5.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(userList.get(i).getBirth())); } OutputStream os = new FileOutputStream("E:/test.xls"); workbook.write(os); os.close(); } catch (IOException e) { e.printStackTrace(); } } public static HSSFCellStyle getStyle(HSSFWorkbook workbook,short fontSize) { // 创建样式对象 HSSFCellStyle createCellStyle = workbook.createCellStyle(); //水平居中 createCellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直居中 createCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置边框 createCellStyle.setBorderBottom(BorderStyle.THIN); createCellStyle.setBorderLeft(BorderStyle.THIN); createCellStyle.setBorderRight(BorderStyle.THIN); createCellStyle.setBorderTop(BorderStyle.THIN); // 创建一个字体对象 HSSFFont createFont = workbook.createFont(); // 设置字体的大小 createFont.setFontHeightInPoints(fontSize); // 设置字体类型 createFont.setFontName("微软雅黑"); // 设置字体的颜色 createFont.setColor(HSSFColor.RED.index); //将字体放置到样式中 createCellStyle.setFont(createFont); return createCellStyle; } }
以下是导出Excel的结果图：
这里补充下合并单元格的知识点：
/*合并单元格需要用到CellRangeAddress对象 CellRangeAddress对象需要传入四个参数 列如：CellRangeAddress(param1,param2,param3,param4)； 参数分别代表如下： param1：开始行 param2：结束行 param3：开始列 param4：结束列 例如： 合并第一行和第二行，此合并只是合并了第一列,第二列、第三列...等后面的列的一二行并没有合并 给合并的单元格赋值是需要注意行号变化 注意：记住行号和列号是从0起始 */ sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
下面粘贴一段合并单元格行和列的综合代码：
public static void main(String[] args) { try { // 创建标题栏 String[] titles = new String[] {"公司名称","项目名称","地址名称"}; String[] strDate = new String[] {"2018-04-29","2018-04-30","2018-05-01","2018-05-02","2018-05-03"}; // 创建一个工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //拿到标题样式和单元格样式 HSSFCellStyle headStyle = getStyle(workbook,(short) 15); HSSFCellStyle colStyle = getStyle(workbook,(short) 12); //新建一个excel页签 HSSFSheet createSheet = workbook.createSheet("地址信息报表"); // 将合并表格的对象添加页签中 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, (6+strDate.length)); createSheet.addMergedRegion(cellRangeAddress); //循环合并1至3列的的一二行 for (int i = 0; i < 3; i++) { createSheet.addMergedRegion(new CellRangeAddress(1, 2, i, i)); } createSheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3+strDate.length)); // 设置单元格的默认宽度 createSheet.setDefaultColumnWidth(25); // 创建一行 HSSFRow row0 = createSheet.createRow(0); HSSFCell cell0 = row0.createCell(0); // 添加标题样式 cell0.setCellStyle(headStyle); // 添加标题 cell0.setCellValue("地址信息报表"); //设置列的标题 HSSFRow row1 = createSheet.createRow(1); // 循环往excel中添加列标题 for (int i = 0; i < titles.length; i++) { HSSFCell cell1 = row1.createCell(i); cell1.setCellStyle(colStyle); cell1.setCellValue(titles[i]); } HSSFCell dateCell = row1.createCell(titles.length); dateCell.setCellStyle(colStyle); dateCell.setCellValue("2018-04-29 / 2018-05-03"); HSSFRow row2 = createSheet.createRow(2); // 循环往excel中添加列标题 for (int i = 0; i < strDate.length; i++) { HSSFCell cell1 = row2.createCell(i + titles.length); cell1.setCellStyle(colStyle); cell1.setCellValue(strDate[i]); } OutputStream os = new FileOutputStream("E:/test.xls"); workbook.write(os); os.close(); } catch (Exception e){ e.printStackTrace(); } }
结果如下：
Excel导入：
package com.test2; import java.io.FileInputStream; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * * @author zhangxiang * */ public class ImportExcel { public static void main(String[] args) { try { //创建输入流对象 InputStream inputStream = new FileInputStream("E:/test.xls"); // 创建工作波对象 HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // 创建user集合对象用于存储Excel导入的信息 List<User> userList = new ArrayList<User>(); // 是否能拿到excel页签 for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheetAt = workbook.getSheetAt(i); // 不存在就结束循环 if(null == sheetAt) { continue; } // 是否存在row for (int j = 0; j < sheetAt.getPhysicalNumberOfRows(); j++) { // 拿到第 j+2 行 前两行是标题 HSSFRow row = sheetAt.getRow(2+j); // 不存在row就结束循环 if(null == row) { continue; } // 存储一行的每个单元格拿到的值 User user = new User(); // 拿到第一个单元格的值 单元格从0开始 HSSFCell cell0 = row.getCell(0); Float f = Float.parseFloat(cell0.toString()); user.setId(f.intValue()); // 第二个单元格的值 HSSFCell cell1 = row.getCell(1); user.setUsername(cell1.toString()); //第三个 HSSFCell cell2 = row.getCell(2); user.setPassword(cell2.toString()); //第四个 HSSFCell cell3 = row.getCell(3); Float a = Float.parseFloat(cell3.toString()); user.setAge(a.intValue()); // 第五个 HSSFCell cell4 = row.getCell(4); user.setSex(cell4.toString()); //第六个 HSSFCell cell5 = row.getCell(5); user.setBirth(new SimpleDateFormat("yyyy-MM-dd").parse(cell5.toString())); userList.add(user); } } // 输出信息 查看是否正确 System.out.println(userList.toString()); } catch (Exception e) { e.printStackTrace(); } } }
以下是导入Excel 的部分打印结果图：
以上代码直接就可以运行！！