excelDownload
来源:互联网 发布:梵高怎么得的精神 知乎 编辑:程序博客网 时间:2024/06/04 18:42
<action name="downloadExcel" class="action.office.DownloadExcelAction"><result name="success" type="stream"> <param name="contentType">application/vnd.ms-excel</param><!-- 默认就是 inputStream,它将会指示 StreamResult 通过 inputName 属性值的 getter 方法, 比如这里就是 getExcelInputStream() 来获取下载文件的内容,意味着你的 Action 要有这个方法 --> <param name="inputName">downloadFile</param> <param name="contentDisposition">attachment;fileName="${fileName}"</param> <param name="bufferSize">4096</param> </result> </action>
jxl.jar
<filter-mapping><filter-name>struts2</filter-name><url-pattern>/office/task/downloadExcel.action</url-pattern></filter-mapping>
package action.office;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.io.UnsupportedEncodingException;import java.util.ArrayList;import java.util.List;import java.util.Map;import javax.servlet.ServletContext;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import org.apache.struts2.ServletActionContext;import org.apache.struts2.interceptor.ServletRequestAware;import org.apache.struts2.interceptor.ServletResponseAware;import org.apache.struts2.interceptor.SessionAware;import org.apache.struts2.util.ServletContextAware;import org.fsap.util.DowloadExcelUtil;import org.fsap.util.ViewSet;import com.opensymphony.xwork2.Action;import com.opensymphony.xwork2.ActionContext;import com.opensymphony.xwork2.ActionSupport;public class DownloadExcelAction extends ActionSupport implements ServletResponseAware,ServletRequestAware, ServletContextAware,SessionAware {private static final long serialVersionUID = -2786097100189963452L;protected HttpServletRequest request; protected HttpServletResponse response; protected ServletContext application;//protected Map<String, Object> session = ActionContext.getContext().getSession();protected Map<String, Object> session;//= (Map<String, Object>) ServletActionContext.getRequest().getSession(); ;//文件名 private String fileName ="text2.xls";//获取服务器Root路径。 application/vnd.ms-excelprivate String filePath;private InputStream downloadFile;private DowloadExcelUtil jxl = new DowloadExcelUtil();public DownloadExcelAction() {}public String execute(){//System.out.println("ok--session------"+session.size());ViewSet view = (ViewSet) session.get("view");//表格台头titlesString[] titles =new String[view.getItems()];//第一行view.getLine(0);for(int l=0;l<view.getItems();l++){titles[l] = view.get(l)==" "?"":view.get(l);} //表格内容listList<String[]> list = new ArrayList<String[]>();; String[] s1 = null ; //从view第二行开始。for(int j=1;j<view.getSize();j++){view.getLine(j);s1 = new String[view.getItems()];for(int l=0;l<view.getItems();l++){s1[l] = view.get(l).trim().equals(" ")?"":view.get(l);//System.out.println(l);}list.add(s1);}//表格行创建在io中。 ByteArrayOutputStream out = new ByteArrayOutputStream(); jxl.createExcelFile(out, fileName, titles,list); //strurts2 输出流。 downloadFile = new ByteArrayInputStream(out.toByteArray());// jxl.readDataFromExcel(new File(filePath), 0);// jxl.openExcel("D://Program Files//Kingsoft//WPS Office Professional//office6//et.EXE",filePath); return Action.SUCCESS;} // 下载文件 public InputStream getDownloadFile() { return downloadFile; } public void setFileName(String fileName) { try {// 解决中文文件名问题 this.fileName = new String(fileName.getBytes("ISO-8859-1"), "GBK"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } } public String getFileName() { String name = ""; try {// 解决下载文件中文文件名问题 name = new String(fileName.getBytes("GBK"), "ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return name; } // public Map<String, Object> getSession() {//return session;//}////public void setSession(Map<String, Object> session) {//this.session = session;//}public void setDownloadFile(InputStream downloadFile) {this.downloadFile = downloadFile;}public String getFilePath() {return filePath;}public void setFilePath(String filePath) {this.filePath = filePath;}@Override public void setServletResponse(HttpServletResponse response) { this.response = response; } @Override public void setServletRequest(HttpServletRequest request) { this.request = request; } @Override public void setServletContext(ServletContext application) { this.application = application; }@Overridepublic void setSession(Map arg0) {this.session = arg0;}}
package org.fsap.util;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.Date;import java.util.List;import jxl.Cell;import jxl.CellType;import jxl.CellView;import jxl.Sheet;import jxl.Workbook;import jxl.WorkbookSettings;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.Colour;import jxl.format.VerticalAlignment;import jxl.write.Formula;import jxl.write.Label;import jxl.write.NumberFormat;import jxl.write.WritableCellFeatures;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;public class DowloadExcelUtil {/** * * * @param os 输出流 * @param sheetName 工作表名 * @param dataTitles 台头 * @param dataContent 表内容 */public void createExcelFile(OutputStream os,String sheetName, String[] dataTitles,List<String[]> dataContent) { WritableWorkbook workbook; try { workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); // 添加第一个工作表 initialSheetSetting(sheet,dataTitles,dataContent); Label label; for (int i = 0; i < dataTitles.length; i++) { // Label(列号,行号,内容,风格) label = new Label(i, 0, dataTitles[i], getTitleCellFormat()); sheet.addCell(label); } System.out.println("dataContent.size="+dataContent.size()); int rowNo=0 ;//行号 for(int i=0;i<dataContent.size();i++){ insertRowData(sheet, i+1, dataContent.get(i), getDataCellFormat(CellType.STRING_FORMULA)); rowNo = i+1; }// // 插入一行// insertRowData(sheet, 1, new String[] { "200201001", "张三222222222222222", "100",// "60", "100", "260" },// getDataCellFormat(CellType.STRING_FORMULA));// // 一个一个插入行// label = new Label(0, 2, "200201002",// getDataCellFormat(CellType.STRING_FORMULA));// sheet.addCell(label);// label = new Label(1, 2, "李四2222222222",// getDataCellFormat(CellType.STRING_FORMULA));// sheet.addCell(label);// insertOneCellData(sheet, 2, 2, 70.5,// getDataCellFormat(CellType.NUMBER));// insertOneCellData(sheet, 3, 2, 90.523,// getDataCellFormat(CellType.NUMBER));// insertOneCellData(sheet, 4, 2, 60.5,// getDataCellFormat(CellType.NUMBER));// insertFormula(sheet, 5, 2, "C3+D3+E3",// getDataCellFormat(CellType.NUMBER_FORMULA)); // 插入日期 mergeCellsAndInsertData(sheet, 0, rowNo+1, dataContent.get(0).length-1, rowNo+1, "导出时间:"+new Date(), getDataCellFormat(CellType.DATE)); workbook.write(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } } /** * @param args */ /** * 生成Excel文件 * * @param path * 文件路径 * @param sheetName * 工作表名称 * @param dataTitles * 数据标题 */ public void createExcelFile(String path, String sheetName, String[] dataTitles,List<String[]> dataContent) { WritableWorkbook workbook; try { OutputStream os = new FileOutputStream(path); workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); // 添加第一个工作表 initialSheetSetting(sheet,dataTitles,dataContent); Label label; for (int i = 0; i < dataTitles.length; i++) { // Label(列号,行号,内容,风格) label = new Label(i, 0, dataTitles[i], getTitleCellFormat()); sheet.addCell(label); } System.out.println("dataContent.size="+dataContent.size()); int rowNo=0 ;//行号 for(int i=0;i<dataContent.size();i++){ insertRowData(sheet, i+1, dataContent.get(i), getDataCellFormat(CellType.STRING_FORMULA)); rowNo = i+1; }// // 插入一行// insertRowData(sheet, 1, new String[] { "200201001", "张三222222222222222", "100",// "60", "100", "260" },// getDataCellFormat(CellType.STRING_FORMULA));// // 一个一个插入行// label = new Label(0, 2, "200201002",// getDataCellFormat(CellType.STRING_FORMULA));// sheet.addCell(label);// label = new Label(1, 2, "李四2222222222",// getDataCellFormat(CellType.STRING_FORMULA));// sheet.addCell(label);// insertOneCellData(sheet, 2, 2, 70.5,// getDataCellFormat(CellType.NUMBER));// insertOneCellData(sheet, 3, 2, 90.523,// getDataCellFormat(CellType.NUMBER));// insertOneCellData(sheet, 4, 2, 60.5,// getDataCellFormat(CellType.NUMBER));// insertFormula(sheet, 5, 2, "C3+D3+E3",// getDataCellFormat(CellType.NUMBER_FORMULA)); // 插入日期 mergeCellsAndInsertData(sheet, 0, rowNo+1, 5, rowNo+1, "导出时间:"+new Date(), getDataCellFormat(CellType.DATE)); workbook.write(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 初始化表格属性 * * @param sheet * @param dataTitles 台头 * @param dataContent 表内容 */ public void initialSheetSetting(WritableSheet sheet,String[] dataTitles,List<String[]> dataContent) { try {// System.out.println(" *********列宽"); // sheet.getSettings().setProtected(true); //设置xls的保护,单元格为只读的 // sheet.getSettings().setDefaultColumnWidth(10); // 设置列的默认宽度 // sheet.setRowView(2,false);//行高自动扩展 // setRowView(int row, int height);--行高 // setColumnView(int col,int width); --列宽 int[] col_width =new int[dataTitles.length]; for(int i=0;i<dataTitles.length;i++){ //设置最大列宽 col_width[i] = dataTitles[i].length()+6; } for(int k = 0;k<dataContent.size();k++){ for(int i=0;i<dataContent.get(k).length;i++){ //设置最大列宽 col_width[i] = dataContent.get(k)[i].length()+6>col_width[i]?dataContent.get(k)[i].length()+6:col_width[i]; } } for(int i=0;i<col_width.length;i++){ sheet.setColumnView(i,col_width[i]);//根据内容自动设置列宽 // System.out.println("列宽"+ col_width[i]); } // sheet.setColumnView(0, 20);// 设置第一列宽度 } catch (Exception e) { e.printStackTrace(); } } /** * 插入公式 * * @param sheet * @param col * @param row * @param formula * @param format */ public void insertFormula(WritableSheet sheet, Integer col, Integer row, String formula, WritableCellFormat format) { try { Formula f = new Formula(col, row, formula, format); sheet.addCell(f); } catch (Exception e) { e.printStackTrace(); } } /** * 插入一行数据 * * @param sheet * 工作表 * @param row * 行号 * @param content * 内容 * @param format * 风格 */ public void insertRowData(WritableSheet sheet, Integer row, String[] dataArr, WritableCellFormat format) { try { Label label; for (int i = 0; i < dataArr.length; i++) { label = new Label(i, row, dataArr[i], format); sheet.addCell(label); } } catch (Exception e) { e.printStackTrace(); } } /** * 插入单元格数据 * * @param sheet * @param col * @param row * @param data */ public void insertOneCellData(WritableSheet sheet, Integer col, Integer row, Object data, WritableCellFormat format) { try { if (data instanceof Double) { jxl.write.Number labelNF = new jxl.write.Number(col, row, (Double) data, format); sheet.addCell(labelNF); } else if (data instanceof Boolean) { jxl.write.Boolean labelB = new jxl.write.Boolean(col, row, (Boolean) data, format); sheet.addCell(labelB); } else if (data instanceof Date) { jxl.write.DateTime labelDT = new jxl.write.DateTime(col, row, (Date) data, format); sheet.addCell(labelDT); setCellComments(labelDT, "这是个创建表的日期说明!"); } else { Label label = new Label(col, row, data.toString(), format); sheet.addCell(label); } } catch (Exception e) { e.printStackTrace(); } } /** * 合并单元格,并插入数据 * * @param sheet * @param col_start * @param row_start * @param col_end * @param row_end * @param data * @param format */ public void mergeCellsAndInsertData(WritableSheet sheet, Integer col_start, Integer row_start, Integer col_end, Integer row_end, Object data, WritableCellFormat format) { try { sheet.mergeCells(col_start, row_start, col_end, row_end);// 左上角到右下角 insertOneCellData(sheet, col_start, row_start, data, format); } catch (Exception e) { e.printStackTrace(); } } /** * 给单元格加注释 * * @param label * @param comments */ public void setCellComments(Object label, String comments) { WritableCellFeatures cellFeatures = new WritableCellFeatures(); cellFeatures.setComment(comments); if (label instanceof jxl.write.Number) { jxl.write.Number num = (jxl.write.Number) label; num.setCellFeatures(cellFeatures); } else if (label instanceof jxl.write.Boolean) { jxl.write.Boolean bool = (jxl.write.Boolean) label; bool.setCellFeatures(cellFeatures); } else if (label instanceof jxl.write.DateTime) { jxl.write.DateTime dt = (jxl.write.DateTime) label; dt.setCellFeatures(cellFeatures); } else { Label _label = (Label) label; _label.setCellFeatures(cellFeatures); } } /** * 读取excel * * @param inputFile * @param inputFileSheetIndex * @throws Exception */ public ArrayList<String> readDataFromExcel(File inputFile, int inputFileSheetIndex) { ArrayList<String> list = new ArrayList<String>(); Workbook book = null; Cell cell = null; WorkbookSettings setting = new WorkbookSettings(); java.util.Locale locale = new java.util.Locale("zh", "CN"); setting.setLocale(locale); setting.setEncoding("ISO-8859-1"); try { book = Workbook.getWorkbook(inputFile, setting); } catch (Exception e) { e.printStackTrace(); } Sheet sheet = book.getSheet(inputFileSheetIndex); for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {// 行 for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {// 列 cell = sheet.getCell(colIndex, rowIndex); // System.out.println(cell.getContents()); list.add(cell.getContents()); } } book.close(); return list; } /** * 得到数据表头格式 * * @return */ public WritableCellFormat getTitleCellFormat() { WritableCellFormat wcf = null; try { // 字体样式 WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false);// 最后一个为是否italic wf.setColour(Colour.RED); wcf = new WritableCellFormat(wf); // 对齐方式 wcf.setAlignment(Alignment.CENTRE); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 边框 wcf.setBorder(Border.ALL, BorderLineStyle.THIN); // 背景色 wcf.setBackground(Colour.GREY_25_PERCENT); } catch (WriteException e) { e.printStackTrace(); } return wcf; } /** * 得到数据格式 * * @return */ public WritableCellFormat getDataCellFormat(CellType type) { WritableCellFormat wcf = null; try { // 字体样式 if (type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {// 数字 NumberFormat nf = new NumberFormat("#.00"); wcf = new WritableCellFormat(nf); } else if (type == CellType.DATE || type == CellType.DATE_FORMULA) {// 日期 jxl.write.DateFormat df = new jxl.write.DateFormat( "yyyy-MM-dd hh:mm:ss"); wcf = new jxl.write.WritableCellFormat(df); } else { WritableFont wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false);// 最后一个为是否italic wcf = new WritableCellFormat(wf); } // 对齐方式 wcf.setAlignment(Alignment.CENTRE); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 边框 wcf.setBorder(Border.LEFT, BorderLineStyle.THIN); wcf.setBorder(Border.BOTTOM, BorderLineStyle.THIN); wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN); // 背景色 wcf.setBackground(Colour.WHITE); wcf.setWrap(true);// 自动换行 } catch (WriteException e) { e.printStackTrace(); } return wcf; } public InputStream getInputExcel(String path){ InputStream is = null;try {is = new FileInputStream(path);//写入到FileInputStream Workbook wb = Workbook.getWorkbook(is); //得到工作薄 Sheet st = wb.getSheet(0);//得到工作薄中的第一个工作表 System.out.println(st.getRows()+"-----------"+st.getColumns()); for(int i = 0;i<st.getRows();i++){ for(int j = 0;j<st.getColumns();j++){ Cell cell=st.getCell(j,i);//得到工作表的第一个单元格,即A1 (行,列) String content=cell.getContents();//getContents()将Cell中的字符转为字符串 System.out.println(content+"content"+i+" "+j); } } wb.close();//关闭工作薄 is.close();//关闭输入流 } catch (Exception e) {e.printStackTrace();}return is; } /** * 打开文件看看 * * @param exePath * @param filePath */ public void openExcel(String exePath, String filePath) { Runtime r = Runtime.getRuntime(); String cmd[] = { exePath, filePath }; try { r.exec(cmd); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { String[] titles = { "学号", "姓名", "语文", "数学", "英语", "总分" }; DowloadExcelUtil jxl = new DowloadExcelUtil(); String filePath = "D:/test2.xls"; List<String[]> list = new ArrayList<String[]>();; String[] s1 = null ; for(int i=0;i<20;i++){ s1 = new String[] { "20020100"+i, "张三2222"+i, "100", "60", "100", "260"}; list.add(s1); } jxl.createExcelFile(filePath, "成绩单", titles,list); jxl.readDataFromExcel(new File(filePath), 0); jxl.openExcel("D://Program Files//Kingsoft//WPS Office Professional//office6//et.EXE",filePath); }}
"../../office/task/downloadExcel.action?fileName="+fileName+"&filePath="+filePath);
- excelDownload
- FRM-92101:forms Server在启动过程中失败
- Redis实战之征服 Redis + Jedis + Spring (二)
- C#中窗体使用Invoke和BeginInvoke详解
- Power Strings(kmp 重复子串)
- boost::checked_delete作用介绍
- excelDownload
- Android项目实战--手机卫士18--读取用户的短信内容以及短信备份
- 黑马程序员------Java的泛型(原理应用、自定义泛型)
- log4j:ERROR Could not instantiate class [org.apache.hadoop.hive.shims.HiveEventCounter].
- PowerDesigner 学习及使用
- Delphi JSON 天气预报
- 线程同步与互斥
- 温度PID的整定过程
- multipart/form-data和application/x-www-form-urlencoded