导出excel

最新推荐文章于 2024-04-30 11:06:11 发布

mjf853912229

最新推荐文章于 2024-04-30 11:06:11 发布

阅读量234

点赞数 1

文章标签： excel

本文链接：https://blog.csdn.net/mjf853912229/article/details/77849793

版权

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.icss.j2ee.servlet.ServletBase;
import com.icss.j2ee.util.URLDecoder;
import com.icss.resourceone.sdk.framework.Context;
import com.icss.resourceone.sdk.framework.EntityException;
import com.icss.resourceone.sdk.framework.Organization;
import com.zhsh.supervision.handler.WjsxcxHandler;
import com.zhsh.supervision.handler.ZglzHandler;
import com.zhsh.supervision.utils.DBUtils;
import com.zhsh.supervision.utils.DBUtils.DataSource;
import com.zhsh.supervision.vo.OrgVO;
import com.zhsh.supervision.vo.ProblemVO;
import com.zhsh.supervision.vo.TjVO;
import com.zhsh.supervision.vo.XmlbVO;
import com.zhsh.supervision.vo.ZTreeVO;

public class ExcelExportServlet extends ServletBase {

/**
*
*/
private static final long serialVersionUID = 1L;

public void doGet(HttpServletRequest request, HttpServletResponse response,List list)
throws ServletException, IOException {
response.setContentType("octets/stream");
// response.addHeader("Content-Disposition", "attachment;filename=test.xls");
String excelName = "项目审计事项表";
//转码防止乱码
response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "iso8859-1" )+".xls");
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String[] headers = new String[]{"序号","项目","项目类型","科室","审计事项","问题类型","问题金额","到位金额","非金额问题数","非金额问题到位数","审计文书(文号)","文书出具时间","整改时间","整改结果","二次跟踪","三次跟踪","审批状态","销号时间"};
try {
ServletOutputStream out=response.getOutputStream();
exportExcel("audit",headers, list, out,"yyyy-MM-dd");
out.flush();
out.close();
System.out.println("excel导出成功!");

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

public void doPost(HttpServletRequest req, HttpServletResponse repo,List list)
throws ServletException, IOException {
doGet(req, repo,list);
}
/**
*
* @throws UnsupportedEncodingException
* @Description: 生成excel并导出到客户端（本地）
* @Auther: lujinyong
*/
protected void exportExcel(String title,String[] headers,List list,OutputStream out,String pattern) throws UnsupportedEncodingException{
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
//设置表格默认列宽度为15个字符
sheet.setDefaultColumnWidth((short) 20);
//设置标题样式
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置标题字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把字体应用到当前的样式
style.setFont(font);
// 设置正文样式,用于设置内容样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置正文字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//把字体应用到当前的样式
style2.setFont(font2);
//设置头部样式
HSSFCellStyle styleBold = workbook.createCellStyle();
//产生头部标题行并合并相应单元格
styleBold.setFillForegroundColor((short) 13);//头部设置背景色
styleBold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框:
styleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
styleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
styleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
styleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置头部居中:
styleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
//设置头部字体:
HSSFFont font3 = workbook.createFont();
/*font3.setFontName("黑体");
font3.setFontHeightInPoints((short) 16);//设置字体大小 */
font3.setFontName("仿宋_GB2312");
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font3.setFontHeightInPoints((short)20);
styleBold.setFont(font3);
HSSFRow row_1 = sheet.createRow(0);
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 8));
sheet.addMergedRegion(new Region(0, (short) 9, 0, (short) 25));
HSSFCell cell_1 = row_1.createCell((short)0);
cell_1.setCellStyle(styleBold);
cell_1.setEncoding(HSSFCell.ENCODING_UTF_16);
String text_1 = URLDecoder.decode("项目情况", "UTF-16");
cell_1.setCellValue(text_1);
HSSFCell cell_2 = row_1.createCell((short)9);
cell_2.setCellStyle(styleBold);
cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
String text_2 = URLDecoder.decode("整改情况", "UTF-16");
cell_2.setCellValue(text_2);

HSSFRow row = sheet.createRow(1);

for(int i = 0; i<headers.length;i++){
HSSFCell cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
String text = URLDecoder.decode(headers[i], "UTF-16");
cell.setCellValue(text);

}

