package com.huawei.cn.upload;
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.OutputStream;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Set;
import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
import jxl.Cell;import jxl.CellType;import jxl.Sheet;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.write.DateFormat;import jxl.write.DateTime;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;
import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import org.apache.struts.actions.DispatchAction;import org.apache.struts.upload.FormFile;
public class UploadAction extends DispatchAction {
public ActionForward upload(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { UploadForm uploadForm = (UploadForm)form; FormFile file = uploadForm.getFile(); try { Workbook workbook = null; workbook = Workbook.getWorkbook(file.getInputStream()); Sheet sheet = workbook.getSheet(0); //String[][] datas = new String[sheet.getRows()][];二维数组 // 解析文件得到六张表的数据 for (int i = 0; i < sheet.getRows(); i++) { //datas[i][0] = sheet.getCell(0, i).getContents(); //datas[i][0] = sheet.getCell(1, i).getContents(); System.out.println(sheet.getCell(0, i).getContents()); System.out.println(sheet.getCell(1, i).getContents()); System.out.println(sheet.getCell(2, i).getContents()); System.out.println(sheet.getCell(3, i).getContents()); Cell cell = sheet.getCell(0, i); CellType d = cell.getType(); } file.destroy(); } catch (Exception e) { e.printStackTrace(); } return null; }
public ActionForward download(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { Map<String, String> headerMap = new LinkedHashMap<String, String>();; headerMap.put("CD", "客户运营商"); headerMap.put("PD", "漫游伙伴"); headerMap.put("DA", "漫游伙伴数据清算中心"); headerMap.put("NMCOUNT", "上发文件数"); String path = request.getRealPath("/jsp/" + "/" + "feipo.xls"); Map<String, String> format = new LinkedHashMap<String, String>(); List<Map<String, Object>> contentList = new ArrayList<Map<String,Object>>(); Map<String, Object> contentMap = null; for(int i = 0;i< 10;i++){ contentMap = new LinkedHashMap<String, Object>(); contentMap.put("CD", "a"+i); contentMap.put("PD", "b"+i); contentMap.put("DA", "c"+i); contentMap.put("NMCOUNT", "d"+i); contentList.add(contentMap); } doSave(path, headerMap, contentList,format, response); return null; }
public boolean doSave(String fileName, Map<String, String> headerMap, List<Map<String, Object>> contentMap, Map<String, String> dtFormat, HttpServletResponse response) {
File file = null; try { String path = File.separator; String fname = fileName; if (null != fileName && fileName.lastIndexOf(File.separator) != -1) { path = fileName.substring(0, fileName .lastIndexOf(File.separator) + 1); fname = fileName.substring( fileName.lastIndexOf(File.separator) + 1, fileName .length()); } String newFileName = path + "export_" + System.currentTimeMillis()+ ".xls"; file = new File(newFileName); OutputStream out = new FileOutputStream(file); // 输出流 对象 WritableWorkbook wookbook = Workbook.createWorkbook(out); // 创建一个Excel文档 WritableSheet sheet = wookbook.createSheet("Sheet1", 0); // 创建一页
// 定义文档格式 包括头和内容单元格格式 WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat titleFormat = new WritableCellFormat(titleFont); titleFormat.setAlignment(Alignment.CENTRE); // 标题居中
WritableFont contFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat contFormat = new WritableCellFormat(contFont); // contFormat.setAlignment(Alignment.RIGHT); // 内容居右 // 不设置格式，使之默认字符串左对齐，数字和日期右对齐
DateFormat df = null; // 针对不同的日期格式 WritableCellFormat dateFormat = null;
// 用于保存日期格式对象 Map<String, WritableCellFormat> dfMap = new LinkedHashMap<String, WritableCellFormat>(); // 等于空，说明没有日期字段，不需要设置格式 if (null != dtFormat) { Set<String> dfKey = dtFormat.keySet(); Iterator<String> dfIt = dfKey.iterator();
for (int i = 0; i < dfKey.size(); i++) { String dfField = dfIt.next();
df = new DateFormat(dtFormat.get(dfField));// 用于日期的 dateFormat = new WritableCellFormat(contFont, df);
dfMap.put(dfField, dateFormat); // 将日期格式对象保存 } }
int row = 0; // 控制换行
// 展示表头 Label label = null; jxl.write.Number num = null; jxl.write.DateTime date = null;
Set<String> heads = headerMap.keySet(); Iterator<String> it = heads.iterator();
for (int i = 0; i < heads.size(); i++) { String title = headerMap.get(it.next());
label = new Label(i, 0, title, titleFormat); // 设置标题 sheet.addCell(label); sheet.setColumnView(i, 30); // 设置列宽 }
row++; // 新增一行
// 展示数据 for (int j = 0; j < contentMap.size(); j++) { Map<String, Object> temp = contentMap.get(j); it = heads.iterator();
for (int k = 0; k < heads.size(); k++) { String fieldName = it.next(); Object cont = temp.get(fieldName);
if (null == cont || cont.toString().length() == 0) { continue; }
if (cont instanceof Date) {
dateFormat = dfMap.get(fieldName);
date = new DateTime(k, row, (Date) cont, dateFormat); sheet.addCell(date); // 添加内容 } else if (cont instanceof Integer) {
num = new jxl.write.Number(k, row, Double .parseDouble(cont.toString())); sheet.addCell(num); // 添加内容 } else if (cont instanceof Long) { num = new jxl.write.Number(k, row, Double .parseDouble(cont.toString())); sheet.addCell(num); // 添加内容 } else if (cont instanceof Float) { num = new jxl.write.Number(k, row, Double .parseDouble(cont.toString())); sheet.addCell(num); // 添加内容 } else if (cont instanceof Double) { num = new jxl.write.Number(k, row, Double .parseDouble(cont.toString())); sheet.addCell(num); // 添加内容 } else { label = new Label(k, row, cont.toString(), contFormat); sheet.addCell(label); // 添加内容 } }
row++; // 行自增 }
wookbook.write(); // 写数据 wookbook.close(); // 关闭流 out.close();
// 文件创建后，将文件以流的形式输出 this.outPutExcel(fname, file, response);
return true;
} catch (IOException e) { System.out.println("另存文件时出现文件流读写异常"); } catch (RowsExceededException e) { System.out.println("要生成的文件数据行过多,另存失败"); } catch (WriteException e) { System.out.println("将数据写入文件时出现错误,另存失败"); } catch (Exception e) { System.out.println("生成另存文件时出现错误"); } finally { if (null != file) { boolean isDelete = file.delete(); // 删除文件 System.out.println(isDelete + ""); } } return false; } /** * @param fi * 输出文件 * @param response * 响应对象 将文件以流的形式输出 * @throws IOException */ private void outPutExcel(String fileName, File fi, HttpServletResponse response) throws IOException {
BufferedInputStream bis = null; // 缓存输入流 BufferedOutputStream bos = null; // 缓存输出流
response.reset(); // 先清空一下response
response.setCharacterEncoding("ISO-8859-1"); // 设置编码 response.setContentType(fi.getPath()); // 设置类型 response.addHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1")); // 设置文件名
response.setContentLength(Integer .parseInt((String.valueOf(fi.length())))); // 设置文件长度
bis = new BufferedInputStream(new FileInputStream(fi)); bos = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[4096]; // 每次读取流的字节数 int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); }
bos.flush(); // 清理一下缓存 bos.close(); response.getOutputStream().flush(); response.getOutputStream().close(); bis.close(); // 关闭流对象
} /** * @param fileName * 文件名 * @return 返回文件名后缀 用于设置文件的返回类型 */ public static String getContentType(String fileName) { String fileNameTemp = fileName.toLowerCase(); // 先转为小写 String ret = ""; if (fileNameTemp.endsWith("txt")) { ret = "text/plain"; } else if (fileNameTemp.endsWith("gif")) { ret = "image/gif"; } else if (fileNameTemp.endsWith("jpg")) { ret = "image/jpeg"; } else if (fileNameTemp.endsWith("jpeg")) { ret = "image/jpeg"; } else if (fileNameTemp.endsWith("jpe")) { ret = "image/jpeg"; } else if (fileNameTemp.endsWith("zip")) { ret = "application/zip"; } else if (fileNameTemp.endsWith("rar")) { ret = "application/rar"; } else if (fileNameTemp.endsWith("doc")) { ret = "application/msword"; } else if (fileNameTemp.endsWith("ppt")) { ret = "application/vnd.ms-powerpoint"; } else if (fileNameTemp.endsWith("xls")) { ret = "application/vnd.ms-excel"; } else if (fileNameTemp.endsWith("html")) { ret = "text/html"; } else if (fileNameTemp.endsWith("htm")) { ret = "text/html"; } else if (fileNameTemp.endsWith("tif")) { ret = "image/tiff"; } else if (fileNameTemp.endsWith("tiff")) { ret = "image/tiff"; } else if (fileNameTemp.endsWith("pdf")) { ret = "application/pdf"; } else if (fileNameTemp.endsWith("mp3")) { ret = "application/mp3"; } return ret; } public ActionForward goUpload(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { return mapping.findForward("upload"); } public ActionForward goDownload(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { return mapping.findForward("download"); }}
