文件导出

@RequestMapping(value="/exportUserExcel")public void exportUserExcel(ModelMap model, HttpServletResponse response,HttpServletRequest request, HttpSession session){//查询条件填充Map<String,Object> queryMap = entityService.fillQueryMap(request,model);//查询条件-营业部处理String storesid = ParameterUtil.getReqParaValue(request,"queryOrgId");if(StringUtils.isNotEmpty(storesid)){if (storesid.substring(storesid.length() - 1,storesid.length()).equals(",")) {queryMap.put("queryOrgId", storesid.substring(0,(storesid.length() - 1)));}}User user = (User)session.getAttribute("CF_USERINFO"); //cookie中获取导出列//String cookieName = user.getLoginName()+"_CustDeliveryListExport";//String cookieValue = getCookieValue(request,cookieName);String columnValues = "";/*if(StringUtils.isNotEmpty(cookieValue) && cookieValue.length() > 0 ){if(!cookieValue.startsWith("checkAllColumn_")){columnValues = cookieValue;}else{columnValues = "showAll";}columnValues = "showAll";}*/queryMap.put("showColumns", columnValues);//执行导出userService.exportCustDeliveryExcel(queryMap, response);}package cn.creditharmoney.utils;import java.awt.Color;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.math.BigDecimal;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Collection;import java.util.Date;import java.util.HashMap;import java.util.HashSet;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Set;import java.util.Vector;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.math.NumberUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFFont;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;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Workbook;import cn.creditharmoney.entity.Attachment;import cn.creditharmoney.entity.User;public class ExportExcelUtil extends ExcelTempleteUtil {private static final int cloumn_with = 15;/** *  可以智能进行表格的合并 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelAdvanced(String fileName,HttpServletResponse response, Collection[] dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.length; sheetIndex++) {//System.out.println("当前第" + (sheetIndex + 1) + "页");// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);//System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 *///System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);//System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 *///System.out.println("\r\n");List datasetList = (List) dataset[sheetIndex];String[] keyss = keys[sheetIndex];int rowIndex = 1;LinkedHashMap<String, String> cellMap = new LinkedHashMap<String, String>();for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);//System.out.println("当前第" + (rowIndex) + "行");Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {//System.out.println("当前第" + (k + 1) + "列");HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);String value = null;//System.out.println(dataMap.get(keyss[k]));if (dataMap.get(keyss[k]) == null) {value = "";} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");value = fmt.format(dataMap.get(keyss[k])).toString();} else if (dataMap.get(keyss[k]) instanceof Number) {DecimalFormat df2 = (DecimalFormat) DecimalFormat.getInstance();df2.applyPattern("0.00");value = df2.format(dataMap.get(keyss[k])).toString();} else {value = dataMap.get(keyss[k]).toString();}}if(cellMap.get(k+"")!=null&&cellMap.get(k+"").equals(value)) {sheet = mergedRegion(sheet, i, i+1, k, k);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中cellContext.setCellStyle(style2);}else {cellContext.setCellValue(value);cellMap.put(k+"", value);}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 你下载指定的文件名,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelAudit(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if(k == 5 || k == 6){//System.out.println("id:"+dataMap.get(keyss[k-1])+",map:"+dataMap.get(keyss[k])+"。");BigDecimal money= new BigDecimal((null==dataMap.get(keyss[k]))?"0":dataMap.get(keyss[k]).toString().trim());DecimalFormat df = new DecimalFormat("#.##");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())));}else{if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 你下载指定的文件名,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);HSSFCellStyle redStyle = configColorContentStyle(workbook,HSSFFont.COLOR_RED);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);String colorFlag = (String)dataMap.get("COLORFLAG");for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);//在数据源中加入colorFlag字段 可以控制字段颜色 2016-03-03if("red".equals(colorFlag)){cellContext.setCellStyle(redStyle);}else{cellContext.setCellStyle(style2);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k]) || "DELIVERYDATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{style2.setDataFormat(dFormat.getFormat("@"));// 防止纯数字科学计数法，影响导入cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}@SuppressWarnings("rawtypes")public static void exportExcel2analyse(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();// 建立一个导出excel工程 HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚sheetHSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);// ---------------开始生成每一个sheet 先完成每列名称-------------String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}// ---------------标题制造完毕----------------------------------List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[sheetIndex];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);/* * 将使用量、使用量old、使用量new、释放量、放款量、剩余量、剩余量old、 * 剩余量new、0（含）—100元、100（含）—1000元、1000（含）—5000元、5000（含）—10000元、10000（含）元以上 * 字段单独格式化成保留两位小数（减少数据库计算压力） */if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else if(dataMap.get(keyss[k]) instanceof BigDecimal){if("RN".equals(keyss[k]) || "BORROW_RATES".equals(keyss[k])){//序号和利率不格式化cellContext.setCellValue(dataMap.get(keyss[k]).toString());}else{DecimalFormat df = new DecimalFormat("0.00");cellContext.setCellValue(df.format(dataMap.get(keyss[k])));}}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}@SuppressWarnings("rawtypes")public static void exportExcel120(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if(k == 5 || k == 7){BigDecimal money= new BigDecimal((null==dataMap.get(keyss[k]))?"0":dataMap.get(keyss[k]).toString().trim());DecimalFormat df = new DecimalFormat("￥#,##0.00");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())));}else if(k == 0){cellContext.setCellValue(i+1);}else{if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 确保数据库的每一列都有值时使用这个比较简单 *  * 导出文件名字,需添加扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcel(String fileName,HttpServletResponse response,Collection<List<Map<String, Object>>>[] dataset,String[][] headerss, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程 */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.length; sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 */System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 */System.out.println("\r\n");List datasetList = (List) dataset[sheetIndex];Iterator<?> it = datasetList.iterator();int rowIndex = 0;while (it.hasNext()) {rowIndex++;row = sheet.createRow(rowIndex);Map mapInList = (Map) it.next();int cellXIndex = 0;for (Object o : mapInList.keySet()) {HSSFCell cellContext = row.createCell(cellXIndex);cellContext.setCellStyle(style2);cellXIndex++;System.out.print(mapInList.get(o) + "\t");if (mapInList.get(o) != null) {cellContext.setCellValue(mapInList.get(o).toString());} else {cellContext.setCellValue("");}}System.out.println("\r\n");}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 确保数据库的每一列都有值时使用这个比较简单 *  * 导出文件名字,需添加扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param list *  *            没一个sheet显示的标题 * @param headerss.get(index) *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelMoreSheet(String fileName,HttpServletResponse response, List<List> dataset, List headerss, List keys, List sheets ) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程 */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚//HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);HSSFSheet sheet = workbook.createSheet(sheets.get(sheetIndex).toString());sheet.setDefaultColumnWidth(cloumn_with);System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 */System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = (String[]) headerss.get(sheetIndex);// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = (String[]) keys.get(sheetIndex);int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 设置标题单元格的样式 * @user zhaojiyan * @date 2013-8-7 * @param workbook * @return */private static HSSFCellStyle configCellStyle(HSSFWorkbook workbook) {HSSFCellStyle style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}/** * 设置内容单元格的样式 * @user zhaojiyan * @date 2013-8-7 * @param workbook * @return */private static HSSFCellStyle configContentStyle(HSSFWorkbook workbook) {HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}private static HSSFCellStyle configColorContentStyle(HSSFWorkbook workbook,short color) {HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);font2.setColor(color);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}/** * 导出富友划扣拆分 * @param fileName * @param response * @param dataset * @param headerss * @param keys * @param sheets */@SuppressWarnings("rawtypes")public static void exportFYHKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);BigDecimal splitMonry=new BigDecimal("50000000000000");for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;int rn=0;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {Map dataMap = (Map) datasetList.get(i);Object ob = dataMap.get(keyss[4]);BigDecimal money= new BigDecimal((null==ob)?"0":ob.toString().trim());if(money.compareTo(splitMonry)>0){int splitCount=(int) Math.ceil(money.divide(splitMonry).doubleValue());for(int j=1;j<=splitCount;j++,rowIndex++){row = sheet.createRow(rowIndex);rn=rn+1;for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rn);}else if(k==4){if(splitCount==j){cellContext.setCellValue(Integer.parseInt(money.subtract(splitMonry.multiply(new BigDecimal(j-1))).toString().trim()));}else{cellContext.setCellValue(Integer.parseInt(splitMonry.toString()));}}else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}money=money.subtract(splitMonry);rowIndex--;}else{rn=rn+1;row = sheet.createRow(rowIndex);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rowIndex);}else if (k==4) {/*DecimalFormat df2 = (DecimalFormat) DecimalFormat.getInstance();df2.applyPattern("0.00");cellContext.setCellValue(df2.format(dataMap.get(keyss[k])).toString());*///cellContext.setCellValue(Long.parseLong(dataMap.get(keyss[k]).toString()));cellContext.setCellValue(Double.parseDouble(money.toString()));} else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 导出好易联划扣拆分 * @param fileName * @param response * @param dataset * @param headerss * @param keys * @param sheets */@SuppressWarnings("rawtypes")public static void exportHYLHKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets,String[] strHeader,String[] strKeys) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);// 原来 BigDecimal splitMonry=new BigDecimal(500000);BigDecimal splitMonry=new BigDecimal("5000000000000");for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];String[] headerValue = strKeys;HSSFRow row = sheet.createRow(2);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 3;int moneyIndex = 10;int rn=0;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {Map dataMap = (Map) datasetList.get(i);//Object jj = dataMap.get(keyss[moneyIndex]);BigDecimal money= new BigDecimal((null==dataMap.get(keyss[moneyIndex]))?"0":dataMap.get(keyss[moneyIndex]).toString().trim());if(money.compareTo(splitMonry)>0){int splitCount=(int) Math.ceil(money.divide(splitMonry).doubleValue());for(int j=1;j<=splitCount;j++,rowIndex++){row = sheet.createRow(rowIndex);//rnrn=rn+1;for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rn);}else if(k==moneyIndex){if(splitCount==j){cellContext.setCellValue(Integer.parseInt(money.subtract(splitMonry.multiply(new BigDecimal(j-1))).multiply(new BigDecimal(100)).toString().trim()));}else{cellContext.setCellValue(Integer.parseInt(splitMonry.multiply(new BigDecimal(100)).toString()));}}else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else if(k==11||k==14){cellContext.setCellValue("");}else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}money=money.subtract(splitMonry);rowIndex--;}else{rn=rn+1;row = sheet.createRow(rowIndex);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rowIndex-2);}else{if (dataMap.get(keyss[k]) == null) {if(k==10){cellContext.setCellValue(Integer.parseInt(money.toString())*100);} else {cellContext.setCellValue("");}} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else {if(k==10){/* * 2015-02-01 liuwei modify * 问题：原来的转换方式当金额后有2位小数时后台报错 * 原：Integer.parseInt(money.toString())*100 * 现：Double.parseDouble(money.toString())*100 */DecimalFormat df = new DecimalFormat("#");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())*100));//cellContext.setCellValue(Integer.parseInt(money.toString())*100);}else if(k==11||k==14){cellContext.setCellValue("");}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}}// 产生表格标题行HSSFRow rowOne = sheet.createRow(0);HSSFRow rowTwo = sheet.createRow(1);for (int cellIndex = 0; cellIndex < strHeader.length; cellIndex++) {HSSFCell cellone = rowOne.createCell(cellIndex);cellone.setCellStyle(style);HSSFRichTextString textOne = new HSSFRichTextString(strHeader[cellIndex]);cellone.setCellValue(textOne);HSSFCell celltwo = rowTwo.createCell(cellIndex);celltwo.setCellStyle(style2);HSSFRichTextString textTwo = new HSSFRichTextString(headerValue[cellIndex]);if(cellIndex==3){celltwo.setCellValue(rn);}else if(cellIndex==4){String aaAmount = "0";if(null==textTwo || "".equals(textTwo) || textTwo.length() <=0){aaAmount = "0";} else {aaAmount = textTwo.toString();}/* * 2015-02-01 liuwei modify * 问题：原来的转换方式当金额后有2位小数时后台报错 * 原：Integer.parseInt(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()) * 现：Double.parseDouble(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()) */DecimalFormat df = new DecimalFormat("#");celltwo.setCellValue(df.format(Double.parseDouble(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString())));//celltwo.setCellValue(Integer.parseInt(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()));}else{celltwo.setCellValue(textTwo);}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}//协议库导出excelpublic static void exportXYKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null&&k==0) {cellContext.setCellValue("AC01(代收)");} else if (dataMap.get(keyss[k]) == null&&k==3) {cellContext.setCellValue("身份证");}else if (dataMap.get(keyss[k]) == null&&k==6) {cellContext.setCellValue("借记卡");}else if (dataMap.get(keyss[k]) == null&&k==8) {cellContext.setCellValue("否");}else if (dataMap.get(keyss[k]) == null&&k==9) {cellContext.setCellValue("客户协议库");}else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(null==dataMap.get(keyss[k])?"":dataMap.get(keyss[k]).toString());}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 设置相应头   zip  * @param response * @param fileName * @param conMap  */public static void setResponseHeader(HttpServletResponse response, String fileName) {  try {  fileName = fileName + ".zip";//    response.setContentType("application/octet-stream;charset=UTF-8");  //    response.setHeader("Content-Disposition", "attachment;filename="  //            + java.net.URLEncoder.encode(fileName, "UTF-8")  //            + ".zip");     response.setCharacterEncoding("GBK");response.setContentType("application/zip");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));} catch (Exception ex) {      ex.printStackTrace();  }  } /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyle(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}  /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configContentStyle(Workbook book) {CellStyle style2 = book.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);// 生成另一个字体Font font2 = book.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}/** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyleTitle(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 14);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}  /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyleTitle2(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style.setWrapText(true);return style;}  /** * 设置内容单元格样式 和 字体 * @param book * @return */public static CellStyle configCellStyleContext(Workbook book) {CellStyle style2 = book.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);// 生成另一个字体Font font2 = book.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)10);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style2.setWrapText(true);return style2;}/** * 设置内容单元格样式 和 字体 * @param book * @return */public static CellStyle configCellStyleSum(Workbook book) {CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体Font font = book.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font.setFontName("宋体");font.setFontHeightInPoints((short)10);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style.setWrapText(true);return style;}/**       * 压缩文件  zip     * @param srcfile 文件名数组       * @param zipfile 压缩后文件       */      public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {          byte[] buf = new byte[1024];          try {              ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));              for (int i = 0; i < srcfile.length; i++) {                  FileInputStream in = new FileInputStream(srcfile[i]);                  out.putNextEntry(new ZipEntry(srcfile[i].getName()));                  int len;                  while ((len = in.read(buf)) > 0) {                      out.write(buf, 0, len);                  }                  out.closeEntry();                  in.close();              }              out.close();          } catch (IOException e) {              e.printStackTrace();          }      }      /** * 创建文件夹 * @param filePath * @return 创建是否成功 */public static boolean makeDirs(String filePath) {File file = new File(filePath);// 如果文件夹不存在则创建if (!file.exists() && !file.isDirectory()) {file.mkdirs();return true; } else {return false;}}/** * 创建文件 * @param filePath * @return 创建是否成功 * @throws IOException */public static boolean makeFile(String filePath) throws IOException {File file = new File(filePath);    if(!file.exists() && !file.isFile())    {              file.createNewFile();        return true;    } else {return false; }}/** * 删除文件夹 * @param file */public static void deleteFile(File file) {  if (file.exists()) {//判断文件是否存在  if (file.isFile()) {//判断是否是文件  file.delete();//删除文件   } else if (file.isDirectory()) {//否则如果它是一个目录  File[] files = file.listFiles();//声明目录下所有的文件 files[];  for (int i = 0;i < files.length;i ++) {//遍历目录下所有的文件  deleteFile(files[i]);//把每个文件用这个方法进行迭代  }  file.delete();//删除文件夹  }  } else {  System.out.println("所删除的文件不存在");  }     } /** * 金账户手机变更信息导出，简化导出代码逻辑 * 实用于简单单sheet导出 * @author fdz  * @param fileName * @param response * @param dataset * @param headerss * @param keys */@SuppressWarnings("unchecked")public static void exportJZHExcel(String fileName,HttpServletResponse response, List<?> dataset,String[] headerss, String[] keys,String excelsavepath,String type) {try {/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);// 指定页脚HSSFSheet sheet = workbook.createSheet("金账户系统用户信息变更");sheet.setDefaultColumnWidth(cloumn_with);String[] titles = headerss;// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List<?> datasetList =dataset;String[] keyss = keys;Map<String,Object> olddataMap=new HashMap<String,Object>();int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map<String,Object> dataMap = (Map<String, Object>) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if("bank".equals(type) &&"变更前".equals(dataMap.get(keyss[0])) ){olddataMap=dataMap;}//如果是银行卡变更//如果是变更后的，则将变更手机号的单元格底色设置为黄色if("bank".equals(type) &&"变更后".equals(dataMap.get(keyss[0]))){if(!olddataMap.isEmpty()){if(!olddataMap.get(keyss[7]).equals(dataMap.get(keyss[7]))){if(k==7){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[1]).equals(dataMap.get(keyss[1]))){if(k==1){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[2]).equals(dataMap.get(keyss[2]))){if(k==2){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[5]).equals(dataMap.get(keyss[5]))){if(k==5){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}}}else if("sj".equals(type)&&k==4 && "变更后".equals(dataMap.get(keyss[0]))){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}File excelpathDir = new File(excelsavepath);         if (!excelpathDir.exists()) {        excelpathDir.mkdirs();        }//设置文件导出的位置 文件路劲+用户ID文件夹+具体的文件FileOutputStream ouputStream = new FileOutputStream(excelsavepath+File.separator+fileName);workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}private static HSSFCellStyle getJZHStyle(HSSFWorkbook workbook){HSSFCellStyle cellStyleTitle = workbook.createCellStyle();  cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格 cellStyleTitle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体HSSFFont font2 = workbook.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式cellStyleTitle.setFont(font2);cellStyleTitle.setWrapText(true);        return cellStyleTitle;}/** *  * //TODO 导出excel入口 * @param response * @param nodeElementName 根文件名称 * @param rList 数据源 */    public static void exportExcelFinal(HttpServletResponse response, String nodeElementName, List<?> rList) { // TODO Auto-generated method stub        String streetSheetName = "";// sheet名称        int j = 0;        Dom4jUtils xml = new Dom4jUtils();        Map<String,String> data = xml.readXml(nodeElementName);        String[] streetKeys = new String[(data.size()-1)/2];        String[] streetHeader =  new String[(data.size()-1)/2];        for ( String key : data.keySet()) {            if(j == 0){                streetSheetName = data.get(key);            }            if(j>0 && j<=(data.size()-1)/2){                streetKeys[j-1] = data.get(key);            }            if(j>(data.size()-1)/2){                streetHeader[j-(data.size()-1)/2-1] = data.get(key);            }            j++;        }        String[] dwmc_streetHeader = streetHeader;        String[] dwmc_streetKeys = streetKeys;        String[][] keys = { dwmc_streetKeys };        String[][] headerss = { dwmc_streetHeader };        SimpleDateFormat fmt = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");        String fileName = streetSheetName;// 报表名称        List<List> list = new ArrayList();                if(rList.size()<=60000){            String[] sheets = new String[1];            sheets[0] = streetSheetName + "1";            list.add(Utils.ConvertObjToMap(rList));            ExportExcelUtil.exportExcel(fileName + fmt.format(new Date())                + ".xls", response, list, headerss, keys, sheets);        }else{            List<?> addList = null;            int fysheet = (int) Math.ceil(rList.size() / 60000)+1;            String[] sheets = new String[fysheet];            for (int i = 1; i <= fysheet; i++) {                sheets[i - 1] = streetSheetName + i;                if(fysheet == i){                addList = rList.subList((i-1)*60000, rList.size());                    list.add(Utils.ConvertObjToMap(addList));                }else{                addList = rList.subList((i-1)*60000, i*60000);                    list.add(Utils.ConvertObjToMap(addList));                }            }            ExportExcelUtil.exportExcel(fileName + fmt.format(new Date())                + ".xls", response, list, headerss, keys, sheets);        }}    /** * 好易联导出TXT方法 * @param fileName * @param response * @param dataset * @param keys * @param strKeys */public static void exportTxtHYL(String fileName,HttpServletResponse response,List<Map<String, Object>> dataset,String[] keys,String strKeys) {StringBuffer bbString_header = new StringBuffer("");StringBuffer bbString = new StringBuffer("");int rn = 0;//循环行for (int i = 0; i < dataset.size(); i++) {Map dataMap = (Map) dataset.get(i);rn++;//循环列for (int k = 0; k < keys.length; k++) {if (k == 0) {bbString.append(rn+",");}else if (k == 10) {//将金额单位"元" 转化为 "分"BigDecimal money1 = new BigDecimal(dataMap.get(keys[k]).toString());DecimalFormat df = new DecimalFormat("#");String str = money1.multiply(new BigDecimal(100)).toString();bbString.append(df.format(Double.parseDouble(str)) + ",");}else if(k==11||k==14){bbString.append(",");}else{if (dataMap.get(keys[k]) == null) {bbString.append(",");}else {if (dataMap.get(keys[k]) instanceof Date) {//日期转化SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");bbString.append(fmt.format(dataMap.get(keys[k])).toString()+ ",");}else {bbString.append(dataMap.get(keys[k]).toString() + ",");}}}}bbString.deleteCharAt(bbString.length() - 1);bbString.append("\r\n");}strKeys = strKeys.replaceAll("hyltxt", rn+"");bbString_header.append(strKeys.toString()+"\r\n");bbString_header.append(bbString);BufferedOutputStream buff = null;ServletOutputStream outSTr = null;try {//输出文本response.setContentType("text/plain");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));outSTr = response.getOutputStream();buff = new BufferedOutputStream(outSTr);buff.write(bbString_header.toString().getBytes("GBK"));buff.flush();buff.close();} catch (Exception e) {e.printStackTrace();} finally {try {buff.close();outSTr.close();} catch (Exception ex) {ex.printStackTrace();}}}/** * gaoxu * 2016-2-3 15:38:29 */public static File exportExcel(String fileName,List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {File file = new File(fileName);try {file.createNewFile();FileOutputStream txtfile = new FileOutputStream(file);/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}workbook.write(txtfile);txtfile.flush();txtfile.close();} catch (IOException e) {e.printStackTrace();}return file;}/** * 通联导出,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static File exportExcelTongLian(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {File file = new File(fileName);try {file.createNewFile();FileOutputStream txtfile = new FileOutputStream(file);/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);HSSFCellStyle redStyle = configColorContentStyle(workbook,HSSFFont.COLOR_RED);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style2);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}String[] titles2 = headerss[1];// 产生表格标题行HSSFRow row2 = sheet.createRow(1);for (int cellIndex = 0; cellIndex < titles2.length; cellIndex++) {HSSFCell cell = row2.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles2[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 2;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);String colorFlag = (String)dataMap.get("COLORFLAG");for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);//在数据源中加入colorFlag字段 可以控制字段颜色 2016-03-03if("red".equals(colorFlag)){cellContext.setCellStyle(redStyle);}else{cellContext.setCellStyle(style2);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{style2.setDataFormat(dFormat.getFormat("@"));// 防止纯数字科学计数法，影响导入cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(txtfile);txtfile.flush();txtfile.close();} catch (IOException e) {e.printStackTrace();}return file;}}-----------------------package com.test.util;import java.io.File;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import org.dom4j.Attribute;import org.dom4j.Document;import org.dom4j.DocumentException;import org.dom4j.Element;import org.dom4j.io.SAXReader;public class Dom4jUtils {        Map<String, String> map = new LinkedHashMap<String, String>();        public Map<String, String> readXml(String nodeElementName){     // 创建saxReader对象          SAXReader reader = new SAXReader();          // 通过read方法读取一个文件 转换成Document对象          Document document;        try {            document = reader.read(new File(this.getClass()                .getResource("/").getPath()                + "exportReport.xml"));          //获取根节点元素对象              Element node = document.getRootElement();              Element temp = node.element(nodeElementName);            //遍历所有的元素节点              listNodes(temp);        } catch (DocumentException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }                  return map;    }         /**      * 遍历当前节点元素下面的所有(元素的)子节点      *       * @param node      */      public void listNodes(Element node) {        //        System.out.println("当前节点的名称：：" + node.getName());        // 获取当前节点的所有属性节点          List<Attribute> list = node.attributes();  //        // 遍历属性节点  //        for (Attribute attr : list) {  //            System.out.println(attr.getText() + "-----" + attr.getName()  //                    + "---" + attr.getValue());  //        }  //        if (!("".equals(node.getTextTrim()))) {  //            System.out.println("文本内容：：：：" + node.getText());  //        }        if(!("".equals(node.getTextTrim()))){            map.put(node.getName(), node.getText());        }        // 当前节点下面子节点迭代器          Iterator<Element> it = node.elementIterator();          // 遍历          while (it.hasNext()) {              // 获取某个子节点对象              Element e = it.next();              // 对子节点进行遍历              listNodes(e);          }      }      }---------package com.test.util;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Field;import java.math.BigDecimal;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.math.NumberUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFFont;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;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Workbook;public class ExportExcelUtil extends ExcelTempleteUtil {private static final int cloumn_with = 15;/** *  可以智能进行表格的合并 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelAdvanced(String fileName,HttpServletResponse response, Collection[] dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.length; sheetIndex++) {//System.out.println("当前第" + (sheetIndex + 1) + "页");// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);//System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 *///System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);//System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 *///System.out.println("\r\n");List datasetList = (List) dataset[sheetIndex];String[] keyss = keys[sheetIndex];int rowIndex = 1;LinkedHashMap<String, String> cellMap = new LinkedHashMap<String, String>();for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);//System.out.println("当前第" + (rowIndex) + "行");Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {//System.out.println("当前第" + (k + 1) + "列");HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);String value = null;//System.out.println(dataMap.get(keyss[k]));if (dataMap.get(keyss[k]) == null) {value = "";} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");value = fmt.format(dataMap.get(keyss[k])).toString();} else if (dataMap.get(keyss[k]) instanceof Number) {DecimalFormat df2 = (DecimalFormat) DecimalFormat.getInstance();df2.applyPattern("0.00");value = df2.format(dataMap.get(keyss[k])).toString();} else {value = dataMap.get(keyss[k]).toString();}}if(cellMap.get(k+"")!=null&&cellMap.get(k+"").equals(value)) {sheet = mergedRegion(sheet, i, i+1, k, k);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中cellContext.setCellStyle(style2);}else {cellContext.setCellValue(value);cellMap.put(k+"", value);}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 你下载指定的文件名,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelAudit(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if(k == 5 || k == 6){//System.out.println("id:"+dataMap.get(keyss[k-1])+",map:"+dataMap.get(keyss[k])+"。");BigDecimal money= new BigDecimal((null==dataMap.get(keyss[k]))?"0":dataMap.get(keyss[k]).toString().trim());DecimalFormat df = new DecimalFormat("#.##");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())));}else{if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 你下载指定的文件名,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);HSSFCellStyle redStyle = configColorContentStyle(workbook,HSSFFont.COLOR_RED);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);String colorFlag = (String)dataMap.get("COLORFLAG");for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);//在数据源中加入colorFlag字段 可以控制字段颜色 2016-03-03if("red".equals(colorFlag)){cellContext.setCellStyle(redStyle);}else{cellContext.setCellStyle(style2);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k]) || "DELIVERYDATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{style2.setDataFormat(dFormat.getFormat("@"));// 防止纯数字科学计数法，影响导入cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}@SuppressWarnings("rawtypes")public static void exportExcel2analyse(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();// 建立一个导出excel工程 HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚sheetHSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);// ---------------开始生成每一个sheet 先完成每列名称-------------String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}// ---------------标题制造完毕----------------------------------List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[sheetIndex];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);/* * 将使用量、使用量old、使用量new、释放量、放款量、剩余量、剩余量old、 * 剩余量new、0（含）—100元、100（含）—1000元、1000（含）—5000元、5000（含）—10000元、10000（含）元以上 * 字段单独格式化成保留两位小数（减少数据库计算压力） */if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else if(dataMap.get(keyss[k]) instanceof BigDecimal){if("RN".equals(keyss[k]) || "BORROW_RATES".equals(keyss[k])){//序号和利率不格式化cellContext.setCellValue(dataMap.get(keyss[k]).toString());}else{DecimalFormat df = new DecimalFormat("0.00");cellContext.setCellValue(df.format(dataMap.get(keyss[k])));}}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}@SuppressWarnings("rawtypes")public static void exportExcel120(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if(k == 5 || k == 7){BigDecimal money= new BigDecimal((null==dataMap.get(keyss[k]))?"0":dataMap.get(keyss[k]).toString().trim());DecimalFormat df = new DecimalFormat("￥#,##0.00");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())));}else if(k == 0){cellContext.setCellValue(i+1);}else{if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 确保数据库的每一列都有值时使用这个比较简单 *  * 导出文件名字,需添加扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcel(String fileName,HttpServletResponse response,Collection<List<Map<String, Object>>>[] dataset,String[][] headerss, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程 */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.length; sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 */System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = headerss[sheetIndex];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 */System.out.println("\r\n");List datasetList = (List) dataset[sheetIndex];Iterator<?> it = datasetList.iterator();int rowIndex = 0;while (it.hasNext()) {rowIndex++;row = sheet.createRow(rowIndex);Map mapInList = (Map) it.next();int cellXIndex = 0;for (Object o : mapInList.keySet()) {HSSFCell cellContext = row.createCell(cellXIndex);cellContext.setCellStyle(style2);cellXIndex++;System.out.print(mapInList.get(o) + "\t");if (mapInList.get(o) != null) {cellContext.setCellValue(mapInList.get(o).toString());} else {cellContext.setCellValue("");}}System.out.println("\r\n");}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 确保数据库的每一列都有值时使用这个比较简单 *  * 导出文件名字,需添加扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param list *  *            没一个sheet显示的标题 * @param headerss.get(index) *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static void exportExcelMoreSheet(String fileName,HttpServletResponse response, List<List> dataset, List headerss, List keys, List sheets ) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程 */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚//HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);HSSFSheet sheet = workbook.createSheet(sheets.get(sheetIndex).toString());sheet.setDefaultColumnWidth(cloumn_with);System.out.println("");/** * 开始生成每一个sheet 先完成标题部分 */System.out.println("开始画sheet" + (sheetIndex + 1));String[] titles = (String[]) headerss.get(sheetIndex);// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);System.out.print(titles[cellIndex] + "\t");cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = (String[]) keys.get(sheetIndex);int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 设置标题单元格的样式 * @user zhaojiyan * @date 2013-8-7 * @param workbook * @return */private static HSSFCellStyle configCellStyle(HSSFWorkbook workbook) {HSSFCellStyle style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}/** * 设置内容单元格的样式 * @user zhaojiyan * @date 2013-8-7 * @param workbook * @return */private static HSSFCellStyle configContentStyle(HSSFWorkbook workbook) {HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}private static HSSFCellStyle configColorContentStyle(HSSFWorkbook workbook,short color) {HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);font2.setColor(color);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}/** * 导出富友划扣拆分 * @param fileName * @param response * @param dataset * @param headerss * @param keys * @param sheets */@SuppressWarnings("rawtypes")public static void exportFYHKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);BigDecimal splitMonry=new BigDecimal("50000000000000");for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;int rn=0;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {Map dataMap = (Map) datasetList.get(i);Object ob = dataMap.get(keyss[4]);BigDecimal money= new BigDecimal((null==ob)?"0":ob.toString().trim());if(money.compareTo(splitMonry)>0){int splitCount=(int) Math.ceil(money.divide(splitMonry).doubleValue());for(int j=1;j<=splitCount;j++,rowIndex++){row = sheet.createRow(rowIndex);rn=rn+1;for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rn);}else if(k==4){if(splitCount==j){cellContext.setCellValue(Integer.parseInt(money.subtract(splitMonry.multiply(new BigDecimal(j-1))).toString().trim()));}else{cellContext.setCellValue(Integer.parseInt(splitMonry.toString()));}}else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}money=money.subtract(splitMonry);rowIndex--;}else{rn=rn+1;row = sheet.createRow(rowIndex);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rowIndex);}else if (k==4) {/*DecimalFormat df2 = (DecimalFormat) DecimalFormat.getInstance();df2.applyPattern("0.00");cellContext.setCellValue(df2.format(dataMap.get(keyss[k])).toString());*///cellContext.setCellValue(Long.parseLong(dataMap.get(keyss[k]).toString()));cellContext.setCellValue(Double.parseDouble(money.toString()));} else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 导出好易联划扣拆分 * @param fileName * @param response * @param dataset * @param headerss * @param keys * @param sheets */@SuppressWarnings("rawtypes")public static void exportHYLHKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets,String[] strHeader,String[] strKeys) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);// 原来 BigDecimal splitMonry=new BigDecimal(500000);BigDecimal splitMonry=new BigDecimal("5000000000000");for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];String[] headerValue = strKeys;HSSFRow row = sheet.createRow(2);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 3;int moneyIndex = 10;int rn=0;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {Map dataMap = (Map) datasetList.get(i);//Object jj = dataMap.get(keyss[moneyIndex]);BigDecimal money= new BigDecimal((null==dataMap.get(keyss[moneyIndex]))?"0":dataMap.get(keyss[moneyIndex]).toString().trim());if(money.compareTo(splitMonry)>0){int splitCount=(int) Math.ceil(money.divide(splitMonry).doubleValue());for(int j=1;j<=splitCount;j++,rowIndex++){row = sheet.createRow(rowIndex);//rnrn=rn+1;for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rn);}else if(k==moneyIndex){if(splitCount==j){cellContext.setCellValue(Integer.parseInt(money.subtract(splitMonry.multiply(new BigDecimal(j-1))).multiply(new BigDecimal(100)).toString().trim()));}else{cellContext.setCellValue(Integer.parseInt(splitMonry.multiply(new BigDecimal(100)).toString()));}}else{if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else if(k==11||k==14){cellContext.setCellValue("");}else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}money=money.subtract(splitMonry);rowIndex--;}else{rn=rn+1;row = sheet.createRow(rowIndex);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if(k==0){cellContext.setCellValue(rowIndex-2);}else{if (dataMap.get(keyss[k]) == null) {if(k==10){cellContext.setCellValue(Integer.parseInt(money.toString())*100);} else {cellContext.setCellValue("");}} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}else {if(k==10){/* * 2015-02-01 liuwei modify * 问题：原来的转换方式当金额后有2位小数时后台报错 * 原：Integer.parseInt(money.toString())*100 * 现：Double.parseDouble(money.toString())*100 */DecimalFormat df = new DecimalFormat("#");cellContext.setCellValue(df.format(Double.parseDouble(money.toString())*100));//cellContext.setCellValue(Integer.parseInt(money.toString())*100);}else if(k==11||k==14){cellContext.setCellValue("");}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}}// 产生表格标题行HSSFRow rowOne = sheet.createRow(0);HSSFRow rowTwo = sheet.createRow(1);for (int cellIndex = 0; cellIndex < strHeader.length; cellIndex++) {HSSFCell cellone = rowOne.createCell(cellIndex);cellone.setCellStyle(style);HSSFRichTextString textOne = new HSSFRichTextString(strHeader[cellIndex]);cellone.setCellValue(textOne);HSSFCell celltwo = rowTwo.createCell(cellIndex);celltwo.setCellStyle(style2);HSSFRichTextString textTwo = new HSSFRichTextString(headerValue[cellIndex]);if(cellIndex==3){celltwo.setCellValue(rn);}else if(cellIndex==4){String aaAmount = "0";if(null==textTwo || "".equals(textTwo) || textTwo.length() <=0){aaAmount = "0";} else {aaAmount = textTwo.toString();}/* * 2015-02-01 liuwei modify * 问题：原来的转换方式当金额后有2位小数时后台报错 * 原：Integer.parseInt(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()) * 现：Double.parseDouble(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()) */DecimalFormat df = new DecimalFormat("#");celltwo.setCellValue(df.format(Double.parseDouble(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString())));//celltwo.setCellValue(Integer.parseInt(new BigDecimal(aaAmount).multiply(new BigDecimal(100)).toString()));}else{celltwo.setCellValue(textTwo);}}}response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}//协议库导出excelpublic static void exportXYKExcel(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {try {OutputStream ouputStream = response.getOutputStream();/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null&&k==0) {cellContext.setCellValue("AC01(代收)");} else if (dataMap.get(keyss[k]) == null&&k==3) {cellContext.setCellValue("身份证");}else if (dataMap.get(keyss[k]) == null&&k==6) {cellContext.setCellValue("借记卡");}else if (dataMap.get(keyss[k]) == null&&k==8) {cellContext.setCellValue("否");}else if (dataMap.get(keyss[k]) == null&&k==9) {cellContext.setCellValue("客户协议库");}else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(null==dataMap.get(keyss[k])?"":dataMap.get(keyss[k]).toString());}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}/** * 设置相应头   zip  * @param response * @param fileName * @param conMap  */public static void setResponseHeader(HttpServletResponse response, String fileName) {  try {  fileName = fileName + ".zip";//    response.setContentType("application/octet-stream;charset=UTF-8");  //    response.setHeader("Content-Disposition", "attachment;filename="  //            + java.net.URLEncoder.encode(fileName, "UTF-8")  //            + ".zip");     response.setCharacterEncoding("GBK");response.setContentType("application/zip");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));} catch (Exception ex) {      ex.printStackTrace();  }  } /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyle(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}  /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configContentStyle(Workbook book) {CellStyle style2 = book.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);// 生成另一个字体Font font2 = book.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style2.setWrapText(true);return style2;}/** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyleTitle(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 14);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中style.setWrapText(true);return style;}  /** * 设置标题单元格样式 和 标题的字体 * @param book * @return */public static CellStyle configCellStyleTitle2(Workbook book) {// 设置标题单元格样式 和 标题的字体CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);// 设置标题单元格内容字体样式Font font = book.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style.setWrapText(true);return style;}  /** * 设置内容单元格样式 和 字体 * @param book * @return */public static CellStyle configCellStyleContext(Workbook book) {CellStyle style2 = book.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.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);// 生成另一个字体Font font2 = book.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)10);// 把字体应用到当前的样式style2.setFont(font2);style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style2.setWrapText(true);return style2;}/** * 设置内容单元格样式 和 字体 * @param book * @return */public static CellStyle configCellStyleSum(Workbook book) {CellStyle style = book.createCellStyle();style.setFillForegroundColor(HSSFColor.WHITE.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);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体Font font = book.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font.setFontName("宋体");font.setFontHeightInPoints((short)10);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字体应用到当前的样式style.setFont(font);style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：左对齐style.setWrapText(true);return style;}/**       * 压缩文件  zip     * @param srcfile 文件名数组       * @param zipfile 压缩后文件       */      public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {          byte[] buf = new byte[1024];          try {              ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));              for (int i = 0; i < srcfile.length; i++) {                  FileInputStream in = new FileInputStream(srcfile[i]);                  out.putNextEntry(new ZipEntry(srcfile[i].getName()));                  int len;                  while ((len = in.read(buf)) > 0) {                      out.write(buf, 0, len);                  }                  out.closeEntry();                  in.close();              }              out.close();          } catch (IOException e) {              e.printStackTrace();          }      }      /** * 创建文件夹 * @param filePath * @return 创建是否成功 */public static boolean makeDirs(String filePath) {File file = new File(filePath);// 如果文件夹不存在则创建if (!file.exists() && !file.isDirectory()) {file.mkdirs();return true; } else {return false;}}/** * 创建文件 * @param filePath * @return 创建是否成功 * @throws IOException */public static boolean makeFile(String filePath) throws IOException {File file = new File(filePath);    if(!file.exists() && !file.isFile())    {              file.createNewFile();        return true;    } else {return false; }}/** * 删除文件夹 * @param file */public static void deleteFile(File file) {  if (file.exists()) {//判断文件是否存在  if (file.isFile()) {//判断是否是文件  file.delete();//删除文件   } else if (file.isDirectory()) {//否则如果它是一个目录  File[] files = file.listFiles();//声明目录下所有的文件 files[];  for (int i = 0;i < files.length;i ++) {//遍历目录下所有的文件  deleteFile(files[i]);//把每个文件用这个方法进行迭代  }  file.delete();//删除文件夹  }  } else {  System.out.println("所删除的文件不存在");  }     } /** * 金账户手机变更信息导出，简化导出代码逻辑 * 实用于简单单sheet导出 * @author fdz  * @param fileName * @param response * @param dataset * @param headerss * @param keys */@SuppressWarnings("unchecked")public static void exportJZHExcel(String fileName,HttpServletResponse response, List<?> dataset,String[] headerss, String[] keys,String excelsavepath,String type) {try {/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);// 指定页脚HSSFSheet sheet = workbook.createSheet("金账户系统用户信息变更");sheet.setDefaultColumnWidth(cloumn_with);String[] titles = headerss;// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List<?> datasetList =dataset;String[] keyss = keys;Map<String,Object> olddataMap=new HashMap<String,Object>();int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map<String,Object> dataMap = (Map<String, Object>) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if("bank".equals(type) &&"变更前".equals(dataMap.get(keyss[0])) ){olddataMap=dataMap;}//如果是银行卡变更//如果是变更后的，则将变更手机号的单元格底色设置为黄色if("bank".equals(type) &&"变更后".equals(dataMap.get(keyss[0]))){if(!olddataMap.isEmpty()){if(!olddataMap.get(keyss[7]).equals(dataMap.get(keyss[7]))){if(k==7){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[1]).equals(dataMap.get(keyss[1]))){if(k==1){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[2]).equals(dataMap.get(keyss[2]))){if(k==2){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}if(!olddataMap.get(keyss[5]).equals(dataMap.get(keyss[5]))){if(k==5){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}}}}else if("sj".equals(type)&&k==4 && "变更后".equals(dataMap.get(keyss[0]))){HSSFCellStyle jzhstyle=getJZHStyle(workbook);cellContext.setCellStyle(jzhstyle);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}File excelpathDir = new File(excelsavepath);         if (!excelpathDir.exists()) {        excelpathDir.mkdirs();        }//设置文件导出的位置 文件路劲+用户ID文件夹+具体的文件FileOutputStream ouputStream = new FileOutputStream(excelsavepath+File.separator+fileName);workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (IOException e) {e.printStackTrace();}}private static HSSFCellStyle getJZHStyle(HSSFWorkbook workbook){HSSFCellStyle cellStyleTitle = workbook.createCellStyle();  cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格 cellStyleTitle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体HSSFFont font2 = workbook.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);font2.setFontName("宋体");font2.setFontHeightInPoints((short)11);// 把字体应用到当前的样式cellStyleTitle.setFont(font2);cellStyleTitle.setWrapText(true);        return cellStyleTitle;}/** *  * //TODO 导出excel入口 * @param response * @param nodeElementName 根文件名称 * @param rList 数据源 */    public static void exportExcelFinal(HttpServletResponse response, String nodeElementName, List<?> rList) { // TODO Auto-generated method stub        String streetSheetName = "";// sheet名称        int j = 0;        Dom4jUtils xml = new Dom4jUtils();        Map<String,String> data = xml.readXml(nodeElementName);        String[] streetKeys = new String[(data.size()-1)/2];        String[] streetHeader =  new String[(data.size()-1)/2];        for ( String key : data.keySet()) {            if(j == 0){                streetSheetName = data.get(key);            }            if(j>0 && j<=(data.size()-1)/2){                streetKeys[j-1] = data.get(key);            }            if(j>(data.size()-1)/2){                streetHeader[j-(data.size()-1)/2-1] = data.get(key);            }            j++;        }        String[] dwmc_streetHeader = streetHeader;        String[] dwmc_streetKeys = streetKeys;        String[][] keys = { dwmc_streetKeys };        String[][] headerss = { dwmc_streetHeader };        SimpleDateFormat fmt = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");        String fileName = streetSheetName;// 报表名称        List<List> list = new ArrayList();                if(rList.size()<=60000){            String[] sheets = new String[1];            sheets[0] = streetSheetName + "1";            list.add(ConvertObjToMap(rList));            ExportExcelUtil.exportExcel(fileName + fmt.format(new Date())                + ".xls", response, list, headerss, keys, sheets);        }else{            List<?> addList = null;            int fysheet = (int) Math.ceil(rList.size() / 60000)+1;            String[] sheets = new String[fysheet];            for (int i = 1; i <= fysheet; i++) {                sheets[i - 1] = streetSheetName + i;                if(fysheet == i){                addList = rList.subList((i-1)*60000, rList.size());                    list.add(ConvertObjToMap(addList));                }else{                addList = rList.subList((i-1)*60000, i*60000);                    list.add(ConvertObjToMap(addList));                }            }            ExportExcelUtil.exportExcel(fileName + fmt.format(new Date())                + ".xls", response, list, headerss, keys, sheets);        }}    public static List<Map> ConvertObjToMap(List<?> list) {        List<Map> result = new ArrayList<Map>();        for (int j = 0; j < list.size(); j++) {            Object obj = list.get(j);            Map<String, Object> reMap = new HashMap<String, Object>();            if (obj == null) return null;            Field[] fields = obj.getClass().getDeclaredFields();            try {                for (int i = 0; i < fields.length; i++) {                    try {                        Field f = obj.getClass().getDeclaredField(fields[i].getName());                        f.setAccessible(true);                        Object o = f.get(obj);                        reMap.put(fields[i].getName(), o);                    } catch (NoSuchFieldException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                    } catch (IllegalArgumentException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                    } catch (IllegalAccessException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                    }                }            } catch (SecurityException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            result.add(reMap);        }        return result;    }    /** * 好易联导出TXT方法 * @param fileName * @param response * @param dataset * @param keys * @param strKeys */public static void exportTxtHYL(String fileName,HttpServletResponse response,List<Map<String, Object>> dataset,String[] keys,String strKeys) {StringBuffer bbString_header = new StringBuffer("");StringBuffer bbString = new StringBuffer("");int rn = 0;//循环行for (int i = 0; i < dataset.size(); i++) {Map dataMap = (Map) dataset.get(i);rn++;//循环列for (int k = 0; k < keys.length; k++) {if (k == 0) {bbString.append(rn+",");}else if (k == 10) {//将金额单位"元" 转化为 "分"BigDecimal money1 = new BigDecimal(dataMap.get(keys[k]).toString());DecimalFormat df = new DecimalFormat("#");String str = money1.multiply(new BigDecimal(100)).toString();bbString.append(df.format(Double.parseDouble(str)) + ",");}else if(k==11||k==14){bbString.append(",");}else{if (dataMap.get(keys[k]) == null) {bbString.append(",");}else {if (dataMap.get(keys[k]) instanceof Date) {//日期转化SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");bbString.append(fmt.format(dataMap.get(keys[k])).toString()+ ",");}else {bbString.append(dataMap.get(keys[k]).toString() + ",");}}}}bbString.deleteCharAt(bbString.length() - 1);bbString.append("\r\n");}strKeys = strKeys.replaceAll("hyltxt", rn+"");bbString_header.append(strKeys.toString()+"\r\n");bbString_header.append(bbString);BufferedOutputStream buff = null;ServletOutputStream outSTr = null;try {//输出文本response.setContentType("text/plain");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));outSTr = response.getOutputStream();buff = new BufferedOutputStream(outSTr);buff.write(bbString_header.toString().getBytes("GBK"));buff.flush();buff.close();} catch (Exception e) {e.printStackTrace();} finally {try {buff.close();outSTr.close();} catch (Exception ex) {ex.printStackTrace();}}}/** * gaoxu * 2016-2-3 15:38:29 */public static File exportExcel(String fileName,List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {File file = new File(fileName);try {file.createNewFile();FileOutputStream txtfile = new FileOutputStream(file);/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 1;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);cellContext.setCellStyle(style2);if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}workbook.write(txtfile);txtfile.flush();txtfile.close();} catch (IOException e) {e.printStackTrace();}return file;}/** * 通联导出,需带扩展名 *  * @param fileName *  * @param response *  *            mybatis返回的数据源的封装 例如:List<Map<String,Object>> * @param dataset *  *            没一个sheet显示的标题 * @param headerss *  *            你sql语句select的column 的name,要和header对应 * @param keys *  *            页脚显示的文字 * @param sheets */@SuppressWarnings("rawtypes")public static File exportExcelTongLian(String fileName,HttpServletResponse response, List<List> dataset,String[][] headerss, String[][] keys, String[] sheets) {File file = new File(fileName);try {file.createNewFile();FileOutputStream txtfile = new FileOutputStream(file);/** * 建立一个导出excel工程  */HSSFWorkbook workbook = new HSSFWorkbook();// 设置标题单元格样式 和 标题的字体HSSFCellStyle style = configCellStyle(workbook);// 设置内容单元格样式 和 内容的字体HSSFCellStyle style2 = configContentStyle(workbook);HSSFCellStyle redStyle = configColorContentStyle(workbook,HSSFFont.COLOR_RED);//数据格式HSSFDataFormat dFormat=workbook.createDataFormat();for (int sheetIndex = 0; sheetIndex < dataset.size(); sheetIndex++) {// 指定页脚HSSFSheet sheet = workbook.createSheet(sheets[sheetIndex]);sheet.setDefaultColumnWidth(cloumn_with);/** * 开始生成每一个sheet 先完成标题部分 */String[] titles = headerss[0];// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int cellIndex = 0; cellIndex < titles.length; cellIndex++) {HSSFCell cell = row.createCell(cellIndex);cell.setCellStyle(style2);HSSFRichTextString text = new HSSFRichTextString(titles[cellIndex]);cell.setCellValue(text);}String[] titles2 = headerss[1];// 产生表格标题行HSSFRow row2 = sheet.createRow(1);for (int cellIndex = 0; cellIndex < titles2.length; cellIndex++) {HSSFCell cell = row2.createCell(cellIndex);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(titles2[cellIndex]);cell.setCellValue(text);}/** * 标题制造完毕 */List datasetList = (List) dataset.get(sheetIndex);String[] keyss = keys[0];int rowIndex = 2;for (int i = 0; i < datasetList.size(); i++, rowIndex++) {row = sheet.createRow(rowIndex);Map dataMap = (Map) datasetList.get(i);String colorFlag = (String)dataMap.get("COLORFLAG");for (int k = 0; k < keyss.length; k++) {HSSFCell cellContext = row.createCell(k);//在数据源中加入colorFlag字段 可以控制字段颜色 2016-03-03if("red".equals(colorFlag)){cellContext.setCellStyle(redStyle);}else{cellContext.setCellStyle(style2);}if (dataMap.get(keyss[k]) == null) {cellContext.setCellValue("");} else {if (dataMap.get(keyss[k]) instanceof Date) {if (keyss[k] == "EXAM_DATE" || "EXAM_DATE".equals(keyss[k])) {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());} else {SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");cellContext.setCellValue(fmt.format(dataMap.get(keyss[k])).toString());}} else {if(keyss[k].toLowerCase().endsWith("_coin") && NumberUtils.isNumber(dataMap.get(keyss[k]).toString())){style2.setDataFormat(dFormat.getFormat("￥#,###.00"));cellContext.setCellValue(Double.parseDouble(dataMap.get(keyss[k]).toString()));}else{style2.setDataFormat(dFormat.getFormat("@"));// 防止纯数字科学计数法，影响导入cellContext.setCellValue(dataMap.get(keyss[k]).toString());}}}}}}//String styleText = "<style>.text{mso-number-format:\\@;}</script>";response.setCharacterEncoding("GBK");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("GBK"), "ISO8859-1"));workbook.write(txtfile);txtfile.flush();txtfile.close();} catch (IOException e) {e.printStackTrace();}return file;}}--------@Overridepublic void exportUserExcel(Map<String, Object> queryMap,HttpServletResponse response) {ReadXmlUtil xml = new ReadXmlUtil();Map data = xml.readCustDeliveryReportXML();String streetSheetName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());String[] streetKeys = null;String[] streetHeader = null;List<String> keyList = new ArrayList<String>();List<String> headerList = new ArrayList<String>();if(queryMap.get("showColumns") !=null && queryMap.get("showColumns").toString().length() > 0){if("showAll".equals(queryMap.get("showColumns").toString())){keyList.add(String.valueOf(data.get("enGROUPID")));keyList.add(String.valueOf(data.get("enCUSTCODE")));headerList.add(String.valueOf(data.get("cnGROUPID")));headerList.add(String.valueOf(data.get("cnCUSTCODE")));}else{String[] columnArray = queryMap.get("showColumns").toString().split("_");for (String column : columnArray) {if(!"defaultLineBox".equals(column)){keyList.add(String.valueOf(data.get("en"+column.toUpperCase().substring(0,column.length()-3))));headerList.add(String.valueOf(data.get("cn"+column.toUpperCase().substring(0,column.length()-3))));}}}}else{//NULLstreetKeys = new String[0];streetHeader = new String[0];}streetKeys = new String[keyList.size()];for (int i = 0; i < keyList.size(); i++) {streetKeys[i] = keyList.get(i);}streetHeader = new String[headerList.size()];for (int i = 0; i < headerList.size(); i++) {streetHeader[i] = headerList.get(i);}String[] dwmc_streetKeys = streetKeys;String[] dwmc_streetHeader = streetHeader;String[][] keys = { dwmc_streetKeys };String[][] headerss = { dwmc_streetHeader };SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");List<List> list = new ArrayList<List>();int count = userMap.selectUserListCount(queryMap);if (count <= 60000) {String[] sheets = new String[1];sheets[0] = streetSheetName + "1";List<Map<String, Object>> fmList = userMap.selectUserListExport(queryMap, new RowBounds(0,count));list.add(fmList);ExportExcelUtil.exportExcel(fmt.format(new Date())+ ".csv", response, list, headerss, keys, sheets);} else {int fysheet = (int) Math.ceil(count / 60000) ;String[] sheets = new String[fysheet];for (int i = 1; i <= fysheet; i++) {sheets[i - 1] = streetSheetName + i;int begin =0; int end = 0;if (i == fysheet) {begin = (i - 1) * 60000 + 1;end = count;} else if (i == 1) {begin = 0;end = i * 60000;} else {queryMap.put("begin", (i - 1) * 60000 + 1);begin = (i - 1) * 60000 + 1;end = i * 60000;}List<Map<String, Object>> fmList = userMap.selectCustomerDeliveryListExport(queryMap, new RowBounds(begin,end));list.add(fmList);}ExportExcelUtil.exportExcel(fmt.format(new Date())+ ".csv", response, list, headerss, keys, sheets);}}