java mvc 导入_Spring MVC 实现Excel的导入导出功能(1：Excel的导入)

packagecom.ssm.demo.utils;importorg.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.xssf.usermodel.*;importorg.springframework.util.StringUtils;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;import java.io.*;importjava.text.DecimalFormat;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;/*** author:13

* date:2018-07*/

public classPoiUtil {//excel默认宽度；

private static int width = 512 * 14;//默认字体

private static String excelfont = "微软雅黑";/***@paramexcelName 导出的EXCEL名字

*@paramheaders 导出的表格的表头

*@paramfileds 导出的数据 map.get(key) 对应的 key

*@paramformators 导出数据的样式

*@paramwidths 表格的列宽度 默认为 512 * 14

*@paramdata 数据集 List

*@paramresponse

*@throwsIOException*/

public static void exportFile(String excelName, String[] headers, String[] fileds, int[] formators, int[] widths, List> data, HttpServletRequest request, HttpServletResponse response) throwsIOException {if (widths == null) {

widths= new int[fileds.length];for (int i = 0; i < fileds.length; i++) {

widths[i]=width;

}

}if (formators == null) {

formators= new int[fileds.length];for (int i = 0; i < fileds.length; i++) {

formators[i]= 1;

}

}//设置文件名

String fileName = "导出数据";if (!StringUtils.isEmpty(excelName)) {

fileName=excelName;

}//创建工作薄

XSSFWorkbook wb = newXSSFWorkbook();//创建sheet

XSSFSheet sheet = wb.createSheet("sheet1");//创建表头，没有则跳过此步骤

int headerrow = 0;if (headers != null) {

XSSFRow row=sheet.createRow(headerrow);//表头样式

XSSFCellStyle style =wb.createCellStyle();

XSSFFont font=wb.createFont();

font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

font.setFontName(excelfont);

font.setFontHeightInPoints((short) 11);

style.setFont(font);

style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

style.setBorderBottom(XSSFCellStyle.BORDER_THIN);

style.setBorderLeft(XSSFCellStyle.BORDER_THIN);

style.setBorderRight(XSSFCellStyle.BORDER_THIN);

style.setBorderTop(XSSFCellStyle.BORDER_THIN);for (int i = 0; i < headers.length; i++) {

sheet.setColumnWidth((short) i, (short) widths[i]);

XSSFCell cell=row.createCell(i);

cell.setCellValue(headers[i]);

cell.setCellStyle(style);

}

headerrow++;

}//表格主体

if (data != null) {

List styleList= newArrayList();//列数

for (int i = 0; i < fileds.length; i++) {

XSSFCellStyle style=wb.createCellStyle();

XSSFFont font=wb.createFont();

font.setFontName(excelfont);

font.setFontHeightInPoints((short) 10);

style.setFont(font);

style.setBorderBottom(XSSFCellStyle.BORDER_THIN);

style.setBorderLeft(XSSFCellStyle.BORDER_THIN);

style.setBorderRight(XSSFCellStyle.BORDER_THIN);

style.setBorderTop(XSSFCellStyle.BORDER_THIN);if (formators[i] == 1) {

style.setAlignment(XSSFCellStyle.ALIGN_LEFT);

}else if (formators[i] == 2) {

style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

}else if (formators[i] == 3) {

style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

}else if (formators[i] == 4) {//int类型

style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));

}else if (formators[i] == 5) {//float类型

style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

}else if (formators[i] == 6) {//百分比类型

style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

}

styleList.add(style);

}for (int i = 0; i < data.size(); i++) { //行数

XSSFRow row =sheet.createRow(headerrow);

Map map=data.get(i);for (int j = 0; j < fileds.length; j++) { //列数

XSSFCell cell =row.createCell(j);

Object o=map.get(fileds[j]);if (o == null || "".equals(o)) {

cell.setCellValue("");

}else if (formators[j] == 4) {//int

cell.setCellValue((Long.valueOf((map.get(fileds[j])) + "")).longValue());

}else if (formators[j] == 5 || formators[j] == 6) {//float

cell.setCellValue((Double.valueOf((map.get(fileds[j])) + "")).doubleValue());

}else{

cell.setCellValue(map.get(fileds[j])+ "");

}

cell.setCellStyle((XSSFCellStyle) styleList.get(j));

}

headerrow++;

}

}//文件名+excel格式"xlsx"

fileName = fileName + ".xlsx";

String filename= "";try{

filename=encodeChineseDownloadFileName(request, fileName);

}catch(Exception e) {

e.printStackTrace();

}

response.setHeader("Content-disposition", filename);

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

response.setHeader("Content-disposition", "attachment;filename=" +filename);

response.setHeader("Pragma", "No-cache");

OutputStream ouputStream=response.getOutputStream();

wb.write(ouputStream);

ouputStream.flush();

ouputStream.close();

}/*** 对文件流输出下载的中文文件名进行编码以屏蔽各种浏览器版本的差异性

*

*@throwsUnsupportedEncodingException*/

public staticString encodeChineseDownloadFileName(

HttpServletRequest request, String pFileName)throwsException {

String filename= null;

String agent= request.getHeader("USER-AGENT");if (null !=agent) {if (-1 != agent.indexOf("Firefox")) {//Firefox

filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";

}else if (-1 != agent.indexOf("Chrome")) {//Chrome

filename = new String(pFileName.getBytes(), "ISO8859-1");

}else {//IE7+

filename = java.net.URLEncoder.encode(pFileName, "UTF-8");

filename= filename.replace("+", "%20");

}

}else{

filename=pFileName;

}returnfilename;

}/*** 获取sheet对象

*

*@paramfile

*@return

*/

public staticXSSFSheet getXSSFSheet(File file) {

InputStream is= null;

XSSFWorkbook xssfWorkbook= null;try{

is= newFileInputStream(file);

xssfWorkbook= newXSSFWorkbook(is);

}catch(IOException e) {return null;

}//获取工作表对象

XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);returnxssfSheet;

}/*** 将单元格数据转换为String

*

*@paramcell

*@return

*/

public staticString getValue(XSSFCell cell) {

String cellValue= "";if (null !=cell) {//判断数据类型，防止报错

switch(cell.getCellType()) {case XSSFCell.CELL_TYPE_NUMERIC: //数字

DecimalFormat df = new DecimalFormat("0");

cellValue=df.format(cell.getNumericCellValue());break;case XSSFCell.CELL_TYPE_STRING: //字符串

cellValue =cell.getStringCellValue();break;case XSSFCell.CELL_TYPE_BOOLEAN: //Boolean

cellValue = cell.getBooleanCellValue() + "";break;case XSSFCell.CELL_TYPE_FORMULA: //公式

cellValue = cell.getCellFormula() + "";break;case XSSFCell.CELL_TYPE_BLANK: //空值

cellValue = "";break;case XSSFCell.CELL_TYPE_ERROR: //故障

cellValue = "非法字符";break;default:

cellValue= "未知类型";break;

}

}returncellValue;

}

}