导出excel

最新推荐文章于 2024-07-23 10:13:17 发布

weixin_33743661

最新推荐文章于 2024-07-23 10:13:17 发布

阅读量67

点赞数

文章标签： json java

原文链接：https://my.oschina.net/ldm95/blog/726111

版权

为什么80%的码农都做不了架构师？>>>

public static Map GetContextConfig(HttpServletRequest request,
           String nodeName) throws Exception {
       Map map = new HashMap();
       DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
       DocumentBuilder db = dbf.newDocumentBuilder();
       // 这个Document就是一个XML文件在内存中的镜像
       Document doc = db.parse(new File(request
               .getRealPath("/WEB-INF/susconfig.xml")));
       NodeList nodeList = doc.getElementsByTagName(nodeName);
       Node fatherNode = nodeList.item(0);
       // 把父节点的属性拿出来
       NodeList childNodes = fatherNode.getChildNodes();
       for (int j = 0; j < childNodes.getLength(); j++) {
           Node childNode = childNodes.item(j);
           // 如果这个节点属于Element ,再进行取值
           if (childNode instanceof Element) {
               map.put(childNode.getNodeName(), childNode.getFirstChild()
                       .getNodeValue());
           }
       }
       return map;
   }

// 导出excel方法
       function ToExcel() {
           var _UNIT_CODE = Ext.getCmp('UNIT_CODE').getValue();
           var _Year = Ext.getCmp('Year').getValue();
           var _Month = Ext.getCmp('Month').getValue();
           if(null == _UNIT_CODE || ''==_UNIT_CODE){
               Ext.Msg.alert('提示','部门不能为空！');
               return false;
           }
           if(null == _Year || ''==_Year){
               Ext.Msg.alert('提示','年份不能为空！');
               return false;
           }
           if(null == _Month || ''==_Month){
               Ext.Msg.alert('提示','月份不能为空！');
               return false;
           }
           var _Flag = myFlag;
           Ext.Ajax.request({
                       url : '../at/At02Action!loadBillData.action',
                       method : 'POST',
                       params : {
                       UNIT_CODE : _UNIT_CODE,
                       Flag : _Flag,
                       Year : _Year,
                       Month : _Month
               },
               success : function(response, options) {
                       var result = Ext.JSON.decode(response.responseText);
                       window.location.href = encodeURI(
                           basePath
                                   + 'at/At02Action!exportExcel.action?tdcf14='
                                   + result.tdcf14 + "&unit_code="
                                   + result.unit_code + "&tdcj01="
                                   + result.tdcj01.toFixed(2) + "&act="
                                   + result.act.toFixed(2) + "&price="
                                   + result.price.toFixed(2) + "&Year="
                                   + _Year + "&Month=" + _Month + "&myFlag="
                                   + myFlag, 'utf-8');
               },
               failure : function() {
               }
           });

       }

