package com.fgkj.servlets;
|
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.io.UnsupportedEncodingException;
|
import java.util.ArrayList;
|
import java.util.List;
|
import javax.servlet.ServletException;
|
import javax.servlet.http.HttpServlet;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
|
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
|
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
|
import org.apache.poi.hssf.usermodel.HSSFRow;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import sun.misc.BASE64Decoder;
|
|
public class ExportTableServlet extends HttpServlet {
|
private String excelName = "Echarts";
|
private String pictureName;
|
private String excelTitle;
|
|
@Override
|
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
|
throws ServletException, IOException {
|
doPost(req, resp);
|
}
|
|
@Override
|
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
|
throws ServletException, IOException {
|
String pageName=req.getParameter("PageName");
|
|
String exp_KeyWord = req.getParameter("exp_normal"); //通用到处表格标识符
|
excelTitle = req.getParameter("excelTitle"); //获取excelTitel标题
|
|
if(ServletUtils.isNotNull(pageName)){
|
if("ele_statistics.jsp".equals(pageName)){
|
excelTitle="电池信息统计查询";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("eleAnalyse.jsp".equals(pageName)){
|
excelTitle="电池组统计查询";
|
pictureName="电池组统计分析图";
|
Exportele_eleAnalyseTable(resp,req);
|
}else if("eleBrdwMaint.jsp".equals(pageName)){
|
excelTitle="电池故障维护记录查询";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("eleBrdwDeal.jsp".equals(pageName)){
|
excelTitle="电池故障处理统计查询";
|
Exportele_statisticsTable(resp,req);
|
}else if("infor_manage.jsp".equals(pageName)){
|
excelTitle="用户信息管理";
|
Exporttele_eleBrdwMaintTable(resp, req);
|
}else if("elewarn.jsp".equals(pageName)){
|
excelTitle="电池告警实时查询";
|
Exportele_statisticsTable(resp, req);
|
}else if("eleHistoryWarn.jsp".equals(pageName)){
|
excelTitle="电池告警历史记录查询";
|
Exportele_statisticsTable(resp, req);
|
}else if("eleMonomer.jsp".equals(pageName)){
|
excelTitle="电池单体统计分析查询";
|
pictureName="单体趋势图";
|
Exportele_eleAnalyseTable(resp,req);
|
}else if("dataManage.jsp".equals(pageName)){
|
excelTitle="电池充放电数据查询";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("jobCheckMgr.jsp".equals(pageName)){
|
excelTitle="作业抽查管理";
|
Exporttele_eleBrdwMaintTable(resp, req);
|
}else if("jobRpt.jsp".equals(pageName)){
|
excelTitle="作业统计分析查询";
|
Exporttele_eleBrdwMaintTable(resp, req);
|
}else if("eleAssess.jsp".equals(pageName)){
|
excelTitle="电池组后评估表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
} else if("myJob.jsp".equals(pageName)){
|
excelTitle="作业明细列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("jobMgr_up".equals(pageName)){
|
excelTitle="作业列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("jobMgr_down".equals(pageName)){
|
excelTitle="作业明细列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("inforpt.jsp".equalsIgnoreCase(pageName)){
|
excelTitle=req.getParameter("title");
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("taskplan.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="电池落后单体列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("dischargeplan.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="放电计划列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("equipment.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="设备用电量统计查询列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("elestatus.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="电池状态列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("endure.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="电池组续航能力表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("powercut.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="机房停电列表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("equipWarn1.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="设备实时告警记录表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("equipHistWarn1.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="设备历史告警记录表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("equip-charge-param.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="设备充放电模块参数表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("batt-attention.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="电池单体关注表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("ele-param.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="电池组信息表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("equip-stats-change.jsp".equalsIgnoreCase(pageName)){
|
excelTitle="设备状态变化记录表";
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}else if("autoPageName".equalsIgnoreCase(pageName)){
|
if(!ServletUtils.isNotNull(excelTitle)){
|
excelTitle = "";
|
}
|
Exporttele_eleBrdwMaintTable(resp,req);
|
}
|
}else if(ServletUtils.isNotNull(exp_KeyWord)){
|
if("1".equals(exp_KeyWord)){
|
ExportGeneralExcelTable(resp,req); //导出通用的表格
|
}
|
}
|
}
|
|
private void ExportGeneralExcelTable(HttpServletResponse resp,
|
HttpServletRequest req) {
|
String exp_thead_str = req.getParameter("exp_thead");
|
String exp_tbody_str = req.getParameter("exp_tbody");
|
|
String exp_page_break = "\\"+req.getParameter("exp_page_break");
|
String exp_data_break = "\\"+req.getParameter("exp_data_break");
|
|
String exp_page_names_str = req.getParameter("exp_page_names");
|
|
String[] exp_page_names = new String[]{};
|
|
|
|
|
|
|
try {
|
//定义一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
if(ServletUtils.isNotNull(exp_thead_str)){
|
exp_page_names = exp_page_names_str.split(exp_page_break); //分割工作簿的名称
|
|
String[] exp_thead_sheets = exp_thead_str.split(exp_page_break);
|
String[] exp_tbody_sheets = exp_tbody_str.split(exp_page_break);
|
if(!ServletUtils.isNotNull(exp_page_names_str)){
|
exp_page_names = new String[]{};
|
for(int s=0;s<exp_tbody_sheets.length;s++){
|
exp_page_names[s] = "sheet"+s;
|
}
|
}
|
|
for (int i = 0; i < exp_thead_sheets.length; i++) {
|
HSSFSheet sheet = i<exp_page_names.length?workbook.createSheet(exp_page_names[i]):workbook.createSheet();
|
String[] exp_thead = exp_thead_sheets[i].split(exp_data_break);
|
String[] exp_tbody = exp_tbody_sheets[i].split(exp_data_break);
|
|
int maxColNum = exp_thead.length; //最大列数
|
int nowRowNum = 0; //当前行
|
int nowColNum = 0; //当前列
|
HSSFRow row = null;
|
for(int j=0;j <= exp_tbody.length;j++){
|
//System.out.println("j="+j+"\t nowRowNum="+nowRowNum);
|
if(j == 0){
|
row = sheet.createRow(nowRowNum++);
|
for(int k=0;k<exp_thead.length;k++){
|
HSSFCell cell = row.createCell(k);
|
HSSFRichTextString text = new HSSFRichTextString(exp_thead[k]);
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
continue;
|
}else if(j%maxColNum == 1){
|
nowColNum = 0;
|
row = sheet.createRow(nowRowNum++);
|
}
|
HSSFCell cell = row.createCell(nowColNum++);
|
HSSFRichTextString text = new HSSFRichTextString(exp_tbody[j-1]);
|
//HSSFRichTextString text = new HSSFRichTextString("*****");
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
}
|
|
}
|
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
workbook.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
private void Exporttele_eleBrdwMaintTable(HttpServletResponse resp,
|
HttpServletRequest req) {
|
String[] th_arr=req.getParameter("table_th_arr").split(",");
|
String[] td_arr=req.getParameter("table_td_arr").split("&");
|
|
//int len=(int)Math.ceil((double)td_arr.length/th_arr.length);
|
//
|
//System.out.println(th_arr.length+"==="+len);
|
//System.out.println(td_arr.length+"==="+len);
|
|
|
try {
|
//定义一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
HSSFSheet sheet = workbook.createSheet(excelTitle);
|
// 产生表格标题行
|
HSSFRow title = sheet.createRow(0);
|
HSSFCell titlecell = title.createCell(0);
|
HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
|
//System.out.print(text+"\t");
|
titlecell.setCellValue(tname);
|
int rowNum = 0;
|
if(th_arr!=null && th_arr.length>0){
|
HSSFRow row0 = sheet.createRow(1);
|
for(int i=0;i<th_arr.length;i++){
|
HSSFCell cell = row0.createCell(i);
|
HSSFRichTextString text = new HSSFRichTextString(th_arr[i]);
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
|
if(td_arr!=null && td_arr.length>0){
|
rowNum=(int)Math.ceil((double)td_arr.length/th_arr.length); //总行数
|
int index=0;
|
//System.out.println("td_arr.length"+td_arr.length);
|
//System.out.println("th_arr.length"+th_arr.length);
|
for(int i=1;i<=rowNum;i++){
|
HSSFRow row = sheet.createRow(i+1);
|
//System.out.println(i);
|
for(int j=0;j<th_arr.length && index<td_arr.length;j++){
|
HSSFCell cell = row.createCell(j);
|
HSSFRichTextString text = new HSSFRichTextString(td_arr[index++]);
|
//System.out.print((i-1)*rowNum+j+"\t");
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
//System.out.println(index);
|
}
|
|
}
|
}
|
|
String exp_picture_str = req.getParameter("exp_picture_str");
|
if(ServletUtils.isNotNull(exp_picture_str)){
|
rowNum = rowNum+3;
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
//当页面传入图片时
|
//System.out.println(exp_picture_str);
|
String[] url = exp_picture_str.split(",");
|
byte[] picbyte= new BASE64Decoder().decodeBuffer(url[1]);
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNum, (short) 10, 25 * (rowNum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,workbook.addPicture(picbyte,HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
|
}
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
workbook.write(out);
|
out.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
//导出eleAnalyse.jsp页面中的表格和图表到Excel中
|
private void Exportele_eleAnalyseTable(HttpServletResponse resp,
|
HttpServletRequest req) {
|
String[] arr_Th=req.getParameter("arr_Th").split(",");
|
String[] arr_Td=req.getParameter("arr_Td").split(",");
|
String[] echart_url=req.getParameter("echart_url").split(",");
|
int rowindex=4;
|
//System.out.println(arr_Td.length+"##"+arr_Th.length);
|
//定义一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
HSSFSheet sheet = workbook.createSheet(excelTitle);
|
// 产生表格标题行
|
HSSFRow title = sheet.createRow(0);
|
HSSFCell titlecell = title.createCell(0);
|
HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
|
//System.out.print(text+"\t");
|
titlecell.setCellValue(tname);
|
if(arr_Th!=null && arr_Th.length>0){
|
HSSFRow row0 = sheet.createRow(1);
|
for(int i=0;i<arr_Th.length;i++){
|
HSSFCell cell = row0.createCell(i);
|
HSSFRichTextString text = new HSSFRichTextString(arr_Th[i]);
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
|
if(arr_Td!=null && arr_Td.length>0){
|
int rowNum=(int)(Math.ceil((double)arr_Td.length/arr_Th.length)); //总行数
|
int index=0;
|
//System.out.println("总共有"+rowNum+"行");
|
for(int i=1;i<=rowNum;i++){
|
HSSFRow row = sheet.createRow(i+1);
|
for(int j=0;j<arr_Th.length && index<arr_Td.length;j++){
|
HSSFCell cell = row.createCell(j);
|
HSSFRichTextString text = new HSSFRichTextString(arr_Td[index++]);
|
//System.out.print((i-1)*rowNum+j+"\t");
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
}
|
rowindex=rowNum+3;
|
}
|
}
|
HSSFRow row = sheet.createRow(rowindex);
|
HSSFCell cell = row.createCell(0);
|
HSSFRichTextString text = new HSSFRichTextString(pictureName);
|
//System.out.print((i-1)*rowNum+j+"\t");
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
if(echart_url!=null && echart_url.length>0){
|
List<byte[]> bytes=new ArrayList<byte[]>();
|
try {
|
bytes.add(new BASE64Decoder().decodeBuffer(echart_url[1]));
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
for (int i = 0; i < bytes.size(); i++) {
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,
|
(short) 0, rowindex+1, (short) 0, 1000 * (2 + 1));
|
anchor.setAnchorType(3);
|
// 插入图片
|
patriarch.createPicture(
|
anchor,
|
workbook.addPicture(bytes.get(i),
|
HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
}
|
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
// 转码防止乱码
|
try {
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
workbook.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
//导出ele_statistics.jsp页面中的表格数据到Excel
|
private void Exportele_statisticsTable(HttpServletResponse resp,
|
HttpServletRequest req) {
|
String[] th_arr=req.getParameter("table_th_arr").split(",");
|
String[] td_arr=req.getParameter("table_td_arr").split(",");
|
|
//int len=td_arr.length/th_arr.length;
|
|
//System.out.println(th_arr.length+"==="+len);
|
//System.out.println(td_arr.length+"==="+len);
|
|
|
try {
|
//定义一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
HSSFSheet sheet = workbook.createSheet(excelTitle);
|
// 产生表格标题行
|
HSSFRow title = sheet.createRow(0);
|
HSSFCell titlecell = title.createCell(0);
|
HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
|
//System.out.print(text+"\t");
|
titlecell.setCellValue(tname);
|
|
if(th_arr!=null && th_arr.length>0){
|
HSSFRow row0 = sheet.createRow(1);
|
for(int i=0;i<th_arr.length;i++){
|
HSSFCell cell = row0.createCell(i);
|
HSSFRichTextString text = new HSSFRichTextString(th_arr[i]);
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
|
if(td_arr!=null && td_arr.length>0){
|
int rowNum=td_arr.length/th_arr.length; //总行数
|
int index=0;
|
//System.out.println("th_arr.length"+td_arr.length);
|
//System.out.println("th_arr.length"+th_arr.length);
|
for(int i=1;i<=rowNum;i++){
|
HSSFRow row = sheet.createRow(i+1);
|
//System.out.println(i);
|
for(int j=0;j<th_arr.length;j++){
|
HSSFCell cell = row.createCell(j);
|
HSSFRichTextString text = new HSSFRichTextString(td_arr[index++]);
|
//System.out.print((i-1)*rowNum+j+"\t");
|
//System.out.print(text+"\t");
|
cell.setCellValue(text);
|
}
|
//System.out.println(index);
|
}
|
|
}
|
}
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
workbook.write(out);
|
out.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
public static void main(String[] args) {
|
String ths = "h1&h2&h3&h4$xh1&xh2&xh3&xh4";
|
System.out.println(ths.indexOf("$"));
|
String[] sheets = ths.split("\\&");
|
System.out.println(sheets.length);
|
}
|
}
|
