@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-03
if("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++) {
// 指定页脚sheet
HSSFSheet 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);
//rn
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==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();
}
}
//协议库导出excel
public 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-03
if("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-03
if("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++) {
// 指定页脚sheet
HSSFSheet 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);
//rn
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==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();
}
}
//协议库导出excel
public 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-03
if("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;
}
}
--------
@Override
public 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{
//NULL
streetKeys = 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);
}
}
---
public Map readCustDeliveryReportXML() {
try{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
File file = new File(this.getClass().getResource("/").getPath()+ "report.xml");
Document document = builder.parse(file);
Element rootElement = document.getDocumentElement();
NodeList clist1 = rootElement.getElementsByTagName("custdelreportlistcnGROUPID");//
Element celement1 = (Element) clist1.item(0);
String cnGROUPID = celement1.getChildNodes().item(0).getNodeValue();
Map<String, String> map = new HashMap<String, String>();
map.put("cnGROUPID", cnGROUPID);
return map;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
--------------report.xml
<?xml version="1.0" encoding="GBK" standalone="no"?>
<root>
<ybjsyjs>
<ybjsyjsNAME>月本金收益结算</ybjsyjsNAME><!-- 报表名称 -->
<!-- 数据库列名 -->
<ybjsyjsenBANK_ACCOUNT>BANK_ACCOUNT</ybjsyjsenBANK_ACCOUNT>
<!-- excel表头名称 -->
<ybjsyjscnBANK_ACCOUNT>收款账户</ybjsyjscnBANK_ACCOUNT>
</ybjsyjs>
</root>
扫一扫
8882
07-28
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