public String exportExcel() throws Exception {
       String tdcf14 = URLDecoder.decode(JsonAction.getParameter("tdcf14"), "UTF-8");
       String unit_code = JsonAction.getParameter("unit_code");
       String tdcj01 = JsonAction.getParameter("tdcj01");
       String act = JsonAction.getParameter("act");
       String price = JsonAction.getParameter("price");
       String Year = JsonAction.getParameter("Year");
       String Month = JsonAction.getParameter("Month");
       String myFlag = JsonAction.getParameter("myFlag");
       String room_desc = URLDecoder.decode(JsonAction.getParameter("room_desc"), "UTF-8"); //房间描述
       String room_code = JsonAction.getParameter("room_code");//房间编号

       String unit_desc = JsonAction.getParameter("unit_desc");//单位名称

       String flagUint = "";
       if(!"".equals(myFlag)){
           if("1".equals(myFlag)){
               myFlag = "电";
               flagUint = "(kWh)";
           }else if("2".equals(myFlag)){
               myFlag = "水";
               flagUint = "(m³)";
           }else if("3".equals(myFlag)){
               myFlag = "气";
               flagUint = "(m³)";
           }else if("4".equals(myFlag)){
               myFlag = "热";
               flagUint = "(MJ)";
           }else if("5".equals(myFlag)){
               myFlag = "电";
               flagUint = "(kWh)";
           }
       }
       HSSFWorkbook workBook = new HSSFWorkbook();
       HSSFCellStyle headerStyle = getHeaderStyle(workBook);
       HSSFCellStyle dataStyle = getDataStyle(workBook);
       HSSFSheet sheet = workBook.createSheet();
       workBook.setSheetName(0, "targetStockList");
       HSSFHeader header = sheet.getHeader();

       header.setCenter("标题");
       CellRangeAddress cellRange = null;
       cellRange = new CellRangeAddress(1, 1, 1, 4);
sheet.addMergedRegion(cellRange);
sheet.setColumnWidth(1, 7000);
sheet.setColumnWidth(2, 8000);
sheet.setColumnWidth(3, 7000);
sheet.setColumnWidth(4, 8000);
HSSFRow row1 = sheet.createRow(1);
       HSSFCell cell0 = row1.createCell(Short.valueOf("1"));
       if("5".equals(myFlag)){
           cell0.setCellValue(tdcf14+myFlag+"费结账单");
       }else{
           cell0.setCellValue(room_desc+myFlag+"费结账单");
       }
       cell0.setCellStyle(headerStyle);
       HSSFRow row11 = sheet.createRow(2);
       HSSFCell cell11 = row11.createCell(Short.valueOf("1"));
       HSSFCell cell12 = row11.createCell(Short.valueOf("2"));
       HSSFCell cell13 = row11.createCell(Short.valueOf("3"));
       HSSFCell cell14 = row11.createCell(Short.valueOf("4"));
       cell11.setCellValue("");
       cell12.setCellValue("");
       cell13.setCellValue("");
       cell14.setCellValue(" "+Year+"年-"+Month+"月");
       HSSFRow row01 = sheet.createRow(3);
       HSSFCell cell01 = row01.createCell(Short.valueOf("1"));
       HSSFCell cell02 = row01.createCell(Short.valueOf("2"));
       HSSFCell cell03 = row01.createCell(Short.valueOf("3"));
       HSSFCell cell04 = row01.createCell(Short.valueOf("4"));
       if("5".equals(JsonAction.getParameter("myFlag"))){//房间账单
           cell01.setCellValue("房间号");
           cell01.setCellStyle(dataStyle);
           cell02.setCellValue(room_code);
           cell02.setCellStyle(dataStyle);
           cell03.setCellValue("名称");
           cell03.setCellStyle(dataStyle);
           if (unit_desc == null || unit_desc.trim().equals("")) {
               cell04.setCellValue(room_desc);
           } else {
               cell04.setCellValue(unit_desc);
           }
           cell04.setCellStyle(dataStyle);
       }else {
           cell01.setCellValue("部门编码");
           cell01.setCellStyle(dataStyle);
           cell02.setCellValue(unit_code);
           cell02.setCellStyle(dataStyle);
           cell03.setCellValue("部门名称");
           cell03.setCellStyle(dataStyle);
           cell04.setCellValue(tdcf14);
           cell04.setCellStyle(dataStyle);
       }
       HSSFRow row0001 = sheet.createRow(4);
       HSSFCell cell0001 = row0001.createCell(Short.valueOf("1"));
       HSSFCell cell0002 = row0001.createCell(Short.valueOf("2"));
       HSSFCell cell0003 = row0001.createCell(Short.valueOf("3"));
       HSSFCell cell0004 = row0001.createCell(Short.valueOf("4"));

       cell0001.setCellValue("实际用"+myFlag + flagUint);
       cell0001.setCellStyle(dataStyle);
       cell0002.setCellValue(act);
       cell0002.setCellStyle(dataStyle);
       cell0003.setCellValue("单价(元)");
       cell0003.setCellStyle(dataStyle);
       cell0004.setCellValue(tdcj01);
       cell0004.setCellStyle(dataStyle);
       HSSFRow row00001 = sheet.createRow(5);
       HSSFCell cell00001 = row00001.createCell(Short.valueOf("1"));
       HSSFCell cell00002 = row00001.createCell(Short.valueOf("2"));
       HSSFCell cell00003 = row00001.createCell(Short.valueOf("3"));
       HSSFCell cell00004 = row00001.createCell(Short.valueOf("4"));
       cell00001.setCellValue("本月"+myFlag+"费");
       cell00001.setCellStyle(dataStyle);
       cell00002.setCellValue(price);
       cell00002.setCellStyle(dataStyle);
       cell00003.setCellValue("");
       cell00003.setCellStyle(dataStyle);
       cell00004.setCellValue("");
       cell00004.setCellStyle(dataStyle);

       HttpServletResponse response = (HttpServletResponse) ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE);
       // 通过Response把数据以Excel格式保存
       response.reset();
       response.setContentType("application/msexcel;charset=UTF-8");
       try {
           response.addHeader("Content-Disposition", "attachment;filename=\"" + new String(("账单信息表" + ".xls").getBytes("GBK"),"ISO8859_1") + "\"");
           OutputStream out = response.getOutputStream();
           workBook.write(out);
           out.flush();
           out.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
       return null;

}

