导包：
jar地址：
/s/1RtejTpZTfbkbCh1h7hKGqg
主要工具类：
package com.tcwl.vsmp.loanmgt.utils;import java.io.BufferedOutputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import com.tcwl.mon.LoanConstant;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;/*** * @author Administrator**/public class LoanExcelUtil{///*** 创建excel工作簿*/private static Workbook wb = null;/*** */private static int hh = 0;/*** 借款查询导出（） {申请类型}*/public static final String[] BUYGOODS = LoanConstant.BUYGOODS;/*** 借款查询导出（）* @param list list* @param keys 健* @param columnNames 值* @return 结果*/public static Workbook createWorkBookJinXin(List<Map<String, Object>> list,String[] keys, String[] columnNames){// 创建excel工作簿wb = new HSSFWorkbook();long allDataSize = (list.size() - 1);long mus = 60000;long avg = allDataSize / mus;long mod = allDataSize % mus;avg = mod == 0 ? avg : avg + 1;for (int si = 0; si < avg; si++){// 创建第一个sheet（页），并命名Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString() + "_" + si);// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();// 设置背景色：// setBorder.setFillForegroundColor((short) 13);// 设置背景色// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。for (int i = 0; i < keys.length; i++){sheet.setColumnWidth((short) i, (short) (35.7 * 150));}// 创建两种单元格格式CellStyle cs = wb.createCellStyle();CellStyle cs2 = wb.createCellStyle();// 创建两种字体Font f = wb.createFont();Font f2 = wb.createFont();// 创建第一种字体样式（用于列名）f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBoldweight(Font.BOLDWEIGHT_BOLD);// 创建第二种字体样式（用于值）f2.setFontHeightInPoints((short) 10);f2.setColor(IndexedColors.BLACK.getIndex());// Font f3=wb.createFont();// f3.setFontHeightInPoints((short) 10);// f3.setColor(IndexedColors.RED.getIndex());// 设置第一种单元格的样式（用于列名）cs.setFont(f);cs.setBorderLeft(CellStyle.BORDER_THIN);cs.setBorderRight(CellStyle.BORDER_THIN);cs.setBorderTop(CellStyle.BORDER_THIN);cs.setBorderBottom(CellStyle.BORDER_THIN);cs.setAlignment(CellStyle.ALIGN_CENTER);cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式// 单元格背景色cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置第二种单元格的样式（用于值）cs2.setFont(f2);cs2.setBorderLeft(CellStyle.BORDER_THIN);cs2.setBorderRight(CellStyle.BORDER_THIN);cs2.setBorderTop(CellStyle.BORDER_THIN);cs2.setBorderBottom(CellStyle.BORDER_THIN);cs2.setAlignment(CellStyle.ALIGN_CENTER);// ============================表头行begin================================//// 创建第一行Row header1 = sheet.createRow((short) 0);// ============================表头行end==================================//// 设置列名if (columnNames.length <= 0){hh = 0;for (int i = 0; i < columnNames.length; i++){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}else{hh = 0;// 创建第二行表头Row header2 = sheet.createRow((short) 1);// 表头 第一行for (int i = 0; i < columnNames.length; i++){if (columnNames[i] != ""){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}}int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());// 设置每行每列的值for (int i = 1; i <= mus + 1; i++){if (i + beginNum > allDataSize){break;}// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的// 创建一行，在页sheet上Row row1 = sheet.createRow(i + hh);// 在row行上创建一个方格for (short j = 0; j < keys.length; j++){Cell cell = row1.createCell(j);if("buyGoods_Id".equals(keys[j])){cell.setCellValue( BUYGOODS[Integer.parseInt(list.get(i + beginNum).get(keys[j]) == null ? " ": list.get(i + beginNum).get(keys[j]).toString())]);cell.setCellStyle(cs2);}else{cell.setCellValue(list.get(i + beginNum).get(keys[j]) == null ? " ": list.get(i + beginNum).get(keys[j]).toString());cell.setCellStyle(cs2);}}}}return wb;}/**** @param list list* @param keys 健* @param columnNames 值* @return 结果*/public static Workbook createWorkBook(List<Map<String, Object>> list,String[] keys, String[] columnNames){// 创建excel工作簿wb = new HSSFWorkbook();long allDataSize = (list.size() - 1);long mus = 60000;long avg = allDataSize / mus;long mod = allDataSize % mus;avg = mod == 0 ? avg : avg + 1;for (int si = 0; si < avg; si++){// 创建第一个sheet（页），并命名Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString() + "_" + si);// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();// 设置背景色：// setBorder.setFillForegroundColor((short) 13);// 设置背景色// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。for (int i = 0; i < keys.length; i++){sheet.setColumnWidth((short) i, (short) (35.7 * 150));}// 创建两种单元格格式CellStyle cs = wb.createCellStyle();CellStyle cs2 = wb.createCellStyle();// 创建两种字体Font f = wb.createFont();Font f2 = wb.createFont();// 创建第一种字体样式（用于列名）f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBoldweight(Font.BOLDWEIGHT_BOLD);// 创建第二种字体样式（用于值）f2.setFontHeightInPoints((short) 10);f2.setColor(IndexedColors.BLACK.getIndex());// Font f3=wb.createFont();// f3.setFontHeightInPoints((short) 10);// f3.setColor(IndexedColors.RED.getIndex());// 设置第一种单元格的样式（用于列名）cs.setFont(f);cs.setBorderLeft(CellStyle.BORDER_THIN);cs.setBorderRight(CellStyle.BORDER_THIN);cs.setBorderTop(CellStyle.BORDER_THIN);cs.setBorderBottom(CellStyle.BORDER_THIN);cs.setAlignment(CellStyle.ALIGN_CENTER);cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式// 单元格背景色cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置第二种单元格的样式（用于值）cs2.setFont(f2);cs2.setBorderLeft(CellStyle.BORDER_THIN);cs2.setBorderRight(CellStyle.BORDER_THIN);cs2.setBorderTop(CellStyle.BORDER_THIN);cs2.setBorderBottom(CellStyle.BORDER_THIN);cs2.setAlignment(CellStyle.ALIGN_CENTER);// ============================表头行begin================================//// 创建第一行Row header1 = sheet.createRow((short) 0);// ============================表头行end==================================//// 设置列名if (columnNames.length <= 0){hh = 0;for (int i = 0; i < columnNames.length; i++){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}else{hh = 0;// 创建第二行表头Row header2 = sheet.createRow((short) 1);// 表头 第一行for (int i = 0; i < columnNames.length; i++){if (columnNames[i] != ""){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}}int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());// 设置每行每列的值for (int i = 1; i <= mus + 1; i++){if (i + beginNum > allDataSize){break;}// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的// 创建一行，在页sheet上Row row1 = sheet.createRow(i + hh);// 在row行上创建一个方格for (short j = 0; j < keys.length; j++){Cell cell = row1.createCell(j);cell.setCellValue(list.get(i + beginNum).get(keys[j]) == null ? " ": list.get(i + beginNum).get(keys[j]).toString());cell.setCellStyle(cs2);}}}return wb;}/*** 保存生成的文件* * @param name* example: saveAaSpecName("e:/like.xls");*/public void saveAsExcelByName(String name){OutputStream out;try{out = new FileOutputStream(name);wb.write(out);System.out.println(out);}catch (FileNotFoundException e){// TODO Auto-generated catch blocke.printStackTrace();}catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}/*** * @param list 数据* @param keys 数据key值集合* @param columnNames 列名* @param sheetName sheet名* @param fileName 文件名* @param response 返回* @throws IOException*/public static void saveAsExcelByName(List<Map<String, Object>> list,String[] keys, String[] columnNames, String sheetName,String fileName, HttpServletResponse response) throws IOException{Map<String, Object> sheetname = new HashMap<String, Object>();sheetname.put("sheetName", sheetName);list.add(0, sheetname);createWorkBook(list, keys, columnNames);// wb.write(response.getOutputStream());response.addHeader("Content-Disposition","attachment;filename=" + fileName + ".xls");OutputStream os = new BufferedOutputStream(response.getOutputStream());wb.write(os);response.setContentType("application/vnd.ms-excel;charset=utf8");os.flush();os.close();}/*** 借款查询导出（）* @param list 数据* @param keys 数据key值集合* @param columnNames 列名* @param sheetName sheet名* @param fileName 文件名* @param response 返回* @throws IOException*/public static void saveAsExcelByNameJinXin(List<Map<String, Object>> list,String[] keys, String[] columnNames, String sheetName,String fileName, HttpServletResponse response) throws IOException{Map<String, Object> sheetname = new HashMap<String, Object>();sheetname.put("sheetName", sheetName);list.add(0, sheetname);/* createWorkBook(list, keys, columnNames);*/createWorkBookJinXin(list, keys, columnNames);// wb.write(response.getOutputStream());response.addHeader("Content-Disposition","attachment;filename=" + fileName + ".xls");OutputStream os = new BufferedOutputStream(response.getOutputStream());wb.write(os);response.setContentType("application/vnd.ms-excel;charset=utf8");os.flush();os.close();}}
控制类代码:
此方法用的数据用map封装，但也可以自己改为用实体类封装；
/**** @param start_date 开始* @param end_date 结束* @param license_plate 车牌* @param borrower_name 借款人* @param amount 金额* @param capital_source 字节来源* @param response 返回*/@RequestMapping("/exportLoanByMapNew")@ResponseBodypublic void exportLoanQueryDatasNew(@RequestParam("start_date") String start_date,@RequestParam("end_date") String end_date,@RequestParam("license_plate") String license_plate,@RequestParam("borrower_name") String borrower_name,@RequestParam("capital_source") Integer capital_source,@RequestParam("amount") Double amount,@RequestParam("order_no") String order_no,@RequestParam("status") String status,/* @RequestParam("buyGoodApply_start_date") String buyGoodApply_start_date,@RequestParam("buyGoodApply_end_date") String buyGoodApply_end_date,*/@RequestParam("buyGoods_Id") Integer buyGoods_Id,@RequestParam("buyGood_Status_Id") Integer buyGood_Status_Id,HttpServletResponse response){Map<String, Object> queryMap = new HashMap<String, Object>();queryMap.put("start_date", start_date);queryMap.put("end_date", end_date);queryMap.put("license_plate", license_plate);queryMap.put("borrower_name", borrower_name);queryMap.put("amount", amount);queryMap.put("capital_source", capital_source);queryMap.put("order_no",order_no );queryMap.put("status",status );/* queryMap.put("buyGoodApply_start_date",buyGoodApply_start_date );queryMap.put("buyGoodApply_end_date",buyGoodApply_end_date );*/queryMap.put("buyGoods_Id",buyGoods_Id );queryMap.put("buyGood_Status_Id",buyGood_Status_Id );String[] columnNames ={ "编号", "借款单号", "客户姓名", "借款状态", "申请类型","申请类型状态", "借款时间", "借款金额", "已借款天数", "利息", "合计总还" };String[] keys ={ "id", "order_no", "borrower_name", "status_desc","buyGood","ds_dic_value","create_time", "amount", "days", "interest", "total_sum" };//数据封装在map中List<Map<String, Object>> rl = service.exprotLoanDatasNew(queryMap);if(rl != null && 0 < rl.size()){for (int i = 0; i < rl.size(); i++) {System.out.println("==========================="+rl.get(i).toString());}}try{/* LoanExcelUtil.saveAsExcelByName(rl, keys, columnNames, "loandata","loandata", response);*///调用导出工具类方法LoanExcelUtil.saveAsExcelByNameJinXin(rl, keys, columnNames, "loandata","loandata", response);}catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}
至此：把代码拷贝过去就能用；
补充：
简洁版！
以下是将工具类与处里类集成放一个类中，方便大家学习；
直接拷贝过去就能用，注意底层传过来的数据格式要与类中接收的一致；
package com.tegen.servlet;import java.io.BufferedOutputStream;import java.io.IOException;import java.io.OutputStream;import java.io.PrintWriter;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import com.tegen.entity.HPVRatio;import com.tegen.service.ExportService;import com.tegen.service.HPVRatioService;import com.tegen.uitl.PageControler;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;public class ExportServlet extends HttpServlet {private static Workbook wb = null;private static int hh = 0;public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {this.doPost(request, response);}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {//设置请求和响应的编码格式request.setCharacterEncoding("utf-8");response.setCharacterEncoding("utf-8");String totale=request.getParameter("totale");HPVRatio h=new HPVRatio();h.setSampleInfo(totale);//获取当前页码String pageIndex=request.getParameter("pageIndex");//如果没有值的话，赋值为1if(pageIndex==null){pageIndex="1";}int currentPage=Integer.parseInt(pageIndex);//每页显示用户条数int pagesize=8;HPVRatioService hpvrvice=new HPVRatioService();PageControler pc=new PageControler();//获取要查询的表的数据总条数int count=hpvrvice.getCount();//获得总页数int totalPages=pc.getTotalPages(count,pagesize);//分页查询/*List<HPVRatio> hpv=new ArrayList<HPVRatio>();*//*hpv=hpvrvice.queryHpvByPage(totale, currentPage, pagesize);*//* List<Map<String, Object>> exportHpv = hpvrvice.ExportHpvByPage(totale, 1, count);*/List<Map<String, Object>> exportHpv = hpvrvice.ExportAll();if(exportHpv == null || exportHpv.size() == 0){return;}//自己改成中文字段名String[] columnNames ={ "编号" ,"字段名1" ,"字段名2" ,"字段名3" , "字段名4" };//数据库字段名String[] keys ={ "ID" ,"SourceType" ,"Number" ,"SampleInfo" , "Globin" };saveAsExcelByNameJinXin(exportHpv,keys,columnNames, "Test","Test", response);/* saveAsExcelByNameJinXin(exportHpv,keys, columnNames, "","", response);*/}public void saveAsExcelByNameJinXin(List<Map<String, Object>> list,String[] keys, String[] columnNames, String sheetName,String fileName, HttpServletResponse response) throws IOException{Map<String, Object> sheetname = new HashMap<String, Object>();sheetname.put("sheetName", sheetName);list.add(0, sheetname);/* createWorkBook(list, keys, columnNames);*/createWorkBookJinXin(list, keys, columnNames);// wb.write(response.getOutputStream());response.addHeader("Content-Disposition","attachment;filename=" + fileName + ".xls");OutputStream os = new BufferedOutputStream(response.getOutputStream());wb.write(os);response.setContentType("application/vnd.ms-excel;charset=utf8");os.flush();os.close();}public static Workbook createWorkBookJinXin(List<Map<String, Object>> list,String[] keys, String[] columnNames){// 创建excel工作簿wb = new HSSFWorkbook();long allDataSize = (list.size() - 1);long mus = 60000;long avg = allDataSize / mus;long mod = allDataSize % mus;avg = mod == 0 ? avg : avg + 1;for (int si = 0; si < avg; si++){// 创建第一个sheet（页），并命名Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString() + "_" + si);// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();// 设置背景色：// setBorder.setFillForegroundColor((short) 13);// 设置背景色// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。for (int i = 0; i < keys.length; i++){sheet.setColumnWidth((short) i, (short) (35.7 * 150));}// 创建两种单元格格式CellStyle cs = wb.createCellStyle();CellStyle cs2 = wb.createCellStyle();// 创建两种字体Font f = wb.createFont();Font f2 = wb.createFont();// 创建第一种字体样式（用于列名）f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBoldweight(Font.BOLDWEIGHT_BOLD);// 创建第二种字体样式（用于值）f2.setFontHeightInPoints((short) 10);f2.setColor(IndexedColors.BLACK.getIndex());// 设置第一种单元格的样式（用于列名）cs.setFont(f);cs.setBorderLeft(CellStyle.BORDER_THIN);cs.setBorderRight(CellStyle.BORDER_THIN);cs.setBorderTop(CellStyle.BORDER_THIN);cs.setBorderBottom(CellStyle.BORDER_THIN);cs.setAlignment(CellStyle.ALIGN_CENTER);cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式// 单元格背景色cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置第二种单元格的样式（用于值）cs2.setFont(f2);cs2.setBorderLeft(CellStyle.BORDER_THIN);cs2.setBorderRight(CellStyle.BORDER_THIN);cs2.setBorderTop(CellStyle.BORDER_THIN);cs2.setBorderBottom(CellStyle.BORDER_THIN);cs2.setAlignment(CellStyle.ALIGN_CENTER);// ============================表头行begin================================//// 创建第一行Row header1 = sheet.createRow((short) 0);// ============================表头行end==================================//// 设置列名if (columnNames.length <= 0){hh = 0;for (int i = 0; i < columnNames.length; i++){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}else{hh = 0;// 创建第二行表头Row header2 = sheet.createRow((short) 1);// 表头 第一行for (int i = 0; i < columnNames.length; i++){if (columnNames[i] != ""){Cell cell = header1.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}}}int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());// 设置每行每列的值for (int i = 1; i <= mus + 1; i++){if (i + beginNum > allDataSize){break;}// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的// 创建一行，在页sheet上Row row1 = sheet.createRow(i + hh);// 在row行上创建一个方格for (short j = 0; j < keys.length; j++){Cell cell = row1.createCell(j);cell.setCellValue(list.get(i + beginNum).get(keys[j]) == null ? " ": list.get(i + beginNum).get(keys[j]).toString());cell.setCellStyle(cs2);}}}return wb;} }
