ExcelUtil——基本工具类
public class ExcelUtil {
?
?private static final Log log = LogFactory.getLog(ExcelUtil.class);//日志
?
?/**
? * 創建Excel對象
? * @param excelBean
? * @return
? */
?public static HSSFWorkbook creatExcel(ExcelBean excelBean){
??HSSFWorkbook wb = new HSSFWorkbook(); //創建Excel工作薄
??//在Excel工作薄中建一工作表，默認為缺省值
//??HSSFSheet sheet = wb.createSheet();
??????? HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
??????? //設置工作表各列寬度
?????? // setColumnWidth(sheet, excelBean.getWidth());
??????? //設置字體，樣式
??????? //HSSFFont font = setFont(wb, "宋體");
??????? //HSSFCellStyle style = setCellStyle(wb, font);
??????? //設置第一行
??????? List<String> titleList = excelBean.getTitleList();
??????? setRowValue(sheet, titleList, 0); //在索引0的位置創建行（最頂端的行）
??????? //設置數據行
??????? List dataList = excelBean.getDataList();
??????? if(dataList != null){
??????? ?for(int i=0; i<dataList.size(); i++){??????? ??
??????? ??List rowList = (List) dataList.get(i);
??????? ??setRowValue(sheet, rowList, i+1); //在索引1的位置創建行（第2行）??????? ??
??????? ?}
??????? }
??????? return wb;
?}
?
?/**
? * 設置行值
? * @param sheet
? * @param List
? * @return
? */
?public static HSSFSheet setRowValue(HSSFSheet sheet, List<String> list,int rowNo){
??if(list != null){
???//在索引rowNo的位置創建行
???HSSFRow rowTitle = sheet.createRow(rowNo);
???for(int i= 0 ; i<list.size();i++){
????String title = list.get(i);
????//從索引0的位置開始創建單元格（左上端）
????HSSFCell cell = rowTitle.createCell((short)i);
????cell.setCellValue(title); //設置單元格內容
???}
??}
??return sheet;
?}
?
?/**
? * 下載Excel
? * @param wb
? * @param filename MemberList.xls
? * @param response
? * @return
? */
?public static boolean downloadExcel(HSSFWorkbook wb, String filename, HttpServletResponse response){
??boolean flag = true;??
??//設置輸入流
??OutputStream output = null;
??try {
//???設置響應類型
???response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("gbk"),"ISO-8859-1"));
???response.setContentType("application/x-msdownload");
???
???output = response.getOutputStream();???
???wb.write(output);//輸出到網頁中
???output.flush();
???log.info("下載Excel：" + filename + ", 成功！");
??} catch (Exception e) {
???flag = false;
???log.info("下載Excel：" + filename + ", 失敗！");
???e.printStackTrace();
??} finally{
???try {
????output.close();
???} catch (IOException e) {
????e.printStackTrace();
???}
??}
??return flag;??
?}
?
?/**
? * 下載Excel
? * add by zhangde 2011-3-17
? * @param wb
? * @param filename MemberList.xls
? * @param response
? * @return
? */
?public static boolean downloadExcelEncode(String encoding, HSSFWorkbook wb, String filename, HttpServletResponse response){
??boolean flag = true;??
??//設置輸入流
??OutputStream output = null;
??try {
//???設置響應類型,用utf-8,可能会出现高位（前面）被截。gbk在简体操作系统中不会。
//???response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes(encoding),"ISO-8859-1"));
???response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(filename, encoding));
???System.out.println("filename=" + filename);
???System.out.println("URLEncoderfilename=" + URLEncoder.encode(filename, encoding));
???response.setContentType("application/x-msdownload");
???
???output = response.getOutputStream();???
???wb.write(output);//輸出到網頁中
???output.flush();
???log.info("下載Excel：" + filename + ", 成功！");
??} catch (Exception e) {
???flag = false;
???log.info("下載Excel：" + filename + ", 失敗！");
???e.printStackTrace();
??} finally{
???try {
????output.close();
???} catch (IOException e) {
????e.printStackTrace();
???}
??}
??return flag;??
?}
?
?
?/**
? * 下載Excel
? * @param wb
? * @param filename MemberList.xls
? * @param response
? * @param encoding 編碼參數 例如 gbk, utf-8
? * @return
? */
?public static boolean downloadExcel(String encoding, HSSFWorkbook wb, String filename, HttpServletResponse response){
??boolean flag = true;??
??//設置輸入流
??OutputStream output = null;
??String name = "";
??try {
//???設置響應類型
???name = FileUtil.toEncodeString(filename, encoding);?// 文件名編碼
???response.setHeader("Content-disposition", "attachment;filename=" + name);
???response.setContentType("application/x-msdownload");
???output = response.getOutputStream();???
???wb.write(output);//輸出到網頁中
???output.flush();
???log.info("下載Excel：" + filename + ", 成功！");
??} catch (Exception e) {
???flag = false;
???log.info("下載Excel：" + filename + ", 失敗！");
???e.printStackTrace();
??} finally{
???try {
????output.close();
???} catch (IOException e) {
????e.printStackTrace();
???}
??}
??return flag;??
?}
?
?/**
? * 導入文件（excel）
? * @param path?? 路徑+檔案名
? * @param line?? 從第幾欄開始
? * @param rowLen 到第幾列結束
? * @return list
? * @throws Exception
? */
?public static List<Object> importData(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{?
??List<Object> tmpList = new ArrayList<Object>();?
??POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
??HSSFWorkbook wb = new HSSFWorkbook(pfs);
??HSSFSheet sheet = wb.getSheetAt(0);
??HSSFRow row = null;
??try{??
???int totalRow = sheet.getLastRowNum(); //excel資料行數
???if(totalRow>0){ //判斷excel是否為空
????for(int i = atRow ; i <=totalRow; i++){
?????? ?List<Object> rowList = new ArrayList<Object>();?
?????? ?row = sheet.getRow(i);
?????? ?if(row!=null){ //判斷行是否為空
?????? ??for(int j = line; j < rowLen; j++){?
???????HSSFCell cell = row.getCell((short) j);
???????if(cell == null){ //判斷列是否為空
????????rowList.add("");
???????}else{
????????int ctype = cell.getCellType();
????????switch(ctype){
?????????// 處理string部份
?????????case HSSFCell.CELL_TYPE_BLANK:
??????????rowList.add("");
?????????break;
?????????
?????????case HSSFCell.CELL_TYPE_STRING:
??????????rowList.add(cell.getRichStringCellValue().getString().trim());
?????????break;
?????????
?????????//處理number部份
?????????case HSSFCell.CELL_TYPE_NUMERIC:
??????????
??????????if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
???????????System.out.print( cell.getDateCellValue());
???????????//須轉date格式
???????????rowList.add(cell.getDateCellValue());
???????????? }else{
???????????? ?
???????????double tmpdouble = cell.getNumericCellValue();
???????????if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) {
????????????
????????????//int
????????????rowList.add(Integer.toString((int)tmpdouble));
???????????}else{
????????????
????????????//double
????????????rowList.add(Double.toString(cell.getNumericCellValue()));
???????????}
???????????? }
?????????break;
?????????
?????????//處理boolean部份
?????????case HSSFCell.CELL_TYPE_BOOLEAN:?
??????????rowList.add(cell.getBooleanCellValue());
?????????break;
????????}
???????}?
??????? ?}
??????? ?tmpList.add(rowList);
?????? ?}
?????? }
???}
??}catch(Exception e){
???e.printStackTrace();
???throw e;
??}
???? return tmpList;
?}
?/**
? * 導入文件（excel）包含空行
? * @param path?? 路徑+檔案名
? * @param line?? 從第幾欄開始
? * @param rowLen 到第幾列結束
? * @return list
? * @throws Exception
? */
?public static List<Object> importData1(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{?
??List<Object> tmpList = new ArrayList<Object>();?
??POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
??HSSFWorkbook wb = new HSSFWorkbook(pfs);
??HSSFSheet sheet = wb.getSheetAt(0);
??HSSFRow row = null;
??try{??
???int totalRow = sheet.getLastRowNum(); //excel資料行數
???if(totalRow>0){ //判斷excel是否為空
????for(int i = atRow ; i <=totalRow; i++){
?????? ?List<Object> rowList = new ArrayList<Object>();?
?????? ?row = sheet.getRow(i);
?????? ?if(row!=null){ //判斷行是否為空
?????? ??for(int j = line; j < rowLen; j++){?
???????HSSFCell cell = row.getCell((short) j);
???????if(cell == null){ //判斷列是否為空
????????rowList.add("");
???????}else{
????????int ctype = cell.getCellType();
????????switch(ctype){
?????????// 處理string部份
?????????case HSSFCell.CELL_TYPE_BLANK:
??????????rowList.add("");
?????????break;
?????????
?????????case HSSFCell.CELL_TYPE_STRING:
??????????rowList.add(cell.getRichStringCellValue().getString().trim());
?????????break;
?????????
?????????//處理number部份
?????????case HSSFCell.CELL_TYPE_NUMERIC:
??????????
??????????if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
???????????System.out.print( cell.getDateCellValue());
???????????//須轉date格式
???????????rowList.add(cell.getDateCellValue());
???????????? }else{
???????????? ?
???????????double tmpdouble = cell.getNumericCellValue();
???????????if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) {
????????????
????????????//int
????????????rowList.add(Integer.toString((int)tmpdouble));
???????????}else{
????????????
????????????//double
????????????rowList.add(Double.toString(cell.getNumericCellValue()));
???????????}
???????????? }
?????????break;
?????????
?????????//處理boolean部份
?????????case HSSFCell.CELL_TYPE_BOOLEAN:?
??????????rowList.add(cell.getBooleanCellValue());
?????????break;
????????}
???????}?
??????? ?}
?????? ?}else{
?????? ??for(int k = line; k < rowLen; k++){?
?????? ???rowList.add("");
?????? ??}
?????? ?}
?????? ?tmpList.add(rowList);
?????? }
???}
??}catch(Exception e){
???e.printStackTrace();
???throw e;
??}
???? return tmpList;
?}
?
}