public HSSFCellStyle getHeaderStyle(HSSFWorkbook workBook){
       //边栏单元格字体
HSSFFont headerFont = workBook.createFont();
//ARIAL字体
headerFont.setFontName(HSSFFont.FONT_ARIAL);
//10号
headerFont.setFontHeightInPoints((short)20);
//粗体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//边栏单元格样式
HSSFCellStyle headerStyle = workBook.createCellStyle();
headerStyle.setFont(headerFont);
//垂直居中
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//水平居中
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return headerStyle;
   }
   public HSSFCellStyle getDataStyle(HSSFWorkbook workBook){
       HSSFFont dataFont = workBook.createFont();
//ARIAL字体
dataFont.setFontName(HSSFFont.FONT_ARIAL);
//10号
dataFont.setFontHeightInPoints((short)15);
//非粗体
dataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//数据单元格样式
HSSFCellStyle dataStyle = workBook.createCellStyle();
dataStyle.setFont(dataFont);
//垂直居中
dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//水平居中
dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//左右上下边框样式
dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//左右上下边框颜色(深蓝色)
dataStyle.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
dataStyle.setRightBorderColor(HSSFColor.DARK_BLUE.index);
dataStyle.setTopBorderColor(HSSFColor.DARK_BLUE.index);
dataStyle.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
return dataStyle;
   }

package com.fun.excel;

import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

/**
* @author Administrator 导出Excel
*/
@SuppressWarnings("serial")
public class excelAction extends ActionSupport {

   @SuppressWarnings("deprecation")
   @Override
   public String execute() throws Exception {
       // 获取问题列表
       // Map map = new HashMap();
       // List<Suggestion> targetStockList =
       // suggestionService.getSuggestionList(map);
       //
       // //创建一个新的Excel
       HSSFWorkbook workBook = new HSSFWorkbook();
       // //创建sheet页
       HSSFSheet sheet = workBook.createSheet();
       // //sheet页名称
       workBook.setSheetName(0, "targetStockList");
       // //创建header页
       HSSFHeader header = sheet.getHeader();
       // //设置标题居中
       header.setCenter("标题");
       //
       // 设置第一行为Header
       HSSFRow row = sheet.createRow(0);
       HSSFCell cell0 = row.createCell(Short.valueOf("0"));
       HSSFCell cell1 = row.createCell(Short.valueOf("1"));
       HSSFCell cell2 = row.createCell(Short.valueOf("2"));

       // 设置字符集
//       cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
//       cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
//       cell2.setEncoding(HSSFCell.ENCODING_UTF_16);

       cell0.setCellValue("问题标题");
       cell1.setCellValue("问题描述");
       cell2.setCellValue("反馈时间");
       //
       //
       // if(targetStockList != null && !targetStockList.isEmpty()) {
       // for(int i = 0; i < targetStockList.size(); i++) {
       // Suggestion targetStock = targetStockList.get(i);
       // row = sheet.createRow(i + 1);
       // cell0 = row.createCell(Short.valueOf("0"));
       // cell1 = row.createCell(Short.valueOf("1"));
       // cell2 = row.createCell(Short.valueOf("2"));
       //
       //
       // // 设置字符集
       // cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
       // cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
       // cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
       //
       //
       // cell0.setCellValue(targetStock.getType());
       // cell1.setCellValue(targetStock.getContent());
       // cell2.setCellValue(targetStock.getPublishTime());
       //
       //
       //
       // sheet.setColumnWidth((short) 0, (short) 4000);
       // sheet.setColumnWidth((short) 1, (short) 4000);
       // sheet.setColumnWidth((short) 2, (short) 4000);
       // }
       // }
       //
       HttpServletResponse response = (HttpServletResponse) ActionContext
               .getContext().get(
                       org.apache.struts2.StrutsStatics.HTTP_RESPONSE);
       // 通过Response把数据以Excel格式保存
       response.reset();
       response.setContentType("application/msexcel;charset=UTF-8");
       try {
           response.addHeader("Content-Disposition", "attachment;filename=\""
                   + new String(("用户意见信息表" + ".xls").getBytes("GBK"),
                           "ISO8859_1") + "\"");
           OutputStream out = response.getOutputStream();
           workBook.write(out);
           out.flush();
           out.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
       return null;