poi 对查询结果导出到excel

poi 对查询出来的list数据进行导出至excel表格中。

工具类:

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;


public class ExportUtils {
@SuppressWarnings("unchecked")
public static void  exportExcel(String title, String[] headers,String[] fields,List<Map<String, Object>> dataset,HSSFWorkbook workbook){
HSSFSheet sheet = workbook.createSheet(title);
sheet.setDefaultColumnWidth(25);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.SKY_BLUE.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.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style2.setFont(font2);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
int j = 1;
for (Map<String, Object> map : dataset) {
HSSFRow datarow = sheet.createRow( j++);
for(int i=0;i<fields.length;i++){
String fieldName= fields[i];
HSSFCell cell = datarow.createCell(i);
cell.setCellStyle(style2);
try {

HSSFRichTextString richString = new HSSFRichTextString(
map.get(fieldName)==null?"":map.get(fieldName).toString());
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
// 解决数字格式的值导出到excel中变成文本格式
Pattern pattern = Pattern.compile("[-+]?[0-9]+(\\.[0-9]+)?"); //判断是否为double类型
   if (richString != null && pattern.matcher(richString.toString()).matches())
     cell.setCellValue(Double.parseDouble(richString.toString()));
   else
     cell.setCellValue(richString);
} catch (SecurityException e) {
e.printStackTrace();
}
}
}
}
}

方法调用：

public void exportExcel(HttpServletRequest request,HttpServletResponse response)
throws Exception {


List list = historyDataService.getReportExcel();
List reportExcelList = new ArrayList();
Iterator it = list.iterator();
while (it.hasNext()) {
ReportExcel reportExcel = (ReportExcel) it.next();
Map reportExceMap = new HashMap();
reportExceMap.put("name", reportExcel.getNAME());
reportExceMap.put("c1", reportExcel.getC1());
reportExceMap.put("c2", reportExcel.getC2());
reportExceMap.put("c3", reportExcel.getC3());
reportExceMap.put("c4", reportExcel.getC4());
reportExceMap.put("c5", reportExcel.getC5());
reportExceMap.put("c6", reportExcel.getC6());
reportExcelList.add(reportExceMap);
}
HSSFWorkbook workbook = new HSSFWorkbook();

Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -1);
SimpleDateFormat format1=new SimpleDateFormat("yyyy.MM.dd");
SimpleDateFormat format2=new SimpleDateFormat("MM.dd");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy");// 可以方便地修改日期格式
String years = dateFormat.format(new Date());
int years_value = Integer.parseInt(years);
years_value--;
String presentYearYesterday=format1.format(cal.getTime());
String previousYearYesterday=years_value+"."+format2.format(cal.getTime());
String yesterday=format2.format(cal.getTime());

String[] headers = {"单位名称", presentYearYesterday+"当天销售额", previousYearYesterday+"当天销售额","同比增长率", "2014.7.1-"+yesterday+"累计销售额", "2013.7.1-"+yesterday+"累计销售额","同比增长率"};
String[] fields = {"name", "c1", "c2", "c3","c4", "c5", "c6"};
ExportUtils.exportExcel("分部每日销售跟进数据", headers, fields, reportExcelList,
workbook);
String agent = request.getHeader("User-Agent");
try {
boolean isFireFox = (agent != null && agent.toLowerCase().indexOf(
"firefox") != -1);
if (isFireFox) {
response.addHeader(
"Content-Disposition",
"attachment; filename*="
+ URLEncoder.encode("分部每日销售跟进表(to信息)", "utf-8") + ".xls");
} else {
response.addHeader(
"Content-Disposition",
"attachment; filename=\""
+ URLEncoder.encode("分部每日销售跟进表(to信息)", "utf-8") + ".xls"
+ "\"");
}
} catch (Exception e) {
response.addHeader("Content-Disposition", "attachment; filename=\""
+ URLEncoder.encode("分部每日销售跟进表(to信息)", "utf-8") + ".xls" + "\"");
}
response.setContentType("application/vnd.ms-excel");
workbook.write(response.getOutputStream());
}