Java Excel 通用导出

最近因为数据迁移问题 ，需要用到excel 导入导出 。

就看了一下这方面的资料，感觉以后肯定还要用，就趁着有点时间就把先通用的导出搞出来。

通用的导入后期有时间也会发布出来，这就是这篇文章文章的由来，废话不多说，直接贴代码。

@ResponseBody@RequestMapping(value = "/exprotExcel", method = { RequestMethod.POST })public void ExprotExcel(HttpServletResponse rep) {Criteria<SysResource> param = new Criteria<SysResource>();List<Map<String, Object>> list = sysResourceService.queryPage(param);String[] heads = {"资源名称=resourceName","资源编号=resourceId","资源类型=resourceTypeZh","资源路径=url","创建时间=createTime","创建人=createBy","父资源编号=parentResourceId","父资源名称=parentResourceName"};int dateColumnIndex = 4;String fileName = "测试文件.xls";//调用通用导出工具ExportExcelUntil.exprotExcel(list,fileName,rep,heads,dateColumnIndex);}

上面是Controller 的调用 

package com.xiong.weixin.untils;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.Serializable;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;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.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;/** *  * @author XiongYC * @date 2017年10月19日 * */public class ExportExcelUntil{private static final String UTF_8 = "UTF-8";private static final String ISO8859_1 = "ISO8859-1";private static final String YYYY_M_DDHHMMSS = "yyyy-M-dd HH:mm:ss";private static final String YYYYMDDHHMMSS = "yyyyMddHHmmss";//public static void main(InputStream fis) throws Exception {//outPrint(inputPrint());//inputPrint(fis);//}//private static List<Model> inputPrint(InputStream fis ) {//List<Model> modelList = null;//Model model = null;//try {////InputStream fis = new FileInputStream("D:\\222.xls");//POIFSFileSystem fileSystem = new POIFSFileSystem(fis);//// 创建Excel工作薄//HSSFWorkbook wb = new HSSFWorkbook(fileSystem);//// 得到第一个工作頁//HSSFSheet sheet = wb.getSheetAt(0);//if (sheet != null){//modelList = new ArrayList<Model>();////遍历多少行//for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {//HSSFRow hssfRow = sheet.getRow(rowNum);//if (hssfRow == null){//continue;//}//model = new Model();////遍历一行有多少列//for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {//HSSFCell hssfCell = hssfRow.getCell(cellNum);//if (hssfCell == null){//continue;//}//if(cellNum == 0){//model.setDistributor(getVal(hssfCell));//}else if (cellNum == 1){//model.setStartCode(getVal(hssfCell));//}else{//model.setStopCode(getVal(hssfCell));//}//}//modelList.add(model);//}//}//} catch (Exception e) {//}//return modelList;////}//private static String getVal(HSSFCell hssfCell) {//if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {//return hssfCell.getStringCellValue();//} else {//return String.valueOf(hssfCell.getNumericCellValue());//}//}//private static void outPrint(List<Model> modelList) throws Exception {//Workbook wb = new HSSFWorkbook();//String str = "测试";//Sheet sheet;//Row row;//for (int i = 1; i < modelList.size(); i++) {//sheet =wb.createSheet(""+i);////row = sheet.createRow(0);//row.createCell(0).setCellValue("经销商");//row.createCell(1).setCellValue("号码");////String  distributor = modelList.get(i).getDistributor();//String  stopCode = modelList.get(i).getStopCode();//String  startCode = modelList.get(i).getStartCode();//int temp = Integer.valueOf(stopCode)- Integer.valueOf(startCode)+1;////for (int j = 0; j <= temp; j++) {//row = sheet.createRow(j+1);//row.createCell(0).setCellValue(distributor);//row.createCell(1).setCellValue(Integer.valueOf(startCode)-1+j);//}//}//FileOutputStream fos = new FileOutputStream("D:\\测试.xls");//wb.write(fos);//fos.close();//}/** * 导出excel * @param list * @param fileName * @param rep * @param heads * @param dateColumnIndex */public static void exprotExcel(List<Map<String, Object>> list,String fileName, HttpServletResponse rep, String[] heads,int dateColumnIndex) {fileName = new SimpleDateFormat(YYYYMDDHHMMSS).format(new Date())+fileName;Workbook wb = new HSSFWorkbook();Row row;Cell cell;if(list.size()>-1){Sheet sheet= wb.createSheet();//创建标题行row = sheet.createRow(0);for (int i = 0; i < heads.length; i++) {cell = row.createCell(i);//暂时解决字段自适应列宽度中文不友好问题cell.setCellValue("         "+heads[i].split("=")[0]+"         ");cell.setCellStyle(cellStyle(wb));}//写入数据for (int i = 0; i < list.size(); i++) {row = sheet.createRow(i+1);for (int j = 0; j < heads.length; j++) {cell = row.createCell(j);String param = null;for (int k = 0; k < heads.length; k++) {if(j == k){if(k== dateColumnIndex){param = new SimpleDateFormat(YYYY_M_DDHHMMSS).format(list.get(i).get(heads[j].split("=")[1]));}else{param = String.valueOf(list.get(i).get(heads[j].split("=")[1]));}}}cell.setCellValue(param);cell.setCellStyle(cellStyle(wb));}}//自适应列宽度for (int i = 0; i < heads.length; i++) {//if (i == 0) {//sheet.setColumnWidth(i, heads[i].split("=")[0].getBytes().length*2*256);//} else {sheet.autoSizeColumn(i,true);//}}}//输出到浏览器out(wb,rep,fileName);}/** * 单元格样式 * @param wb * @return */private static CellStyle cellStyle(Workbook wb) {CellStyle  cellStyle = wb.createCellStyle();// 居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);return cellStyle;}/** * 输出到浏览器 * @param wb * @param rep * @param fileName */private static void out(Workbook wb, HttpServletResponse rep,String fileName) {OutputStream os = null ;    try {rep.setHeader("Content-Disposition", "attachment;filename="  + new String(fileName.getBytes(UTF_8),ISO8859_1));rep.setContentType("application/vnd.ms-excel;charset=utf-8");os = rep.getOutputStream();wb.write(os);os.flush();//os.close();} catch (IOException e) {e.printStackTrace();}finally{try {if(os!=null){os.close(); }} catch (IOException e) {e.printStackTrace();}}}}//class Model implements Serializable{///**// * // *///private static final long serialVersionUID = -7003708043162396435L;//private String distributor;//private String startCode;//private String stopCode;//public String getDistributor() {//return distributor;//}//public void setDistributor(String distributor) {//this.distributor = distributor;//}//public String getStartCode() {//return startCode;//}//public void setStartCode(String startCode) {//this.startCode = startCode;//}//public String getStopCode() {//return stopCode;//}//public void setStopCode(String stopCode) {//this.stopCode = stopCode;//}////}

这是导出代码。注解的是导入导出 一开始自己随便写的case。如果需要 也可以看一下。