jxl操作Excel

Java高级群：224651178

//jxl操作Excel工具类 包含导入导出代码 package com.wzpmt.excel.util; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import javax.servlet.http.HttpServletResponse; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.read.biff.BiffException; import jxl.write.WritableCellFormat; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; //import org.apache.struts2.ServletActionContext; public class ExcelUtils { /** * 获取表头的格式 * @return */ public static WritableCellFormat getHeadCellFormat(){ WritableCellFormat wcfHead=new WritableCellFormat(); try { wcfHead.setBorder(Border.ALL, BorderLineStyle.THIN); wcfHead.setAlignment(Alignment.CENTRE); wcfHead.setBackground(Colour.YELLOW2); } catch (Exception e) { e.printStackTrace(); } return wcfHead; } /** * 获取红色的单元格式 * @return */ public static WritableCellFormat getRedCellFormat(){ WritableCellFormat wcRed = new WritableCellFormat(); try { wcRed.setBorder(Border.ALL, BorderLineStyle.THIN); wcRed.setAlignment(Alignment.CENTRE); wcRed.setBackground(Colour.RED); } catch (Exception e) { e.printStackTrace(); } return wcRed; } /** * 获取WritableWorkbook对象，让调用者写excel * @return */ public static WritableWorkbook getWritableWorkbook(){ ByteArrayOutputStream os =new ByteArrayOutputStream(); WritableWorkbook workBook =null; try { workBook = Workbook.createWorkbook(os); } catch (IOException e) { e.printStackTrace(); } return workBook; } /** * 写入WritableWorkBook后关闭它，并将写入的excel以流的形式返回 * @param workBook * @param os * @return */ public static ByteArrayInputStream writeCloseWorkbook(WritableWorkbook workBook,ByteArrayOutputStream os){ try { workBook.write(); } catch (IOException e1) { e1.printStackTrace(); }finally{ if(workBook!=null){ try { workBook.close(); } catch (IOException e) { e.printStackTrace(); } } } return new ByteArrayInputStream(os.toByteArray()); } /** * 写excel的表头 * @param headNames * @param sheet * @return */ public static WritableSheet writeExcelHead(String[] headNames,WritableSheet sheet){ try { jxl.write.WritableCell wcell=null; for(int i=0;i<headNames.length;i++){ wcell = new jxl.write.Label(i,0,headNames[i],getHeadCellFormat()); sheet.addCell(wcell); } } catch (Exception e) { e.printStackTrace(); } return sheet; } /** * 将已经写好的excel以流的形式写出 * @param excelName * @param is */ public static void ExcelExport(String excelName,InputStream is,HttpServletResponse response){ String expFileName=excelName; try { expFileName = new String(expFileName.getBytes("gbk"),"iso8859-1"); //HttpServletResponse response= ServletActionContext.getResponse(); response.setContentType("application/ms-excel; charset=UTF-8"); response.setHeader("Content-Disposition","attachment;filename="+ expFileName+".xls"); InputStream inputStream=is; OutputStream os = null; response.setContentLength(inputStream.available()); os = response.getOutputStream(); int iBytesRead = 0; byte[] buffer = new byte[8192]; BufferedInputStream bis = new BufferedInputStream(inputStream, 8192); BufferedOutputStream bos = new BufferedOutputStream(os, 8192); while ((iBytesRead = bis.read(buffer, 0, 8192)) != -1) { bos.write(buffer, 0, iBytesRead); } bos.flush(); bos.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 根据文件获取Workbook对象，用于导入 * @param file * @return * @throws IOException * @throws BiffException * @throws Exception */ public static Workbook getWorkBookForImport(File file) throws Exception{ InputStream is = new FileInputStream(file); Workbook workBook = Workbook.getWorkbook(is); return workBook; } public static void closeWorkBookForImport(Workbook workBook){ if(workBook!=null){ try { workBook.close(); } catch (Exception e) { e.printStackTrace(); } } } }

Java高级群：224651178