import java.util.ArrayList
import java.util.HashMap
import java.util.List
import java.util.Map
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import com.dzd.utils.LogUtil
import org.apache.log4j.Logger
import com.dzd.sms.application.Define
public class OrderExportBusiness
{
public void orderExport(HttpServletRequest request, HttpServletResponse response,
List<User> dataList)
{
try
{
// 构造导出数据
List<Map<String, Object>> resultList = constructeResultList(dataList)
String sheetName = "统计信息"
String[] head0 = new String[]
{ "序号", "日期", "业务员", "客户名称", "账户", "通道", "通道类型", "计费量", "移动", "联通", "电信" }
String[] head1 = new String[]
{ "成功", "失败", "未知", "成功", "失败", "未知", "成功", "失败", "未知" }
String[] headnum0 = new String[]
{ "1,2,0,0", "1,2,1,1", "1,2,2,2", "1,2,3,3", "1,2,4,4", "1,2,5,5", "1,2,6,6",
"1,2,7,7", "1,1,8,10", "1,1,11,13", "1,1,14,16" }
String[] headnum1 = new String[]
{ "2,2,8,8", "2,2,9,9", "2,2,10,10", "2,2,11,11", "2,2,12,12", "2,2,13,13", "2,2,14,14",
"2,2,15,15", "2,2,16,16" }
String[] colName = new String[]
{ Define.STATICAL.NUMBER, Define.STATICAL.DATE, Define.STATICAL.NICKNAME,
Define.STATICAL.NAME, Define.STATICAL.EMAIL, Define.STATICAL.SMSAISLENAME,
Define.STATICAL.SMSAISLETYPEID, Define.STATICAL.SENDNUM,
Define.STATICAL.SUMSUCCEEDNUMUS, Define.STATICAL.SUMFAILURENUMUS,
Define.STATICAL.SUMUNKNOWNNUMUS, Define.STATICAL.SUMSUCCEEDNUMMS,
Define.STATICAL.SUMFAILURENUMMS, Define.STATICAL.SUMUNKNOWNNUMMS,
Define.STATICAL.SUMSUCCEEDNUMTS, Define.STATICAL.SUMFAILURENUMTS,
Define.STATICAL.SUMUNKNOWNNUMTS }
ExcelUtil.reportMergeXls(request, response, resultList, sheetName, head0, headnum0,
head1, headnum1, colName)
} catch (Exception e)
{
e.printStackTrace()
logger.error("导出失败")
throw new RuntimeException("导出失败")
}
}
private List<Map<String, Object>> constructeResultList(List<User> dataList)
{
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>()
Map<String, Object> tmpMap = null
int number = 0
for ( User user : dataList )
{
number = number++ + 1
tmpMap = new HashMap<String, Object>()
tmpMap.put(Define.STATICAL.NUMBER, number)
tmpMap.put(Define.STATICAL.DATE, user.getAuditTime())
tmpMap.put(Define.STATICAL.NICKNAME, user.getNickName())
tmpMap.put(Define.STATICAL.NAME, user.getName())
tmpMap.put(Define.STATICAL.EMAIL, user.getEmail())
tmpMap.put(Define.STATICAL.SMSAISLENAME, user.getSmsAisleName())
tmpMap.put(Define.STATICAL.SMSAISLETYPEID, user.getSmsAisleTypeId())
tmpMap.put(Define.STATICAL.SENDNUM, user.getSendNum())
tmpMap.put(Define.STATICAL.SUMSUCCEEDNUMUS, user.getSucceedNumUs())
tmpMap.put(Define.STATICAL.SUMFAILURENUMUS, user.getFailureNumUs())
tmpMap.put(Define.STATICAL.SUMUNKNOWNNUMUS, user.getUnknownNumUs())
tmpMap.put(Define.STATICAL.SUMSUCCEEDNUMMS, user.getSucceedNumMs())
tmpMap.put(Define.STATICAL.SUMFAILURENUMMS, user.getFailureNumMs())
tmpMap.put(Define.STATICAL.SUMUNKNOWNNUMMS, user.getUnknownNumMs())
tmpMap.put(Define.STATICAL.SUMSUCCEEDNUMTS, user.getSucceedNumTs())
tmpMap.put(Define.STATICAL.SUMFAILURENUMTS, user.getFailureNumTs())
tmpMap.put(Define.STATICAL.SUMUNKNOWNNUMTS, user.getUnknownNumTs())
resultList.add(tmpMap)
}
return resultList
}
}
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
import com.dzd.sms.application.Define;
/**
* *
*
* @author 作者 E-mail: *
* @date 创建时间:2017年4月11日 下午4:44:25 *
* @version 1.0 *
* @parameter *
* @since *
* @return
*/
public class ExcelUtil {
/**
* 多行表头 dataList:导出的数据;sheetName:表头名称; head0:表头第一行列名;headnum0:第一行合并单元格的参数
* head1:表头第二行列名;headnum1:第二行合并单元格的参数;detail:导出的表体字段
*
*/
public static void reportMergeXls(HttpServletRequest request, HttpServletResponse response,
List<Map<String, Object>> dataList, String sheetName, String[] head0, String[] headnum0, String[] head1,
String[] headnum1, String[] detail) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFFont headfont = setHeadFont(workbook);
HSSFCellStyle headstyle = setHeadStyle(workbook, headfont);
HSSFCellStyle style = setStyle(workbook);
HSSFCellStyle style2 = setStyle2(workbook);
boolean customer_order = Boolean.parseBoolean(request.getParameter(Define.FILENAME.CUSTOMER_ORDER));
setSheetWidthAndHigh(sheet);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, detail.length));
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 0x349);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(headstyle);
cell.setCellValue(sheetName);
for (int i = 0; i < headnum0.length; i++) {
String[] temp = headnum0[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
}
for (int i = 0; i < headnum1.length; i++) {
String[] temp = headnum1[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
}
setCell(head0, head1, sheet, style);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + 3);
for (int j = 0; j < detail.length; j++) {
Map<String, Object> tempmap = dataList.get(i);
Object data = tempmap.get(detail[j]);
cell = row.createCell(j);
cell.setCellStyle(style2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (data instanceof String) {
cell.setCellValue(data.toString());
} else if (data instanceof Integer) {
cell.setCellValue(Integer.valueOf(data.toString()));
} else if (data instanceof Date) {
String startTime = sdf.format(data);
cell.setCellValue(startTime);
}
}
}
String fileName = new String(sheetName);
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.reset();
response.setContentType("applicationnd.ms-excel;charset=utf-8");
response.setHeader( "Content-Disposition", "attachment;filename=\""+ new String( fileName.getBytes( "gb2312" ), "ISO8859-1" )+ ".xls" + "\"");
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
private static void setCustomCell(String[] head0, String[] head1, HSSFSheet sheet,
HSSFCellStyle style)
{
HSSFRow row;
HSSFCell cell;
row = sheet.createRow(1);
int jj = 0;
for (int i = 0; i < 6; i++) {
if (i > 3) {
jj = jj > 6 ? jj : i + 2;
cell = row.createCell(jj);
jj += 3;
} else {
cell = row.createCell(i);
}
cell.setCellValue(head0[i]);
cell.setCellStyle(style);
}
row = sheet.createRow(2);
for (int i = 0; i < head0.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
if (i > 2 && i < 12) {
for (int j = 0; j < head1.length; j++) {
cell = row.createCell(j + 3);
cell.setCellValue(head1[j]);
cell.setCellStyle(style);
}
}
}
}
private static void setCell(String[] head0, String[] head1, HSSFSheet sheet,
HSSFCellStyle style)
{
HSSFRow row;
HSSFCell cell;
row = sheet.createRow(1);
int jj = 0;
for (int i = 0; i < 11; i++) {
if (i > 8) {
jj = jj > 11 ? jj : i + 2;
cell = row.createCell(jj);
jj += 3;
} else {
cell = row.createCell(i);
}
cell.setCellValue(head0[i]);
cell.setCellStyle(style);
}
row = sheet.createRow(2);
for (int i = 0; i < head0.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
if (i > 7 && i < 17) {
for (int j = 0; j < head1.length; j++) {
cell = row.createCell(j + 8);
cell.setCellValue(head1[j]);
cell.setCellStyle(style);
}
}
}
}
private static void setSheetWidthAndHigh(HSSFSheet sheet) {
sheet.setColumnWidth(0, 1600);
sheet.setColumnWidth(1, 3600);
sheet.setColumnWidth(2, 6000);
sheet.setColumnWidth(3, 4500);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 4500);
sheet.setColumnWidth(6, 2800);
sheet.setColumnWidth(7, 2800);
sheet.setColumnWidth(8, 2800);
sheet.setColumnWidth(9, 2800);
sheet.setColumnWidth(10, 2800);
sheet.setColumnWidth(11, 2800);
sheet.setColumnWidth(12, 2800);
sheet.setColumnWidth(13, 2800);
sheet.setColumnWidth(14, 2800);
sheet.setColumnWidth(15, 2800);
sheet.setColumnWidth(16, 2800);
sheet.setDefaultRowHeight((short) 360);
}
private static void setCustomSheetWidthAndHigh(HSSFSheet sheet) {
sheet.setColumnWidth(0, 1600);
sheet.setColumnWidth(1, 3600);
sheet.setColumnWidth(2, 2800);
sheet.setColumnWidth(3, 2800);
sheet.setColumnWidth(4, 2800);
sheet.setColumnWidth(5, 2800);
sheet.setColumnWidth(6, 2800);
sheet.setColumnWidth(7, 2800);
sheet.setColumnWidth(8, 2800);
sheet.setColumnWidth(9, 2800);
sheet.setColumnWidth(10, 2800);
sheet.setColumnWidth(11, 2800);
sheet.setDefaultRowHeight((short) 360);
}
private static HSSFCellStyle setStyle2(HSSFWorkbook workbook) {
HSSFFont font2 = setDateFont(workbook);
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setFont(font2);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setWrapText(true);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style2;
}
private static HSSFCellStyle setStyle(HSSFWorkbook workbook) {
HSSFFont font = setDateFont(workbook);
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setLocked(true);
return style;
}
private static HSSFFont setDateFont(HSSFWorkbook workbook) {
HSSFFont datefont = workbook.createFont();
datefont.setFontName("宋体");
datefont.setFontHeightInPoints((short) 12);
return datefont;
}
private static HSSFCellStyle setHeadStyle(HSSFWorkbook workbook, HSSFFont headfont) {
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headstyle.setLocked(true);
return headstyle;
}
private static HSSFFont setHeadFont(HSSFWorkbook workbook) {
HSSFFont headfont = workbook.createFont();
headfont.setFontName("宋体");
headfont.setFontHeightInPoints((short) 22);
return headfont;
}
}