package com.fgkj.servlets;
|
|
import com.fgkj.util.*;
|
import com.fgkj.dto.*;
|
import com.fgkj.mapper.BattTestData;
|
import com.google.gson.reflect.TypeToken;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.hssf.util.CellRangeAddress;
|
import sun.misc.BASE64Decoder;
|
|
import javax.servlet.ServletException;
|
import javax.servlet.http.HttpServlet;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.io.UnsupportedEncodingException;
|
import java.math.BigDecimal;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
public class EchartPictureDowloadServlet extends HttpServlet {
|
private String excelName = "Echarts";
|
private List<byte[]> bytes;
|
@Override
|
protected void service(HttpServletRequest req, HttpServletResponse resp)
|
throws ServletException, IOException {
|
String pageName = req.getParameter("pageName");
|
if (ServletUtils.isNotNull(pageName)) {
|
if ("charge-test".equalsIgnoreCase(pageName)) {
|
chargeExcelExprot(req, resp); //导出单个图表
|
}else if("control".equalsIgnoreCase(pageName)){
|
controlExcelExprot(req,resp); //实时监测导出图表
|
}else if("exportTbal".equalsIgnoreCase(pageName)){
|
ExprotReport(req,resp); //导出BTS常规报表
|
}else if("charge-test-monData".equalsIgnoreCase(pageName)){
|
ExprotLD9MonTestData(req,resp); //导出LD-9度历史数据
|
}else if("exportCTExcel".equalsIgnoreCase(pageName)){
|
ExprotCTReport(req,resp); //导出CT机类似报表
|
}
|
}
|
}
|
|
/**
|
* 历史放电数据页面中导出LD9设备的放电数据
|
* @param req
|
* @param resp
|
*/
|
private void ExprotLD9MonTestData(HttpServletRequest req,
|
HttpServletResponse resp) {
|
String mon_vol_line = req.getParameter("mon_vol_line"); //单体电压折线图
|
String group_vol_line = req.getParameter("group_vol_line"); //组端/在线电压折线图
|
String test_curr_line = req.getParameter("test_curr_line"); //测试电流折线图
|
String batt_real_cap = req.getParameter("batt_real_cap"); //单体实际容量柱状图
|
|
String battinf = req.getParameter("battinf"); //电池组信息
|
String mon_test_data = req.getParameter("mon_test_data"); //单体测试数据
|
String testdatainfo = req.getParameter("testdatainfo"); //单体测试数据
|
|
|
bytes=new ArrayList<byte[]>();
|
|
try {
|
//单体电压折线图
|
if (ServletUtils.isNotNull(mon_vol_line)) {
|
String[] url = mon_vol_line.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
//System.out.println("1111111111111111111111111");
|
}
|
|
//组端/在线电压折线图
|
if (ServletUtils.isNotNull(group_vol_line)) {
|
String[] url = group_vol_line.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
//System.out.println("2222222222222222222222222");
|
}
|
|
//单体电流曲线图
|
if (ServletUtils.isNotNull(test_curr_line)) {
|
String[] url = test_curr_line.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
//System.out.println("3333333333333333333333333333");
|
}
|
|
//单体电流曲线图
|
if (ServletUtils.isNotNull(batt_real_cap)) {
|
String[] url = batt_real_cap.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
//System.out.println("3333333333333333333333333333");
|
}
|
BattInf binf = ActionUtil.getGson().fromJson(battinf, BattInf.class);
|
Ld9testdata_inf mondatainfo = ActionUtil.getGson().fromJson(testdatainfo, Ld9testdata_inf.class);
|
List<Ld9testdata> montestdata = ActionUtil.getGson().fromJson(mon_test_data, new TypeToken<List<Ld9testdata>>(){}.getType());
|
createMonTestDataExcel(bytes,binf,montestdata,mondatainfo,resp,req);
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
//生成历史数据中LD9单体数据的报表
|
private void createMonTestDataExcel(List<byte[]> bytes,BattInf binf,List<Ld9testdata> montestdata,Ld9testdata_inf mondatainfo,HttpServletResponse resp,HttpServletRequest req) {
|
try {
|
// 创建一个工作薄
|
HSSFWorkbook wb = new HSSFWorkbook();
|
HSSFSheet sheet1 = wb.createSheet("数据总表");
|
// HSSFRow row = sheet1.createRow(2);
|
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
|
int rownum = 1;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
|
sheet1.getRow(rownum).createCell(2).setCellValue(binf.getStationName()+"-"+binf.getBattGroupName());
|
rownum++;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+binf.getBattProducer());
|
sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(binf.getBattInUseDate(),"yyyy-MM-dd"));
|
sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+binf.getMonCount());
|
sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+binf.getMonVolStd()+"V");
|
sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(binf.getMonCapStd(),0)+"AH");
|
sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(binf.getMonResStd(),3)+"mΩ");
|
sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(binf.getMonSerStd(),0));
|
|
rownum++;
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "被测单体编号：#"+mondatainfo.getMon_num());
|
sheet1.getRow(rownum).createCell(2).setCellValue( "测试类型："+binf.getBattGroupName5());
|
sheet1.getRow(rownum).createCell(3).setCellValue( "测试时间："+formartDate(mondatainfo.getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));
|
sheet1.getRow(rownum).createCell(4).setCellValue( "测试电流："+mondatainfo.getTest_curr()+"A");
|
sheet1.getRow(rownum).createCell(5).setCellValue( "测试时长："+formatTestLong(mondatainfo.getTest_timelong()));
|
sheet1.getRow(rownum).createCell(6).setCellValue( "测试容量："+formartDouble(mondatainfo.getTest_cap(),0)+"AH");
|
sheet1.getRow(rownum).createCell(7).setCellValue( "终止原因："+binf.getBattGroupName6());
|
|
rownum++;
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");
|
sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
|
rownum+=2;
|
|
int picnum = 0;
|
String[] picName = new String[]{
|
"单体电压曲线图","端电压曲线图","单体电流曲线图","单体实际容量柱状图"
|
};
|
for(int i=0;i<picName.length;i++){
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue(picName[i]);
|
rownum++;
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
}
|
|
createNewLD9DataSheet(wb,montestdata);
|
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
wb.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
|
public void createNewLD9DataSheet(HSSFWorkbook wb,final List<Ld9testdata> montestdata){
|
HSSFSheet sheet = wb.createSheet("单体数据表");
|
if(montestdata != null && montestdata.size()>0){
|
|
int mon_count = 1; //总单体数量
|
int total_col = montestdata.size(); //总记录条数
|
|
|
List<String> tabTh = new ArrayList<String>(){{
|
this.add("时间(HH:MM:SS)");this.add("组端电压(V)");this.add("在线电压(V)");this.add("电流(A)");this.add("测试容量(AH)");this.add("单体电压"+montestdata.get(0).getTest_monnum()+"(V)");
|
}};
|
//for(int i=1;i<=mon_count;i++){
|
// tabTh.add("单体电压"+i+"(V)");
|
//}
|
|
String[][] datas = new String[total_col][tabTh.size()];
|
|
//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
|
|
for(int j=0;j < datas.length;j++){
|
int currnum = 0;
|
datas[j][currnum++] = formatTestLong(montestdata.get(j).getTest_timelong());
|
datas[j][currnum++] = formartDouble(montestdata.get(j).getGroup_vol(),1)+"";
|
datas[j][currnum++] = formartDouble(montestdata.get(j).getOnline_vol(),1)+"";
|
datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_curr(),3)+"";
|
datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_cap(),1)+"";
|
|
|
datas[j][currnum++] =formartDouble(montestdata.get(j).getMon_vol(),3)+"";
|
|
//datas[j][currnum++] = "";
|
|
}
|
//System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);
|
|
int maxcol = 254; //设置最大列数
|
|
int countReLine = (int) Math.ceil((double)total_col/maxcol);
|
int totalrow = countReLine *(tabTh.size()+1); //excel总行数
|
for(int i=0;i<totalrow;i++){
|
sheet.createRow(i);
|
}
|
|
//System.out.println("countReLine:"+countReLine);
|
|
int currRow = 0; //当前行
|
int arr_index = 0; //当前列
|
for(int i=0;i<countReLine;i++){
|
for(int col = 0;col <= maxcol && col<=datas.length && arr_index<datas.length;col++){
|
if(col == 0){
|
for(int k=0;k<tabTh.size();k++){
|
sheet.getRow(k+currRow).createCell(col).setCellValue(tabTh.get(k));
|
}
|
}else{
|
for(int k = 0;k<datas[arr_index].length;k++){
|
sheet.getRow(k+currRow).createCell(col).setCellValue(datas[arr_index][k]);
|
}
|
arr_index++;
|
}
|
}
|
currRow += tabTh.size()+1;
|
}
|
|
//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
|
}
|
}
|
|
|
/**
|
* 导出报表
|
* @param req
|
* @param resp
|
*/
|
private void ExprotReport(HttpServletRequest req, HttpServletResponse resp) {
|
String ltop_echart = req.getParameter("ltop_echart"); //组端电压折线图
|
String rtop_echart = req.getParameter("rtop_echart"); //电池电流折线图
|
String lbottom_echart = req.getParameter("lbottom_echart"); //单体电压折线图
|
String rbottom_echart = req.getParameter("rbottom_echart"); //单体电压柱状图
|
String actucap_echart = req.getParameter("actucap_echart"); //实际容量折线图
|
String restcap_echart = req.getParameter("restcap_echart"); //实际容量折线图
|
String capperc_echart = req.getParameter("capperc_echart"); //实际容量折线图
|
String montmp_echart = req.getParameter("tmp_echart"); //单体温度折线图
|
String mon_res_echart = req.getParameter("mon_res"); //单体内阻折线图
|
String mon_jhcurr_eachart = req.getParameter("JH_curr"); //单体均衡电流柱状图
|
String last_vol_echart = req.getParameter("last_vol"); //单体终止电压折线图
|
String last_tmp_echart = req.getParameter("last_tmp"); //单体终止电压折线图
|
|
|
bytes=new ArrayList<byte[]>();
|
Batt_Maint_Dealarm bmd = ActionUtil.getGson("yyyy-MM-dd HH:mm:ss").fromJson(req.getParameter("obj-bmd"), Batt_Maint_Dealarm.class);
|
Title echarttitle = ActionUtil.getGson().fromJson(req.getParameter("obj-title"), Title.class);
|
String[][] datas = ActionUtil.getGson().fromJson(req.getParameter("arr-data"), String[][].class);
|
|
String[][] mon_vol_list = ActionUtil.getGson().fromJson(req.getParameter("mon-vol-list"), String[][].class);
|
String[][] mon_tmp_list = ActionUtil.getGson().fromJson(req.getParameter("mon-tmp-list"), String[][].class);
|
List<Batttestdata> groupinfo = ActionUtil.getGson().fromJson(req.getParameter("mon-group-list"), new TypeToken<List<Batttestdata>>(){}.getType());
|
|
//System.out.println("mon_vol_list:"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length+"\t groupinfo:"+groupinfo.size());
|
|
//System.out.println(groupinfo);
|
//System.out.println(echarttitle);
|
//System.out.println(datas);
|
try {
|
//组端电压曲线图
|
if (ServletUtils.isNotNull(ltop_echart)) {
|
String[] url = ltop_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//电池电流曲线图
|
if (ServletUtils.isNotNull(rtop_echart)) {
|
String[] url = rtop_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//电池单体终止电压曲线图
|
if (ServletUtils.isNotNull(last_vol_echart)) {
|
String[] url = last_vol_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//电池单体终止温度曲线图
|
if (ServletUtils.isNotNull(last_tmp_echart)) {
|
String[] url = last_tmp_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
|
//单体内阻柱状图
|
if (ServletUtils.isNotNull(mon_res_echart)) {
|
String[] url = mon_res_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
|
//单体均衡柱状图
|
if (ServletUtils.isNotNull(mon_jhcurr_eachart)) {
|
String[] url = mon_jhcurr_eachart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
|
//单体电压柱状图
|
if (ServletUtils.isNotNull(rbottom_echart)) {
|
String[] url = rbottom_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//单体实际容量柱状图
|
if(ServletUtils.isNotNull(actucap_echart)){
|
String[] url = actucap_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//单体剩余容量柱状图
|
if(ServletUtils.isNotNull(restcap_echart)){
|
String[] url = restcap_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//单体容量百分比柱状图
|
if(ServletUtils.isNotNull(capperc_echart)){
|
String[] url = capperc_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
//单体电压曲线图
|
if (ServletUtils.isNotNull(lbottom_echart)) {
|
String[] url = lbottom_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
|
//单体温度折线图
|
if(ServletUtils.isNotNull(montmp_echart)){
|
String[] url = montmp_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
if(bytes.size()>0){
|
createExcel(bytes, echarttitle, datas, bmd,mon_vol_list,mon_tmp_list,groupinfo, resp, req);
|
}
|
|
}
|
|
|
private void controlExcelExprot(HttpServletRequest req,
|
HttpServletResponse resp) {
|
String only_echart=req.getParameter("only_echart");
|
bytes=new ArrayList<byte[]>();
|
if(ServletUtils.isNotNull(only_echart)){
|
try {
|
String[] url = only_echart.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
createExcel(bytes, resp, req);
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
private void chargeExcelExprot(HttpServletRequest req,
|
HttpServletResponse resp) {
|
// String fileName = req.getParameter("filename");
|
|
String Big_picture=req.getParameter("big_pic");
|
// System.out.println(ltop_echart+"***");
|
bytes = new ArrayList<byte[]>();
|
try {
|
|
if(ServletUtils.isNotNull(Big_picture)){
|
String[] url = Big_picture.split(",");
|
bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
|
}
|
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
if (bytes.size() > 0) {
|
|
createExcel(bytes, resp, req);
|
}
|
}
|
|
private void createExcel(List<byte[]> bytes, HttpServletResponse resp,
|
HttpServletRequest req) {
|
try {
|
// 创建一个工作薄
|
HSSFWorkbook wb = new HSSFWorkbook();
|
HSSFSheet sheet1 = wb.createSheet("new sheet");
|
// HSSFRow row = sheet1.createRow(2);
|
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
|
for (int i = 0; i < bytes.size(); i++) {
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, 25 * i + 1, (short) 10, 25 * (i + 1));
|
anchor.setAnchorType(3);
|
// 插入图片
|
patriarch.createPicture(
|
anchor,
|
wb.addPicture(bytes.get(i),
|
HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
}
|
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
wb.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
//生成历史数据中的报表
|
private void createExcel(List<byte[]> bytes,Title title,String[][] datas,Batt_Maint_Dealarm bmd,String[][] mon_vol_list ,String[][] mon_tmp_list,List<Batttestdata> groupinfo, HttpServletResponse resp,
|
HttpServletRequest req) {
|
String[] arrTh = new String[]{
|
"单体编号","起始单体电压(V)","截止单体电压(V)","实际容量(AH)","剩余容量(AH)","实际容量百分比(%)","单体内阻(mΩ)","起始单体温度(℃)","终止单体温度(℃)"
|
};
|
String[] picName = new String[]{
|
"组端电压曲线","电池电流曲线","终止单体电压","终止单体温度","单体内阻","单体均衡电流","单体电压","单体实际容量","单体剩余容量","单体实际容量百分比","单体电压曲线","单体温度曲线"
|
};
|
try {
|
// 创建一个工作薄
|
HSSFWorkbook wb = new HSSFWorkbook();
|
HSSFSheet sheet1 = wb.createSheet("数据总表");
|
// HSSFRow row = sheet1.createRow(2);
|
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
|
int rownum = 1;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
|
sheet1.getRow(rownum).createCell(2).setCellValue(bmd.getBinf().getStationName()+"-"+bmd.getBinf().getBattGroupName());
|
rownum++;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+bmd.getBinf().getBattProducer());
|
sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(bmd.getBinf().getBattInUseDate(),"yyyy-MM-dd"));
|
sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+bmd.getBinf().getMonCount());
|
sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+bmd.getBinf().getMonVolStd()+"V");
|
sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(bmd.getBinf().getMonCapStd(),0)+"AH");
|
sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(bmd.getBinf().getMonResStd(),3)+"mΩ");
|
sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(bmd.getBinf().getMonSerStd(),0));
|
|
rownum++;
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "测试类型："+BattTestData.battState(bmd.getSdata().getTest_type()));
|
sheet1.getRow(rownum).createCell(2).setCellValue( "测试时间："+formartDate(bmd.getSdata().getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));
|
sheet1.getRow(rownum).createCell(3).setCellValue( "测试电流："+bmd.getSdata().getTest_curr()+"A");
|
sheet1.getRow(rownum).createCell(4).setCellValue( "测试时长："+formatTestLong(bmd.getSdata().getTest_timelong()));
|
sheet1.getRow(rownum).createCell(5).setCellValue( "测试容量："+formartDouble(bmd.getSdata().getTest_cap(),0)+"AH");
|
sheet1.getRow(rownum).createCell(6).setCellValue( "终止原因："+(bmd.getSdata().getTest_type()==2?"":bmd.getBinf().getStationIp()));
|
|
rownum++;
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");
|
sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
|
rownum+=2;
|
|
int picnum = 0;
|
|
for(int i=0;i<picName.length;i++){
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue(picName[i]);
|
rownum++;
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
}
|
|
/*sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("单体实际容量");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("单体剩余容量");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("单体实际容量百分比");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("单体电压曲线");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("组端电压曲线");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("电池电流曲线");
|
rownum++;
|
anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
|
anchor.setAnchorType(3);
|
patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picnum ++;rownum+=25;*/
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue("最高单体电压");
|
sheet1.getRow(rownum).createCell(2).setCellValue("最低单体电压");
|
sheet1.getRow(rownum).createCell(3).setCellValue("平均单体电压");
|
sheet1.getRow(rownum).createCell(4).setCellValue("落后单体电压值");
|
sheet1.getRow(rownum).createCell(5).setCellValue("落后单体数量");
|
sheet1.getRow(rownum).createCell(6).setCellValue("落后单体数量百分比");
|
rownum++;
|
|
sheet1.createRow(rownum);
|
sheet1.getRow(rownum).createCell(1).setCellValue(title.getMax());
|
sheet1.getRow(rownum).createCell(2).setCellValue(title.getMin());
|
sheet1.getRow(rownum).createCell(3).setCellValue(title.getAvg());
|
sheet1.getRow(rownum).createCell(4).setCellValue(title.getClow());
|
sheet1.getRow(rownum).createCell(5).setCellValue(title.getLc());
|
sheet1.getRow(rownum).createCell(6).setCellValue(title.getLp()+"%");
|
rownum+=2;
|
|
sheet1.createRow(rownum);
|
for(int i = 0 ;i<arrTh.length;i++){
|
sheet1.getRow(rownum).createCell(i+1).setCellValue(arrTh[i]);
|
}
|
rownum++;
|
|
if(datas!=null && datas.length>0){
|
for(int i=0;i<datas[0].length;i++){
|
sheet1.createRow(rownum);
|
for(int j=0;j<datas.length;j++){
|
sheet1.getRow(rownum).createCell(j+1).setCellValue(datas[j][i]);
|
}
|
rownum++;
|
}
|
}
|
|
createMonInfoSheet(wb,mon_vol_list ,mon_tmp_list,groupinfo);
|
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
wb.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public void createMonInfoSheet(HSSFWorkbook wb,String[][] mon_vol_list ,String[][] mon_tmp_list,List<Batttestdata> groupinfo){
|
HSSFSheet sheet = wb.createSheet("单体数据表");
|
if(mon_vol_list != null && mon_tmp_list != null && groupinfo != null){
|
int mon_count = mon_tmp_list.length; //总单体数量
|
int total_col = mon_vol_list.length; //总记录条数
|
|
|
List<String> tabTh = new ArrayList<String>(){{
|
this.add("时间(HH:MM:SS)");this.add("组端电压(V)");this.add("电流(A)");this.add("测试容量(AH)");
|
}};
|
for(int i=1;i<=mon_count;i++){
|
tabTh.add("单体电压"+i+"(V)");
|
}
|
for(int i=0;i<=mon_count;i++){
|
if(i == 0){
|
tabTh.add("");
|
continue;
|
}
|
tabTh.add("单体温度"+i+"(℃)");
|
}
|
|
String[][] datas = new String[total_col][tabTh.size()];
|
|
//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
|
|
for(int j=0;j < datas.length;j++){
|
int currnum = 0;
|
datas[j][currnum++] = formatTestLong(groupinfo.get(j).getTest_timelong());
|
datas[j][currnum++] = formartDouble(groupinfo.get(j).getGroup_vol(),1)+"";
|
datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_curr(),1)+"";
|
datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_cap(),1)+"";
|
|
for(int i=0;i<mon_vol_list[j].length;i++){
|
datas[j][currnum++] = mon_vol_list[j][i];
|
}
|
datas[j][currnum++] = "";
|
for(int i=0;i<mon_tmp_list.length;i++){
|
datas[j][currnum++] = mon_tmp_list[i][j];
|
}
|
}
|
//System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);
|
|
int maxcol = 254; //设置最大列数
|
|
int countReLine = (int) Math.ceil((double)total_col/maxcol);
|
int totalrow = countReLine *(tabTh.size()+1); //excel总行数
|
for(int i=0;i<totalrow;i++){
|
sheet.createRow(i);
|
}
|
|
//System.out.println("countReLine:"+countReLine);
|
|
int currRow = 0; //当前行
|
int arr_index = 0; //当前列
|
for(int i=0;i<countReLine;i++){
|
for(int col = 0;col <= maxcol && col<=datas.length && arr_index<datas.length;col++){
|
if(col == 0){
|
for(int k=0;k<tabTh.size();k++){
|
sheet.getRow(k+currRow).createCell(col).setCellValue(tabTh.get(k));
|
}
|
}else{
|
for(int k = 0;k<datas[arr_index].length;k++){
|
sheet.getRow(k+currRow).createCell(col).setCellValue(datas[arr_index][k]);
|
}
|
arr_index++;
|
}
|
}
|
currRow += tabTh.size()+1;
|
}
|
|
//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
|
}
|
}
|
|
//导出CT机样式报表
|
private void ExprotCTReport(HttpServletRequest req, HttpServletResponse resp) {
|
String ltop_echart = req.getParameter("ltop_echart"); //组端电压折线图
|
String rtop_echart = req.getParameter("rtop_echart"); //电池电流折线图
|
String lbottom_echart = req.getParameter("lbottom_echart"); //单体电压折线图
|
String actucap_echart = req.getParameter("actucap_echart"); //实际容量折线图
|
|
bytes=new ArrayList<byte[]>();
|
Batt_Maint_Dealarm bmd = ActionUtil.getGson("yyyy-MM-dd HH:mm:ss").fromJson(req.getParameter("obj-bmd"), Batt_Maint_Dealarm.class);
|
Title echarttitle = ActionUtil.getGson().fromJson(req.getParameter("obj-title"), Title.class);
|
String[][] datas = ActionUtil.getGson().fromJson(req.getParameter("arr-data"), String[][].class);
|
|
String[][] mon_vol_list = ActionUtil.getGson().fromJson(req.getParameter("mon-vol-list"), String[][].class);
|
String[][] mon_tmp_list = ActionUtil.getGson().fromJson(req.getParameter("mon-tmp-list"), String[][].class);
|
List<Batttestdata> groupinfo = ActionUtil.getGson().fromJson(req.getParameter("mon-group-list"), new TypeToken<List<Batttestdata>>(){}.getType());
|
// 创建一个工作薄
|
HSSFWorkbook wb = new HSSFWorkbook();
|
|
CreateReportCover(wb,bmd); //生成报告封面
|
|
CreateStartEndVol(wb,datas); //生成起止电压工作簿
|
|
CreateCapAnalyTable(wb,datas,actucap_echart); //生成容量分析表
|
|
CreateMonVolAnalyTable(wb,datas,lbottom_echart); //生成电压特性比较图
|
|
CreateTotalVolAnalyTable(wb,"总电压曲线图",ltop_echart); //生成总电压曲线图
|
|
CreateTotalVolAnalyTable(wb,"总电流曲线图",rtop_echart); //生成总电流曲线
|
|
createMonVolDataTable(wb,mon_vol_list,groupinfo); //生成电压数据表格
|
|
createExcel(wb,resp);
|
|
}
|
|
//生成报告封面
|
private void CreateReportCover(HSSFWorkbook wb,Batt_Maint_Dealarm bmd){
|
HSSFSheet sheet = wb.createSheet("报告封面");
|
|
sheet.setDefaultColumnWidth(9); // 设置单元格默认宽度
|
sheet.setDefaultRowHeight((short)(30*11));
|
//sheet.setDefaultRowHeightInPoints(18); //设置单元格默认高度
|
|
//System.out.println(bmd.getSdata());
|
HSSFCellStyle titleCellStyle = ExcelUtil.createCellStyle(wb,"楷体_GB2312",28,true,true); //单元格样式
|
|
HSSFCellStyle menuCellStyle = ExcelUtil.createCellStyle(wb,"楷体_GB2312",20,false,true); //单元格样式
|
HSSFCellStyle textCellStyle = ExcelUtil.createCellStyle(wb,"楷体_GB2312",20,true,true); //单元格样式
|
sheet.setColumnWidth(3, 24*256);
|
//sheet.setColumnWidth(3, 20*256);
|
int rowNum = 1;
|
sheet.createRow(rowNum++);
|
sheet.createRow(rowNum++);
|
sheet.createRow(rowNum++);
|
sheet.addMergedRegion(new CellRangeAddress(2,3,2,8)); //合并指定的单元格 param1 开始行 param2 结束行 param3 开始列 param4 结束列
|
sheet.getRow(2).createCell(2).setCellValue("蓄电池容量测试报告");
|
sheet.getRow(2).getCell(2).setCellStyle(titleCellStyle);
|
sheet.getRow(2).setHeightInPoints(17);
|
sheet.getRow(3).setHeightInPoints(17);
|
sheet.createRow(rowNum++);
|
sheet.createRow(rowNum++);
|
sheet.getRow(4).setHeightInPoints(40);
|
sheet.createRow(rowNum++);
|
sheet.getRow(5).createCell(3).setCellValue("分 公 司");
|
sheet.getRow(5).getCell(3).setCellStyle(menuCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(5,5,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(6).createCell(3).setCellValue("电池组名");
|
sheet.getRow(6).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(6).createCell(4).setCellValue(bmd.getBinf().getBattGroupName());
|
sheet.getRow(6).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(6,6,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(7).createCell(3).setCellValue("电池品牌");
|
sheet.getRow(7).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(7).createCell(4).setCellValue(bmd.getBinf().getBattProducer());
|
sheet.getRow(7).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(7,7,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(8).createCell(3).setCellValue("电池型号");
|
sheet.getRow(8).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(8).createCell(4).setCellValue(bmd.getBinf().getBattModel());
|
sheet.getRow(8).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(8,8,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(9).createCell(3).setCellValue("电池生产日期");
|
sheet.getRow(9).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(9).createCell(4).setCellValue(formartDate(bmd.getBinf().getBattProductDate(),"yyyy-MM-dd"));
|
sheet.getRow(9).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(9,9,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(10).createCell(3).setCellValue("投入使用日期");
|
sheet.getRow(10).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(10).createCell(4).setCellValue(formartDate(bmd.getBinf().getBattInUseDate(),"yyyy-MM-dd"));
|
sheet.getRow(10).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(10,10,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(11).createCell(3).setCellValue("标称容量");
|
sheet.getRow(11).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(11).createCell(4).setCellValue(bmd.getBinf().getMonCapStd());
|
sheet.getRow(11).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(11,11,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(12).createCell(3).setCellValue("单体电压");
|
sheet.getRow(12).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(12).createCell(4).setCellValue(bmd.getBinf().getMonVolStd());
|
sheet.getRow(12).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(12,12,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(13).createCell(3).setCellValue("放 电 率");
|
sheet.getRow(13).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(13).createCell(4).setCellValue(10);
|
sheet.getRow(13).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(13,13,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(14).createCell(3).setCellValue("维护人员");
|
sheet.getRow(14).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(14).createCell(4).setCellValue("");
|
sheet.getRow(14).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(14,14,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(15).createCell(3).setCellValue("联系电话");
|
sheet.getRow(15).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(15).createCell(4).setCellValue("");
|
sheet.getRow(15).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(15,15,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(16).setHeightInPoints(24);
|
sheet.createRow(rowNum++);
|
sheet.getRow(17).createCell(3).setCellValue("测试单位");
|
sheet.getRow(17).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(17).createCell(4).setCellValue("");
|
sheet.getRow(17).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(17,17,4,9));
|
sheet.createRow(rowNum++);
|
sheet.getRow(18).createCell(3).setCellValue("测试时间");
|
sheet.getRow(18).getCell(3).setCellStyle(menuCellStyle);
|
sheet.getRow(18).createCell(4).setCellValue(formartDate(bmd.getSdata().getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));
|
sheet.getRow(18).getCell(4).setCellStyle(textCellStyle);
|
sheet.addMergedRegion(new CellRangeAddress(18,18,4,9));
|
}
|
|
//生成起止电压数据
|
private void CreateStartEndVol(HSSFWorkbook wb,String[][] data){
|
HSSFSheet sheet = wb.createSheet("起止电压数据");
|
|
HSSFCellStyle titleCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,true); //单元格样式
|
HSSFCellStyle numCellStyle = ExcelUtil.createCellStyle(wb,"Times New Roman",12,true,true); //单元格样式
|
HSSFCellStyle txtCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,false); //单元格样式
|
|
sheet.setColumnWidth(2, 24*256);
|
sheet.setColumnWidth(3, 24*256);
|
sheet.setColumnWidth(4, 24*256);
|
|
sheet.setDefaultColumnWidth(9); // 设置单元格默认宽度
|
sheet.setDefaultRowHeight((short)(30*11));
|
int rowNum = 0;
|
sheet.createRow(rowNum);
|
sheet.getRow(rowNum).createCell(3).setCellValue("起始值(V)");
|
sheet.getRow(rowNum).getCell(3).setCellStyle(titleCellStyle);
|
sheet.getRow(rowNum).createCell(4).setCellValue("结束值(V)");
|
sheet.getRow(rowNum++).getCell(4).setCellStyle(titleCellStyle);
|
sheet.createRow(rowNum);
|
sheet.getRow(rowNum).createCell(2).setCellValue("最高电压");
|
sheet.getRow(rowNum).getCell(2).setCellStyle(titleCellStyle);
|
String[] startArr = data[1];
|
String[] endArr = data[2];
|
float maxStart = Float.parseFloat(startArr[0]);
|
int maxStartNum = 1;
|
float maxEnd = Float.parseFloat(endArr[0]);
|
int maxEndNum = 1;
|
for(int i = 0;i<startArr.length;i++){
|
if(Float.parseFloat(startArr[i]) > maxStart){
|
maxStart = Float.parseFloat(startArr[i]);
|
maxStartNum = i+1;
|
}
|
if(Float.parseFloat(endArr[i]) > maxEnd){
|
maxEnd = Float.parseFloat(endArr[i]);
|
maxEndNum = i+1;
|
}
|
}
|
sheet.getRow(rowNum).createCell(3).setCellValue(""+maxStart+"(#"+maxStartNum+")");
|
sheet.getRow(rowNum).getCell(3).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(4).setCellValue(""+maxEnd+"(#"+maxEndNum+")");
|
sheet.getRow(rowNum++).getCell(4).setCellStyle(txtCellStyle);
|
for(int i=0;i<startArr.length;i++){
|
sheet.createRow(rowNum);
|
sheet.getRow(rowNum).createCell(2).setCellValue("#"+(i+1));
|
sheet.getRow(rowNum).getCell(2).setCellStyle(numCellStyle);
|
sheet.getRow(rowNum).createCell(3).setCellValue(""+startArr[i]);
|
sheet.getRow(rowNum).getCell(3).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(4).setCellValue(""+endArr[i]);
|
sheet.getRow(rowNum++).getCell(4).setCellStyle(txtCellStyle);
|
}
|
}
|
|
//生成容量分析表
|
private void CreateCapAnalyTable(HSSFWorkbook wb,String[][] data,String picstr){
|
HSSFSheet sheet = wb.createSheet("容量分析表");
|
|
HSSFCellStyle titleCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,true); //单元格样式
|
HSSFCellStyle txtCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,false); //单元格样式
|
|
sheet.setDefaultColumnWidth(9); // 设置单元格默认宽度
|
sheet.setColumnWidth(0, 8*256);
|
sheet.setColumnWidth(1, 6*256);
|
sheet.setColumnWidth(2, 13*256);
|
sheet.setColumnWidth(3, 18*256);
|
sheet.setColumnWidth(4, 18*256);
|
sheet.setColumnWidth(5, 18*256);
|
sheet.setColumnWidth(6, 18*256);
|
|
byte [] pic = null;
|
if(ServletUtils.isNotNull(picstr)){
|
try {
|
String[] url = picstr.split(",");
|
pic = new BASE64Decoder().decodeBuffer(url[1]);
|
} catch (IOException e) {
|
//e.printStackTrace();
|
}
|
}
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
if(pic != null){
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, 25 * 0 + 1, (short) 10, 25 * (0 + 1));
|
anchor.setAnchorType(3);
|
// 插入图片
|
patriarch.createPicture(anchor, wb.addPicture(pic,HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
}
|
|
int rouNum = 26;
|
sheet.createRow(rouNum);
|
sheet.getRow(rouNum).createCell(2).setCellValue("单体编号");
|
sheet.getRow(rouNum).getCell(2).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(3).setCellValue("剩余容量(AH)");
|
sheet.getRow(rouNum).getCell(3).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(4).setCellValue("实际容量(AH)");
|
sheet.getRow(rouNum).getCell(4).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(5).setCellValue("容量百分比(%)");
|
sheet.getRow(rouNum++).getCell(5).setCellStyle(titleCellStyle);
|
|
for(int i=0;i<data[0].length;i++){
|
sheet.createRow(rouNum);
|
sheet.getRow(rouNum).createCell(2).setCellValue(data[0][i]);
|
sheet.getRow(rouNum).getCell(2).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(3).setCellValue(data[4][i]);
|
sheet.getRow(rouNum).getCell(3).setCellStyle(txtCellStyle);
|
sheet.getRow(rouNum).createCell(4).setCellValue(data[3][i]);
|
sheet.getRow(rouNum).getCell(4).setCellStyle(txtCellStyle);
|
sheet.getRow(rouNum).createCell(5).setCellValue(data[5][i]);
|
sheet.getRow(rouNum++).getCell(5).setCellStyle(txtCellStyle);
|
|
}
|
}
|
|
//生成电压特性比较图
|
private void CreateMonVolAnalyTable(HSSFWorkbook wb,String[][] data,String picstr){
|
HSSFSheet sheet = wb.createSheet("电压特性比较图");
|
|
HSSFCellStyle titleCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,true); //单元格样式
|
HSSFCellStyle txtCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,false); //单元格样式
|
|
sheet.setDefaultColumnWidth(9); // 设置单元格默认宽度
|
|
sheet.setColumnWidth(2, 19*256);
|
sheet.setColumnWidth(3, 19*256);
|
sheet.setColumnWidth(4, 19*256);
|
|
byte [] pic = null;
|
if(ServletUtils.isNotNull(picstr)){
|
try {
|
String[] url = picstr.split(",");
|
pic = new BASE64Decoder().decodeBuffer(url[1]);
|
} catch (IOException e) {
|
//e.printStackTrace();
|
}
|
}
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
if(pic != null){
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, 25 * 0 + 1, (short) 10, 25 * (0 + 1));
|
anchor.setAnchorType(3);
|
// 插入图片
|
patriarch.createPicture(anchor, wb.addPicture(pic,HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
}
|
|
int rouNum = 26;
|
sheet.createRow(rouNum);
|
float maxStart = Float.parseFloat(data[1][0]);
|
float maxEnd = Float.parseFloat(data[2][0]);
|
int maxStartNum = 1,maxEndNum = 1;
|
for(int i=0;i<data[0].length;i++){
|
if(Float.parseFloat(data[1][i])>maxStart){
|
maxStart = Float.parseFloat(data[1][i]);
|
maxStartNum = i+1;
|
}
|
|
if(Float.parseFloat(data[2][i])>maxEnd){
|
maxEnd = Float.parseFloat(data[2][i]);
|
maxEndNum = i+1;
|
}
|
}
|
|
sheet.getRow(rouNum).createCell(3).setCellValue("起始值(V)");
|
sheet.getRow(rouNum).getCell(3).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(4).setCellValue("结束值(V)");
|
sheet.getRow(rouNum++).getCell(4).setCellStyle(titleCellStyle);
|
sheet.createRow(rouNum);
|
sheet.getRow(rouNum).createCell(2).setCellValue("最高电压");
|
sheet.getRow(rouNum).getCell(2).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(3).setCellValue(maxStart+"(#"+maxStartNum+")");
|
sheet.getRow(rouNum).getCell(3).setCellStyle(txtCellStyle);
|
sheet.getRow(rouNum).createCell(4).setCellValue(maxEnd+"(#"+maxEndNum+")");
|
sheet.getRow(rouNum++).getCell(4).setCellStyle(txtCellStyle);
|
|
|
for(int i=0;i<data[0].length;i++){
|
sheet.createRow(rouNum);
|
sheet.getRow(rouNum).createCell(2).setCellValue(data[0][i]);
|
sheet.getRow(rouNum).getCell(2).setCellStyle(titleCellStyle);
|
sheet.getRow(rouNum).createCell(3).setCellValue(data[1][i]);
|
sheet.getRow(rouNum).getCell(3).setCellStyle(txtCellStyle);
|
sheet.getRow(rouNum).createCell(4).setCellValue(data[2][i]);
|
sheet.getRow(rouNum++).getCell(4).setCellStyle(txtCellStyle);
|
|
}
|
}
|
|
//生成单个图片工作簿
|
private void CreateTotalVolAnalyTable(HSSFWorkbook wb,String sheetName,String picstr){
|
HSSFSheet sheet = wb.createSheet(sheetName);
|
sheet.setDefaultColumnWidth(9); // 设置单元格默认宽度
|
|
byte [] pic = null;
|
if(ServletUtils.isNotNull(picstr)){
|
try {
|
String[] url = picstr.split(",");
|
pic = new BASE64Decoder().decodeBuffer(url[1]);
|
} catch (IOException e) {
|
//e.printStackTrace();
|
}
|
}
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
if(pic != null){
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, 25 * 0 + 1, (short) 10, 25 * (0 + 1));
|
anchor.setAnchorType(3);
|
// 插入图片
|
patriarch.createPicture(anchor, wb.addPicture(pic,HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
}
|
|
}
|
|
//生成电压数据表格
|
private void createMonVolDataTable(HSSFWorkbook wb,String[][] mon_vol_list,List<Batttestdata> groupinfo){
|
|
HSSFCellStyle titleCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,true); //单元格样式
|
HSSFCellStyle txtCellStyle = ExcelUtil.createCellStyle(wb,"宋体",12,true,false); //单元格样式
|
if(mon_vol_list != null && groupinfo != null){
|
int mon_count = mon_vol_list[0].length; //总单体数量
|
int total_col = mon_vol_list.length; //总记录条数
|
|
|
List<String> tabTh = new ArrayList<String>(){{
|
this.add("时间(HMS)");this.add("总电压(V)");this.add("总电流(A)");this.add("容量(AH)");this.add("标准电压(V)");
|
}};
|
for(int i=1;i<=mon_count;i++){
|
tabTh.add("#"+i);
|
}
|
int maxColCount = 221; //最大的列数
|
int tableCount = 1;
|
HSSFSheet sheet = null;
|
int nowColIndex = 0;
|
int rowNum = 0;
|
for(int i =0;i<=mon_vol_list.length;i++){
|
if(i%maxColCount == 0){
|
//创建新的工作簿
|
sheet = wb.createSheet("电压数据表格"+tableCount++);
|
for(int k = 0;k<tabTh.size();k++){
|
sheet.createRow(k);
|
sheet.setDefaultColumnWidth(12); // 设置单元格默认宽度
|
}
|
}
|
rowNum = 0;
|
nowColIndex = i%maxColCount;
|
|
if(i == 0){
|
//第一个数据表格
|
for(int j = 0 ;j<tabTh.size();j++){
|
//System.out.println(tabTh.get(j)+"\t"+j);
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(tabTh.get(j));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(titleCellStyle);;
|
}
|
}else{
|
//其他的表格
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(formatTestLong(groupinfo.get(i-1).getTest_timelong()));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(formartDouble(groupinfo.get(i-1).getGroup_vol(),1));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(formartDouble(groupinfo.get(i-1).getTest_curr(),1));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(formartDouble(groupinfo.get(i-1).getTest_cap(),0));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(formartDouble(calMaxToArr(mon_vol_list[i-1]),3));
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
for(int j = 0;j<mon_vol_list[i-1].length;j++){
|
sheet.getRow(rowNum).createCell(nowColIndex).setCellValue(mon_vol_list[i-1][j]);
|
sheet.getRow(rowNum++).getCell(nowColIndex).setCellStyle(txtCellStyle);
|
}
|
}
|
|
|
}
|
|
|
|
//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
|
}
|
}
|
|
|
///生成Excel报表
|
private void createExcel(HSSFWorkbook wb,HttpServletResponse resp){
|
try {
|
// 转码防止乱码
|
resp.addHeader("Content-Disposition", "attachment;filename="
|
+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
|
+ ".xls");
|
OutputStream out = resp.getOutputStream();
|
wb.write(out);
|
out.close();
|
} catch (UnsupportedEncodingException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 将日期格式转换成指定的字符串格式
|
* @param date 日期
|
* @param str 字符串的格式
|
* @return
|
*/
|
public static String formartDate(Date date,String str){
|
return new SimpleDateFormat(str).format(date);
|
}
|
|
//将秒转化成时:分:秒
|
public static String formatTestLong(long theTime) {
|
long theTime1 = 0;// 分
|
long theTime2 = 0;// 小时
|
// alert(theTime);
|
if(theTime >= 60) {
|
theTime1 = theTime/60;
|
theTime = theTime%60;
|
//alert(theTime1+"-"+theTime);
|
if(theTime1 >= 60) {
|
theTime2 = theTime1/60;
|
theTime1 = theTime1%60;
|
}
|
}
|
String result = (theTime<10?"0":"")+theTime;
|
if(theTime1 >= 0) {
|
result =(theTime1<10?"0":"")+theTime1+":"+result;
|
}
|
if(theTime2 >= 0) {
|
result =(theTime2<10?"0":"")+theTime2+":"+result;
|
}
|
//console.info(result);
|
return result;
|
}
|
|
//计算一个字符串数组的最大值并返回
|
public static double calMaxToArr(String[] arr){
|
double max = 0;
|
if(arr.length > 0){
|
max = Float.parseFloat(arr[0]);
|
for(int i=0;i<arr.length;i++){
|
if(Float.parseFloat(arr[i]) > max){
|
max = Float.parseFloat(arr[i]);
|
}
|
}
|
}
|
return max;
|
}
|
|
//保留指定的位数
|
public static double formartDouble(double b , int places){
|
return new BigDecimal(b).setScale(places, BigDecimal.ROUND_HALF_UP).doubleValue();
|
}
|
}
|
