java poi Excel导入导出示例

package com.execl; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; 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.dom4j.io.OutputFormat; /** * * 演示poi导出excel * @author zyq * @date 2017年7月18日 上午9:26:20 * */ public class ExportExcel { /** * 表格头部样式 : title_style<br> * 表格数据样式 : data_style<br> */ private Map<String, HSSFCellStyle> cellStyleMap = new HashMap<String, HSSFCellStyle>(); public static void main(String[] args) { // TODO Auto-generated method stub ExportExcel e = new ExportExcel(); e.excelByEventType(); } /** * 导出 示例 */ public void excelByEventType(){ String path = null; //标题列头 String[] title = {"序号","类别","内容","数量"}; // 设置编码 OutputFormat format = OutputFormat.createPrettyPrint(); format.setEncoding("UTF-8"); HSSFRow row = null; HSSFWorkbook wb =new HSSFWorkbook(); HSSFSheet sheet = null ; setCellStyleMap(wb); //模拟数据~^_^ start List<Map<String,String>> list = new ArrayList<Map<String,String>>(); for (int i = 0; i < 10; i++) { int temp = i+1; Map<String,String> model = new HashMap<String, String>(); model.put("column1", temp+""); model.put("column2", "类别" + temp); model.put("column3", "内容" + temp); model.put("column4", new Random().nextInt() + ""); list.add(model); } //模拟数据~^_^ end //初始化 列数 int columnNum = title.length; //宽度参数为excel中的列宽数值 short width = 20; //创建sheet sheet = wb.createSheet("Sheet1"); //设置列头（第一行） row = sheet.createRow(0); //创建行 for (int k=0;k<title.length;k++) { //循环标题列头 if(k==0){ sheet.setColumnWidth((short)k, (short)10 * 256); }else{ sheet.setColumnWidth((short)k, (short)width * 256); } HSSFCell cell = row.createCell(k); cell.setCellValue(title[k]); //给标题列头赋值 cell.setCellStyle(cellStyleMap.get("title_style")); } for (int i = 1;i<list.size()+1;i++) { //循环行 row = sheet.createRow(i); for(int j = 0;j<columnNum;j++){ //循环列 HSSFCell cell = row.createCell(j); cell.setCellStyle(cellStyleMap.get("data_style")); switch (j) { case 0: cell.setCellValue(list.get(i-1).get("column1")); //给列赋值 value必须是String类型 break; case 1: cell.setCellValue(list.get(i-1).get("column2")); // break; case 2: cell.setCellValue(list.get(i-1).get("column3")); break; case 3: cell.setCellValue(list.get(i-1).get("column4") == null ? "0" : list.get(i-1).get("column4").toString()); break; } } } //为文件取名(取时间，避免文件名冲突) Date nowTime = new Date(System.currentTimeMillis()); SimpleDateFormat sdFormatter = new SimpleDateFormat("yyyyMMddHHmmssSSS"); String retStrFormatNowDate = sdFormatter.format(nowTime); String fileName= retStrFormatNowDate+"_export.xls";//文件名 File file = new File("D:\\Downloads\\"); file.mkdirs(); //创建文件夹 //上传服务器路径 String filePath = "D:\\Downloads\\"+fileName; System.out.println(filePath); try { FileOutputStream writeFile = new FileOutputStream(filePath); wb.write(writeFile); writeFile.close(); } catch (IOException e1) { e1.printStackTrace(); } } /** * <pre>统一创建单元格样式集 cellStyleMap </pre> * 表格头部样式 : title_style<br> * 表格数据样式 : data_style<br> * * 避免单元格格式频繁创建，导致“样式丢失”。并提升性能。 */ private void setCellStyleMap(HSSFWorkbook wb){ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 11); HSSFDataFormat cellFormat = wb.createDataFormat(); /** * 表格头部样式 */ HSSFCellStyle title_style = wb.createCellStyle(); title_style.setFont(font); title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 title_style.setDataFormat(cellFormat.getFormat("@"));//文本格式 title_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 title_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 title_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 title_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 cellStyleMap.put("title_style", title_style); /** * 表格数据样式 */ HSSFCellStyle data_style = wb.createCellStyle(); data_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 data_style.setDataFormat(cellFormat.getFormat("@"));//文本格式 data_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 data_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 data_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 data_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 cellStyleMap.put("data_style", data_style); } }