package com.train.test;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.http.HttpServletResponse;
import static java.lang.Integer.parseInt;
/**
* @ClassName: TechnicalRenovationController
* @Description:
* @author: wlk
* @date: 2018-08-27 19:31:28
*/
@Controller
@RequestMapping("/exportTechnical")
public class TechnicalRenovationController {
/**.
* 技术改造导出
*/
@RequestMapping("/enovation")
@ResponseBody
public void exportTechnical(HttpServletResponse response) {
try{
//1.动车组、2.配属动车所、3.车组号、4.完成日期、5.技改/源头质量项目内容、6.车型、7.源头整治、8.变更性质、9.路局批复文号、10.段批复文号、11.段批复时间、
// 12.结合修程(专项/二/三/四/五级修)、13.开始日期、14.结束日期、15.计划数量(380BL)、16.完成数量(380BL)、17.计划数量(380B)、18.完成数量(380B)19,是否完成
//只是把值放到了一个字符串里,怎么取数据自己写,只要把值取到放到指定位置就行。
String temp ="{\"status\":\"success\",\"msg\":\"\",\"" +
"data\":[{\"1\":\"CRH380BL(1005定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\"," +
"\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}," +
"{\"1\":\"CRH380BL(1005定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"第27项\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"正在实施\"}," +
"{\"1\":\"CRH380BL(1005定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"第27项\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已发函取消\"}," +
"{\"1\":\"CRH380BL(1005定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}," +
"{\"1\":\"CRH380B(556定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"第27项\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}," +
"{\"1\":\"CRH380B(556定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}," +
"{\"1\":\"CRH380B(556定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}," +
"{\"1\":\"CRH380B(556定员)\",\"2\":\"杭州所\",\"3\":\"3585\",\"4\":\"2018-10-22\",\"5\":\"司机台COMPACT I/O地址线缩短\",\"6\":\"380BBL\",\"7\":\"第27项\",\"8\":\"设备改造\",\"9\":\"辆动函〔2017〕96号\",\"10\":\"2017年第53号\",\"11\":\"2017/4/14\",\"12\":\"高级修\",\"13\":\"2017/3/1\n\",\"14\":\"2018/6/28\n\",\"15\":\"12\",\"16\":\"22\",\"17\":\"59\",\"18\":\"22\",\"19\":\"已完成\"}]}";
JSONObject jsonobject = JSON.parseObject(temp);;
JSONArray array = jsonobject.getJSONArray("data");
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("技术改造");;
sheet.createFreezePane(3, 3);
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 3500);
//列数不确定循环指定
for (int i = 3; i <= array.size()+3; i++){
sheet.setColumnWidth(i, 4000);
}
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
row.setHeightInPoints(35);
HSSFCell cell = row.createCell(0);
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setFillForegroundColor(IndexedColors.WHITE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//红
HSSFCellStyle styler = wb.createCellStyle();
styler.setWrapText(true);
styler.setFillForegroundColor(IndexedColors.RED.index);
styler.setFillPattern(FillPatternType.SOLID_FOREGROUND);
styler.setBorderBottom(BorderStyle.THIN);
styler.setBorderLeft(BorderStyle.THIN);
styler.setBorderRight(BorderStyle.THIN);
styler.setBorderTop(BorderStyle.THIN);
styler.setAlignment(HorizontalAlignment.CENTER);
styler.setVerticalAlignment(VerticalAlignment.CENTER);
// 黄
HSSFCellStyle styley = wb.createCellStyle();
styley.setWrapText(true);
styley.setFillForegroundColor(IndexedColors.YELLOW.index);
styley.setFillPattern(FillPatternType.SOLID_FOREGROUND);
styley.setBorderBottom(BorderStyle.THIN);
styley.setBorderLeft(BorderStyle.THIN);
styley.setBorderRight(BorderStyle.THIN);
styley.setBorderTop(BorderStyle.THIN);
styley.setAlignment(HorizontalAlignment.CENTER);
styley.setVerticalAlignment(VerticalAlignment.CENTER);
//绿
HSSFCellStyle styleg = wb.createCellStyle();
styleg.setWrapText(true);
styleg.setFillForegroundColor(IndexedColors.SEA_GREEN.index);
styleg.setFillPattern(FillPatternType.SOLID_FOREGROUND);
styleg.setBorderBottom(BorderStyle.THIN);
styleg.setBorderLeft(BorderStyle.THIN);
styleg.setBorderRight(BorderStyle.THIN);
styleg.setBorderTop(BorderStyle.THIN);
styleg.setAlignment(HorizontalAlignment.CENTER);
styleg.setVerticalAlignment(VerticalAlignment.CENTER);
//表头
HSSFCellStyle styled = wb.createCellStyle();
styled.setWrapText(true);
styled.setFillForegroundColor(IndexedColors.DARK_TEAL.index);
styled.setFillPattern(FillPatternType.SOLID_FOREGROUND);
styled.setBorderBottom(BorderStyle.THIN);
styled.setBorderLeft(BorderStyle.THIN);
styled.setBorderRight(BorderStyle.THIN);
styled.setBorderTop(BorderStyle.THIN);
styled.setAlignment(HorizontalAlignment.CENTER);
styled.setVerticalAlignment(VerticalAlignment.CENTER);
//灰色
HSSFCellStyle stylegr = wb.createCellStyle();
stylegr.setWrapText(true);
stylegr.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
stylegr.setFillPattern(FillPatternType.SOLID_FOREGROUND);
stylegr.setBorderBottom(BorderStyle.THIN);
stylegr.setBorderLeft(BorderStyle.THIN);
stylegr.setBorderRight(BorderStyle.THIN);
stylegr.setBorderTop(BorderStyle.THIN);
stylegr.setAlignment(HorizontalAlignment.CENTER);
stylegr.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont fontd = wb.createFont();
fontd.setColor(HSSFColor.WHITE.index);
fontd.setFontName("黑体");
styled.setFont(fontd);
HSSFCellStyle style1 = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 18); //字体高度
font.setBold(true);
font.setFontName("宋体");
style1.setFont(font);
style1.setFillForegroundColor(IndexedColors.WHITE.index);
style1.setAlignment(HorizontalAlignment.CENTER);
style1.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle style2 = wb.createCellStyle();
HSSFFont font2 = wb.createFont();
font2.setFontHeightInPoints((short) 12); //字体高度
font2.setFontName("黑体");
style2.setFillForegroundColor(IndexedColors.WHITE.index);
style2.setFont(font2);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellValue("CRH3型动车组技术改造汇总报表(整理中)");
cell.setCellStyle(styled);
CellRangeAddress region = new CellRangeAddress(0, 0, (short)0, (short)2);
sheet.addMergedRegion(region);
for (int i = 3; i <array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("19")+"");//获取值并赋值
if(((JSONObject)array.get(i-3)).get("19").equals("已完成")){
cell.setCellStyle(styleg);
}else if(((JSONObject)array.get(i-3)).get("19").equals("正在实施")){
cell.setCellStyle(styley);
}else{
cell.setCellStyle(styler);
}
}
//序号
row = sheet.createRow(1);
row.setHeightInPoints(15);
cell = row.createCell((short) 0);
//直接赋值
int projectNum = 24;
cell.setCellValue("2017年共涉及技术改造项目数:"+projectNum);
//合并两行两列
CellRangeAddress region1 = new CellRangeAddress(1, 2, (short)0, (short)1);
sheet.addMergedRegion(region1);
setRegionStyle(sheet, region1, style);
cell = row.createCell((short) 2);
cell.setCellValue("序号");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(i-2);
cell.setCellStyle(style);
}
//技改/源头质量项目内容
row = sheet.createRow(2);
row.setHeightInPoints(60);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("技改/源头质量项目内容");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("5")+"");
cell.setCellStyle(style);
}
//车型
row = sheet.createRow(3);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
String completed ="15";//计算
cell.setCellValue("已完成:"+completed);
//合并两行一列
CellRangeAddress region2 = new CellRangeAddress(3, 4, (short)0, (short)0);
sheet.addMergedRegion(region2);
setRegionStyle(sheet, region2, style);
cell = row.createCell((short) 1);
String rate ="15%";//计算
cell.setCellValue("完成率:"+ rate);
CellRangeAddress region3 = new CellRangeAddress(3, 4, (short)1, (short)1);
sheet.addMergedRegion(region3);
setRegionStyle(sheet, region3, style);
cell = row.createCell((short) 2);
cell.setCellValue("车型");
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("6")+"");
cell.setCellStyle(style);
}
//源头整治
row = sheet.createRow(4);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("源头整治");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("7")+"");
if(((JSONObject)array.get(i-3)).get("7").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//变更性质
row = sheet.createRow(5);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("2017年共涉及技术改造项目数");
//合并两行两列
CellRangeAddress region4 = new CellRangeAddress(5, 6, (short)0, (short)1);
sheet.addMergedRegion(region4);
setRegionStyle(sheet, region4, style);
cell = row.createCell((short) 2);
cell.setCellValue("变更性质");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("8")+"");
if(((JSONObject)array.get(i-3)).get("8").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//路局批复文号
row = sheet.createRow(6);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("路局批复文号");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("9")+"");
if(((JSONObject)array.get(i-3)).get("9").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//段批复文号
row = sheet.createRow(7);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("已完成:"+completed);
//合并两行一列
CellRangeAddress region5 = new CellRangeAddress(7, 8, (short)0, (short)0);
sheet.addMergedRegion(region5);
setRegionStyle(sheet, region5, style);
cell = row.createCell((short) 1);
cell.setCellValue("完成率:"+ rate);
CellRangeAddress region6 = new CellRangeAddress(7, 8, (short)1, (short)1);
sheet.addMergedRegion(region6);
setRegionStyle(sheet, region6, style);
cell = row.createCell((short) 2);
cell.setCellValue("段批复文号");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("10")+"");
if(((JSONObject)array.get(i-3)).get("10").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//段批复时间
row = sheet.createRow(8);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("段批复时间");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("11")+"");
if(((JSONObject)array.get(i-3)).get("11").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//结合修程(专项/二/三/四/五级修)
row = sheet.createRow(9);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("结合修程(专项/二/三/四/五级修)");
//合并两行一列
CellRangeAddress region7 = new CellRangeAddress(9, 9, (short)0, (short)2);
sheet.addMergedRegion(region7);
setRegionStyle(sheet, region7, style);
cell = row.createCell((short) 3);
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("12")+"");
if(((JSONObject)array.get(i-3)).get("12").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//开始日期
row = sheet.createRow(10);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("执行周期");
//合并两行两列
CellRangeAddress region8 = new CellRangeAddress(10, 11, (short)0, (short)1);
sheet.addMergedRegion(region8);
setRegionStyle(sheet, region8, style);
cell = row.createCell((short) 2);
cell.setCellValue("开始日期");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("13")+"");
if(((JSONObject)array.get(i-3)).get("13").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//结束日期
row = sheet.createRow(11);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("结束日期");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("14")+"");
if(((JSONObject)array.get(i-3)).get("14").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//计划数量(380BL)
row = sheet.createRow(12);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("执行情况(380BL)");
//合并两行两列
CellRangeAddress region9 = new CellRangeAddress(12, 13, (short)0, (short)1);
sheet.addMergedRegion(region9);
setRegionStyle(sheet, region9, style);
cell = row.createCell((short) 2);
cell.setCellValue("计划数量");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("15")+"");
if(((JSONObject)array.get(i-3)).get("15").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//完成数量(380BL)
row = sheet.createRow(13);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("完成数量");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("16")+"");
if(((JSONObject)array.get(i-3)).get("16").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//计划数量(380B)
row = sheet.createRow(14);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("执行情况(380B)");
//合并两行两列
CellRangeAddress region10 = new CellRangeAddress(14, 15, (short)0, (short)1);
sheet.addMergedRegion(region10);
setRegionStyle(sheet, region10, style);
cell = row.createCell((short) 2);
cell.setCellValue("计划数量");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("17")+"");
if(((JSONObject)array.get(i-3)).get("17").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//完成数量(380B)
row = sheet.createRow(15);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("完成数量");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue(((JSONObject)array.get(i-3)).get("18")+"");
if(((JSONObject)array.get(i-3)).get("18").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
//完成日期
row = sheet.createRow(16);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue("动车组车型");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("配属动车所");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("车组号");
cell.setCellStyle(style);
for (int i = 3; i < array.size()+3; i++) {
cell = row.createCell((short) i);
cell.setCellValue("完成日期");
cell.setCellStyle(style);
}
/* //按动车组分别统计条数
String temp1 ="{\"status\":\"success\",\"msg\":\"\",\"" +
"data\":[{\"6\":\"CRH380BL1005\",\"20\":\"4\"},{\"6\":\"CRH380BL556\",\"20\":\"4\"}]}";
JSONObject jsonobject1 = JSON.parseObject(temp1);;
JSONArray array1 = jsonobject1.getJSONArray("data");*/
//循环存值
for (int i = 0; i < array.size(); i++) {
row = sheet.createRow(17+i);
row.setHeightInPoints(30);
cell = row.createCell((short) 0);
cell.setCellValue(((JSONObject) array.get(i)).get("1") + "");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue(((JSONObject)array.get(i)).get("2")+"");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue(((JSONObject)array.get(i)).get("3")+"");
cell.setCellStyle(style);
for (int k = 3; k < array.size()+3; k++) {
cell = row.createCell((short) k);
cell.setCellValue(((JSONObject)array.get(i)).get("4")+"");
if(((JSONObject)array.get(i)).get("4").equals("")){
cell.setCellStyle(stylegr);
}else {
cell.setCellStyle(style);
}
}
}
// 将文件存到指定位置
HSSFPrintSetup printSetup = sheet.getPrintSetup();
printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
sheet.setDisplayGridlines(false);
sheet.setPrintGridlines(false);
//sheet.autoSizeColumn(0); //自动调整列宽
// 导出日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String exportTime = sdf.format(new Date());
OutputStream output= null;
output = response.getOutputStream();
response.reset();
String url = "attachment; filename=\""+ new String(("技术改造-"+exportTime).getBytes( "gbk" ), "ISO8859-1" )+".xls" + "\"";
response.setHeader("Content-disposition", url ); // 设定输出文件头
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
private void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
cell.setCellStyle(cs);
}
}
}
}