[java]代码库
package com.projectcycle.second.report;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang.StringUtils;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForward;
import com.projectcycle.business.utils.DBManager;
@SuppressWarnings("deprecation")
public class MonthDetailExcel extends Action {
String titleName = "";
@SuppressWarnings("unchecked")
public void dumpExcel(HttpServletRequest request,
HttpServletResponse response,String datasql, String excelType,
String fileName_, String[] heads, String[] param, String[] parame,String[]xy,String[] xye,Integer width,Integer height,Integer Pwidth,Integer Pheight) {
DBManager db = new DBManager();
OutputStream os=null;
try {
System.out.println("-----开始导出excel------");
Date date = new Date();
String dateStr = new SimpleDateFormat("yyyyMMddhhmmss").format(date);
String pathDownload =request.getRealPath("/")+"download/excelTemp/";
isChartPathExist(pathDownload);
titleName=fileName_;
String fileName = fileName_ + dateStr + ".xls";
String pathOfExcel = pathDownload + fileName;
String[]result =new String[2];
result[0]=fileName;
result[1]=pathOfExcel;
String pathOfPicture = pathDownload + "company.png";
List<Map<String,String>>datalist=new ArrayList<Map<String,String>>();
db.newConnection();
Vector vector = db.executeQueryVector3LowerCase(datasql.toString());
if (vector.size()>0||!vector.isEmpty()) {
for(int i=0;i<vector.size();i++){
Hashtable ht =(Hashtable)vector.get(i);
Map<String, String> map = new HashMap<String, String>();
for(int j=0;j<param.length;j++){
map.put(param[j], getStringValue((String) ht.get(param[j])));
}
datalist.add(map);
}
}
os= new FileOutputStream(pathOfExcel);
if("1".equals(excelType)){//正常的grid
CreateExcel(os, datalist,heads,param,parame);
}
response.setContentType("appliction/json;charset=utf-8");
response.getWriter().write(fileName+","+pathOfExcel);
} catch (Exception e) {
e.printStackTrace();
}finally{
db.closeConnection();
if(os!=null){
try {
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* 创建excel表格
*
* @param os
* @param list
* @param heads
* @param param
* @param parame
*/
@SuppressWarnings("unchecked")
public void CreateExcel(OutputStream os, List list, String[] heads,
String[] param, String[] parame) {
try {
// 创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(os);
// 创建一页
WritableSheet sheet = workbook.createSheet("第一页", 0);
// 构造表头
WritableCellFormat wcflist = new WritableCellFormat(); // 单元格定义
wcflist.setAlignment(Alignment.CENTRE);
wcflist.setVerticalAlignment(VerticalAlignment.CENTRE);
wcflist.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
WritableCellFormat wcflist1 = new WritableCellFormat(); // 单元格定义
wcflist1.setAlignment(Alignment.LEFT);
wcflist1.setVerticalAlignment(VerticalAlignment.CENTRE);
wcflist1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
wcflist1.setWrap(true);
// 加粗 10
WritableFont font1 = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat newFormat1 = new WritableCellFormat(font1); // 单元格定义
newFormat1.setAlignment(Alignment.CENTRE);
newFormat1.setBackground(jxl.format.Colour.GRAY_25);
newFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
newFormat1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
WritableCellFormat newFormat = new WritableCellFormat(font1); // 单元格定义
newFormat.setAlignment(Alignment.CENTRE);
newFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
newFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
Label title = new Label(0, 0, titleName);
sheet.mergeCells(0, 0, heads.length-1, 0);// 起始列,起始行,终止列,终止行
title.setCellFormat(newFormat);
sheet.addCell(title);
sheet.setRowView(0, 600);
Label head;
for (int i = 0; i < heads.length; i++) {
head = new Label(i, 1, heads[i]);
head.setCellFormat(newFormat1);
sheet.addCell(head);
if (heads[i].length() * 2 < 20) {
sheet.setColumnView(i, 20);
} else {
sheet.setColumnView(i, heads[i].length() * 2);
}
sheet.setRowView(1, 400);
sheet.setRowView(2, 400);
}
String nameCell;
String cellTemp = "";
String deptName="";
int k = 0;
Label bodys;
for (int j = 0; j < param.length; j++) {
for (int i = 0; i < list.size(); i++) {
Map<String, String> map = (Map<String, String>) list.get(i);
String name = param[j];
nameCell = (String) map.get(name);
int w=0;
String syjh=(String) map.get("syjh");
deptName =(String) map.get("departmentname");
String xyjh=(String) map.get("xyjh");
if (param[j].equals("departmentname")) {
if (deptName.equals(cellTemp)) {
k++;
if (i == list.size() - 1) {
sheet.mergeCells(j, (i + 2 - k), j, (i +2));
k = 0;
}
} else {
cellTemp = deptName;
sheet.mergeCells(j, (i+1 - k), j, (i+1));
//System.out.println(k);
k = 0;
}
}
if((syjh.length())>=(xyjh.length())){
w=syjh.length()/14;
}else{
w=xyjh.length()/14;
}
if(("syjh".equals(name))||("xyjh".equals(name))){
nameCell=nameCell.replace(",", "\012");
}
bodys = new Label(j, i + 2, nameCell);
if(("syjh".equals(name))||("xyjh".equals(name))){
bodys.setCellFormat(wcflist1);
}else{
bodys.setCellFormat(wcflist);
}
sheet.addCell(bodys);
sheet.setRowView(i + 2, (w+1)*400);
}
cellTemp = "";
k = 0;
}
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 下载的方法
* @param pathOfExcel
* @param fileName
* @param request
* @param response
* @return
*/
public ActionForward download(String pathOfExcel, String fileName,
HttpServletRequest request, HttpServletResponse response) {
try {
request.setCharacterEncoding("iso-8859-1");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
OutputStream os = null;
InputStream is = null;
String filePath = pathOfExcel;// 项目路径
try {
File downloadFile = new File(filePath);
is = new FileInputStream(downloadFile);
bis = new BufferedInputStream(is);
os = response.getOutputStream();
bos = new BufferedOutputStream(os);
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题
fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/-excel");// 文件类型contenttype
response.setHeader("Content-Disposition", "attachment; filename="
+ fileName); // 关键部分,打开一个下载框
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = bis.read(buffer, 0, 8192)) != -1) {
bos.write(buffer, 0, bytesRead);
}
bos.flush();
is.close();
bis.close();
os.close();
bos.close();
} catch (Exception e) {
}
System.gc();
return null;
}
/**
* 删除图片
* @param pathOfPicture:图片的路径
*/
public void deleteImage(String pathOfPicture){
try {
File file = new File(pathOfPicture);
if (file.exists()) {
boolean d = file.delete();
if (d) {
System.out.print("删除成功!");
} else {
System.out.print("删除失败!");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 判断文件夹是否存在,如果不存在则新建
*
* @param chartPath
*/
@SuppressWarnings("unused")
private void isChartPathExist(String chartPath) {
File file = new File(chartPath);
if (!file.exists()) {
file.mkdirs();
// log.info("CHART_PATH="+CHART_PATH+"create.");
}
}
public String getStringValue(String sv) {
return StringUtils.isBlank(sv)?"":sv;
}
public int getIntValue(String sv) {
return Integer.parseInt(StringUtils.isBlank(sv)?"0":sv);
}
}