int _i=1;
try {
for(Iterator i = list.iterator(); i.hasNext();){
ProblemVO pr=(ProblemVO)i.next();
String xmmc=pr.getXMMC();
if(xmmc!=null&&xmmc.length()>0){
URLDecoder.decode(xmmc, "UTF-16");
}
String xmlx=pr.getXMLX();
if(xmlx!=null&&xmlx.length()>0){
URLDecoder.decode(xmlx, "UTF-16");
}
String ksmc=pr.getKSMC();
if(ksmc!=null&&ksmc.length()>0){
URLDecoder.decode(ksmc, "UTF-16");
}
String sxmc=pr.getSXMC();
if(sxmc!=null&&sxmc.length()>0){
URLDecoder.decode(sxmc, "UTF-16");
}
String wtlx=pr.getWTLX();
if(wtlx!=null&&wtlx.length()>0){
URLDecoder.decode(wtlx, "UTF-16");
}
String wtje=pr.getWTJG();
String dwje=pr.getDWJE();
String fjewts=pr.getFJEWTDWS();
String fjewtdws=pr.getFJEWTDWS();
String sjwf=pr.getWSLB();
if(sjwf!=null&&sjwf.length()>0){
URLDecoder.decode(sjwf, "UTF-16");
}
String wswh=pr.getWSWH();
if(wswh!=null&&wswh.length()>0){
URLDecoder.decode(wswh, "UTF-16");
}
sjwf=sjwf+"("+wswh+")";
String wswjsj="";
String zgsj=pr.getZGSJ();
String zgjg=pr.getBK3();
if(zgjg!=null&&zgjg.length()>0){
URLDecoder.decode(zgjg, "UTF-16");
}

String ercigz=pr.getBK4();
if(ercigz!=null&&ercigz.length()>0){
URLDecoder.decode(ercigz, "UTF-16");
}
String sancigz=pr.getBK5();
if(sancigz!=null&&sancigz.length()>0){
URLDecoder.decode(sancigz, "UTF-16");
}
String sxzt=pr.getSXZT();
if("整改审核中".equals(sxzt)){
sxzt="销号中";
}
if(sxzt!=null&&sxzt.length()>0){
URLDecoder.decode(sxzt, "UTF-16");
}
String xhsj=pr.getXHQRSJ();
int j=0;
row = sheet.createRow(_i);
HSSFCell _u=null;

_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(_i);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(xmmc);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(xmlx);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(ksmc);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(sxmc);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(wtlx);
_u=row.createCell((short)j++);
_u.setCellValue(wtje);
_u=row.createCell((short)j++);
_u.setCellValue(dwje);
_u=row.createCell((short)j++);
_u.setCellValue(fjewts);
_u=row.createCell((short)j++);
_u.setCellValue(fjewtdws);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(sjwf);
_u=row.createCell((short)j++);
_u.setCellValue(wswjsj);
_u=row.createCell((short)j++);
_u.setCellValue(zgsj);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(zgjg);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(ercigz);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(sancigz);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(sxzt);
_u=row.createCell((short)j++);
_u.setEncoding(HSSFCell.ENCODING_UTF_16);
_u.setCellValue(xhsj);
_i++;
}
workbook.write(out);

} catch (IOException e) {
e.printStackTrace();
}
}

@Override
protected void performTask(HttpServletRequest req, HttpServletResponse repo)
throws ServletException, IOException {

String exp_xmmc="";
Map<String,String> map=new HashMap<String,String>();
String exp_xmmcs=req.getParameter("exp_xmmc");

if(exp_xmmcs!=null&&!"".equals(exp_xmmcs)){
String[] expxmmc = exp_xmmcs.split(",");
StringBuffer bf=new StringBuffer();
for(int i=0;i<expxmmc.length;i++){
bf.append( "'"+expxmmc[i]+"',");
}
exp_xmmc=bf.toString();
}
if(null!=exp_xmmc&&!("").equals(exp_xmmc)){
exp_xmmc = exp_xmmc.substring(0, exp_xmmc.length()-1);
}

map.put("exp_xmmc", exp_xmmc);

Connection conn = DBUtils.createDBConnect(Context.getInstance(), DataSource.Supervision);

WjsxcxHandler nh = new WjsxcxHandler(conn);
// 通过项目的uuid查询下面的问题
List prolist = nh.getExpList(req, repo, conn, map);
if(prolist.isEmpty()){
//repo.getWriter().append("无项目信息").flush();
PrintWriter out = repo.getWriter();
out.print("<script type='text/javascript'>alert('无项目信息')</script>");
out.print("<script type='text/javascript'>window.history.go(-1)</script>");
out.flush();
out.close();
}else{

DBUtils.closeDBConnect(conn);
ExcelExportServlet es=new ExcelExportServlet();
es.doPost(req, repo,prolist);
}

}