/*
* 创建人:cwq
* 创建时间 :20160105
* 创建用于 用于临时文件的下载
*
* */
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
// import com.sunrise.foundation.utils.RequestWrapper;
// import com.sunrise.framework.core.ApplicationManager;
// import com.sunrise.framework.struts2.Header;
// import com.sunrise.framework.struts2.JsonObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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 org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
public class DownloadExcel {
// private RequestWrapper request = ApplicationManager.getRequest();
//tomcat地址
// private String Path = request.getSession().getServletContext().getRealPath("/");
private File resultFile = null;
private String sheetName = "sheet";
private int sheetRow = 0;
private String fileName=null;
private String [] gaugeHead = {"第一列","第二列"};
private String [][] cellValue = {{"1","2"},{"1","2"},{"3","4"}};
private Map<String, Object> params = new HashMap<String, Object>();
private String gauge = null;
private String cell = null;
// public JsonObject main()throws Exception{
// if(request.getParameter("sheetName")!=null) sheetName = request.getParameter("sheetName");
// if(request.getParameter("sheetRow")!=null) sheetRow = Integer.parseInt(request.getParameter("sheetRow"));
// if(request.getParameter("gaugeHead")!=null) gauge = request.getParameter("gaugeHead");
// if(request.getParameter("cellValue")!=null) cell = request.getParameter("cellValue");
// showParams(request);
// System.out.println("======"+gauge+"=======");
// System.out.println("===="+cell+"==========");
// // sheetRow=2;
// deleteFile(new File(Path+"/uploadfile/download/DownloadExcel/"),60*30);//删除30分钟前的文件
// // deleteFile(new File(Path+"/uploadfile/download/DownloadExcel/result201601060941462.xlsx"),0);
// return export(sheetName, sheetRow, gaugeHead, cellValue);
// }
/*
* 删除删除本文件
* @fileName 文件名字
* */
// public JsonObject Delete(){
// if(request.getParameter("fileName")!=null){
// fileName=request.getParameter("fileName");
// deleteFile(new File(Path+"/uploadfile/download/DownloadExcel/"+fileName+".xls"),1);
// params.put("fileName",fileName);
// return new JsonObject(new Header(0, "success"), params);
// }else{
// params.put("message","文件已经删除");
// return new JsonObject(new Header(0, "false"), params);
// }
// }
/*
* 测试直接调用方法
*
* */
public void exportExcel() throws Exception{
exportExcel(null,null);
}
/*
* 直接调用方法
*
* */
// public JsonObject exportExcel(String [] gaugeHead,List cellValue) throws Exception{
// List list = new ArrayList();
// // List list = new ArrayList();
// // String [] str ={"8986061509000064555","已经初始化该数据,并且更新"};
// // String [] str2 ={"8986061509000064558","已经初始化该数据,并且更新"};
// // list.add(str);
// // list.add(str2);
// // ToList(list);
// return export(sheetName, sheetRow, gaugeHead, ToList(cellValue));
// }
/*
* 转为二数组
* */
private String[][] ToList( List list){
int listLength = list.size();
Object[] listArray = list.toArray();
String[][] arrList = new String[listLength][];
for(int i=0;i<listArray.length;i++){
arrList[i]=(String[]) listArray[i];
}
return arrList;
}
/*
* 用于导出xlsx 文件
* @sheetName 页的名字
* @sheetRow 多少行一个页
* @gaugeHead 表头
* @cellValue 内容
* */
private String export(String sheetName,int sheetRow,String [] gaugeHead,String [][] cellValue) throws IOException {
fileName="result"+createTime()+createRandom();
//结果存放文件以及地址
resultFile = new File(Path+"/uploadfile/download/DownloadExcel/"+fileName+".xls");
// 创建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
//工作簿的样式
CellStyle cellType= CellStyle(wb);
if(sheetRow>0) {
int len = cellValue.length;//数据的行数
int yuShu = len%sheetRow;//取余数
int ye = (yuShu==0)?(int)Math.ceil(len/sheetRow):((int)Math.ceil(len/sheetRow)+1);
// System.out.println(ye);
for(int i=0;i<ye;i++){
String name= (i==0)? sheetName : sheetName+ String.valueOf(i);//页的名字
wb.createSheet(name);//创建页
gaugeHead(wb.getSheet(name), cellType, gaugeHead);//表头
CellValue(wb.getSheet(name), cellType,setPage(i,cellValue,sheetRow,ye,yuShu));//设置内容
}
}else {
// 创建页
HSSFSheet sheet = wb.createSheet(sheetName);
gaugeHead(sheet, cellType, gaugeHead);//表头
CellValue(sheet, cellType, cellValue);//设置内容
}
FileOutputStream os = new FileOutputStream(resultFile);
//保存工作簿
wb.write(os);
os.flush();
os.close();
// params.put("fileName",fileName);
// return new JsonObject(new Header(0, "success"), params);
return fileName;
}
/*
* 处理分页的数据
* @page 页码
* @arr 存进来的数据
* @sheetRow 多少行一页
* @ye 共多少页
* @yuShu 最后一页多少个
* */
private String[][] setPage(int page,String[][] arr,int sheetRow,int ye,int yuShu){
// System.out.println("page:"+page+",arr:"+arr[0][0]+",sheetRow:"+sheetRow)
int startNum =page*sheetRow;//开始数据
int endNum =(page+1)*sheetRow;//结束数据
int l = 0;
if(page==(ye-1)){//最后一页
if(yuShu!=0){//不是整除
l = yuShu;
endNum = page*sheetRow+yuShu;
}else{
l = (page+1)*sheetRow-page*sheetRow;
}
}else{
l = (page+1)*sheetRow-page*sheetRow;
}
// System.out.println("长度:"+l+"endNum:"+endNum);
String[][] newArr = new String[l][];
int index = 0;
for(int i=startNum;i<endNum;i++){
int len = arr[i].length;
String[] arrRow = new String[len];
for(int j=0;j<len;j++){
arrRow[j]=arr[i][j];
}
newArr[index]=arrRow;
index++;
}
return newArr;
}
/*
* 删除文件
* @file 文件
* @time 删前时间 单位s
* */
private void deleteFile(File file,int time) {
// System.out.println(file.isFile());
time = (time==0)?30:time;
try {
if (!file.isFile()) {//不是文件
// System.out.println(file.listFiles());
File[] tempList =file.listFiles();
for (int i = 0; i < tempList.length; i++) {
if (tempList[i].isFile()) {
if (canDeleteFile(tempList[i],time)) {
delete(tempList[i],"文件");
}
}
if (tempList[i].isDirectory()) {
if (canDeleteFile(tempList[i],time)) {
delete(tempList[i],"文件夹");
}
}
}
}else{
delete(file,"文件");
}
} catch (Exception e) {
System.out.println("删除文件失败!"+ e);
}
}
/*
* 文件删除动作
* */
private void delete(File file,String name){
if (file.delete()) {
System.out.println(name+":" + file.getName() + "删除成功!");
} else {
System.out.println(name+":" + file.getName() + "删除失败!此文件可能正在被使用");
}
}
/*
* 计算文件时间
* @file 文件
* @time 删前时间 单位s
* */
private boolean canDeleteFile(File file,int time) {
Date date = new Date();
long lastUpTime = file.lastModified() / 1000;
long secondsOfOneMinute = time;
//可删除文件的时间
long canDelTime = date.getTime() / 1000 - secondsOfOneMinute;
if(lastUpTime <= canDelTime) {
return true;
}
return false;
}
/*
* 创建时间
* 时分秒
* */
private String createTime(){
SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
return format.format(new Date());
}
private int createRandom(){
return (int)(Math.random()*10);
}
/*
* 创建工作簿的样式
* @wb 工作簿
* */
private CellStyle CellStyle(Workbook wb){
CellStyle cellType = null;
cellType = wb.createCellStyle();
cellType.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellType.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellType.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellType.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellType.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直方向对其方式
cellType.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
cellType.setFillForegroundColor(HSSFColor.WHITE.index);
cellType.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return cellType;
}
/*
* 设置导出数据
* @sheet 工作簿的页
* @cellType 工作簿的样式
* @cellValue 表头数组
* */
private void CellValue(HSSFSheet sheet,CellStyle cellType,String[][] cellValue){
HSSFRow row = null;
HSSFCell cell = null;
for (int i=0;i<cellValue.length;i++){
row = sheet.createRow(i+1); // 第二行
int len = cellValue[i].length;
for(int j=0;j<len;j++){
//列
cell = row.createCell(j);
// 单元格样式
cell.setCellStyle(cellType);
// 单元格的值
cell.setCellValue(cellValue[i][j]);
}
}
}
/*
* 设置导出的表头
* @sheet 工作簿的页
* @cellType 工作簿的样式
* @gaugeHead 表头数组
* */
private void gaugeHead(HSSFSheet sheet,CellStyle cellType,String[] gaugeHead){
HSSFRow row = null;
HSSFCell cell = null;
row = sheet.createRow(0); // 第一行
for (int i=0;i<gaugeHead.length;i++){
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i,gaugeHead[i].getBytes().length*2*256);
// 列
cell = row.createCell(i);
// 单元格样式
cell.setCellStyle(cellType);
// 单元格的值
cell.setCellValue(gaugeHead[i]);
}
}
/*
* 参数打印调试
* */
private void showParams(RequestWrapper request) {
Map map = new HashMap();
Enumeration paramNames = request.getParameterNames();
while (paramNames.hasMoreElements()) {
String paramName = (String) paramNames.nextElement();
String[] paramValues = request.getParameterValues(paramName);
if (paramValues.length == 1) {
String paramValue = paramValues[0];
if (paramValue.length() != 0) {
map.put(paramName, paramValue);
}
}
}
Set<Map.Entry<String, String>> set = map.entrySet();
System.out.println("------------------------------");
for (Map.Entry entry : set) {
System.out.println(entry.getKey() + ":" + entry.getValue());
}
System.out.println("------------------------------");
}
}