特点 列出List中要导入到Excel的列名#中文名,然后就完事了。具体的看看main()你就知道有多简单了。
利用POI,本工具类可以实现Excel导出和下载,话不多说,放码过来。
package com.fantong.ois.wms.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 导出或下载Excel,
* Title: ExportXls<br>
* Description: 用#分割列与转义标题 ;标题包含*表示需要过滤该列<br>
* @author xiaour@github.com
* @createDate 2016年5月31日
* @version v1.0
*/
public class ExportXls {
private OutputStream out;//传入OutputStream表示写入到服务器,传入HttpServletResponse则直接下载
private static String tag="#";//标题与字段分割符
private static String hidden="*";//隐藏标识符
public ExportXls(OutputStream out) {
super();
this.out = out;
}
/**
*
*@category
*@throws IOException
*@createDate 2016年5月31日
* @param sheetName
* @param titles 字段名#中文列名
* @param list
* */
@SuppressWarnings({"deprecation" })
public void exportExcel(String sheetName, String[] titles, List<Map<String, Object>> list) throws IOException {
// 声明一个工作薄
@SuppressWarnings("resource")
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.TEAL.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
//产生表格标题行
HSSFRow row = sheet.createRow(0);
sheet.createFreezePane( 0, 1, 0, 1 );
for (int i = 0; i < titles.length; i++) {
if(titles[i].indexOf(hidden)<0){
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text=null;
if(titles[i].indexOf(tag)>0){
text = new HSSFRichTextString(titles[i].split(tag)[1]);
}else {
text = new HSSFRichTextString(titles[i]);
}
cell.setCellValue(text);
}
}
String value="";
for(int j=1;j<list.size()+1;j++){
Map<String, Object> map=list.get(j-1);
row = sheet.createRow(j);
for(int s=0;s<titles.length;s++){
if(titles[s].indexOf(hidden)<0){
HSSFCell txtCell = row.createCell(s);
if(map!=null){
Object obj=null;
if(titles[s].indexOf(tag)>0){
obj=map.get(titles[s].split(tag)[0]);
}else {
obj=map.get(titles[s]);
}
String temp=obj!=null?obj.toString():"";
value= temp.toString();
}
HSSFRichTextString text2 = new HSSFRichTextString(value);
txtCell.setCellValue(text2);
}
}
}
workbook.write(out);
out.flush();
out.close();
}
public static void main(String[] args) throws Exception {
//写入本地
OutputStream out = new FileOutputStream("d://1432026362899.xls");
ExportXls ex = new ExportXls(out);
String [] headers={"A#标题1","B#标题2","C#标题3*"};
List<Map<String,Object>> dataList= new ArrayList<Map<String,Object>>();
for(int j=0;j<headers.length;j++){
Map<String,Object> map= new HashMap<String,Object>();
map.put("A",j+1);
map.put("B",new BigDecimal("580.63"));
map.put("C",null);
dataList.add(map);
}
ex.exportExcel("TEST",headers,dataList);
out.close();
System.out.println("excel导出成功!");
}
}
上面有测试类可以直接导出到本地,下面看看怎么直接打开连接就下载吧!
@ResponseBody
@RequestMapping(value = "/download", method = { RequestMethod.GET, RequestMethod.POST })
public void download(HttpServletResponse response){
try {
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename='xiaour@github.com.xls'");
response.setCharacterEncoding("utf-8");
//可以将output输出到服务器
OutputStream outputStream=response.getOutputStream();
ExportXls ex = new ExportXls(outputStream);
String [] headers={"id#编号","name#名称","operation_time#最近更新时间"};
ex.exportExcel("报表",headers,dataList);
}
} catch (Exception e) {
logger.error(e);
}
}