最近因为数据迁移问题 ,需要用到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();
CellStyle cellStyle = wb.createCellStyle();
//创建标题行
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,cellStyle ));
}
//写入数据
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,cellStyle ));
}
}
//自适应列宽度
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) {
// 居中
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。如果需要 也可以看一下。