Java POI操作Excle工具类

  1 import java.io.File;
  2 import java.io.FileInputStream;
  3 import java.io.FileOutputStream;
  4 import java.io.IOException;
  5 import java.io.InputStream;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import jxl.Cell;
 10 import jxl.Workbook;
 11 
 12 import org.apache.log4j.Logger;
 13 import org.apache.poi.hssf.usermodel.HSSFCell;
 14 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 15 import org.apache.poi.hssf.usermodel.HSSFFont;
 16 import org.apache.poi.hssf.usermodel.HSSFRow;
 17 import org.apache.poi.hssf.usermodel.HSSFSheet;
 18 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 19 import org.apache.poi.hssf.util.HSSFColor;
 20 import org.dom4j.Document;
 21 import org.dom4j.DocumentException;
 22 
 23 import com.chinadigitalvideo.hibernate.Provider;
 24 import com.chinadigitalvideo.service.ProviderMgr;
 25 import com.chinadigitalvideo.utils.bean.App_Data;
 26 import com.chinadigitalvideo.xbase.GUID;
 27 
 28 public class POIExcelHelper {
 29     public static Logger logger = Logger.getLogger(POIExcelHelper.class);
 30 
 31     /**
 32      * 设置表头样式 
 33      * @param workbook
 34      * @return
 35      */
 36     public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
 37         HSSFFont font = workbook.createFont();
 38         font.setColor(HSSFColor.BLUE.index);
 39         font.setFontHeight((short) 200);
 40         font.setFontName("楷体_GB2312");
 41         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 42 
 43         HSSFCellStyle style = workbook.createCellStyle();
 44         style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
 45         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 46         style.setFont(font);
 47         style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
 48         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 49         return style;
 50     }
 51     
 52     /**
 53      * 设置表格特别数据样式
 54      * @param workbook
 55      * @return
 56      */
 57     public static HSSFCellStyle getDataStyle2(HSSFWorkbook workbook) {
 58         HSSFFont font = workbook.createFont();
 59         font.setColor(HSSFColor.BLACK.index);
 60         font.setFontHeight((short) 200);
 61         font.setFontName("楷体_GB2312");
 62 
 63         HSSFCellStyle style = workbook.createCellStyle();
 64         style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
 65         style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
 66         style.setFont(font);
 67         style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
 68         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 69         return style;
 70     }
 71 
 72     /**
 73      * 创建单元格内容
 74      * @param row
 75      * @param id
 76      * @param value
 77      * @param style
 78      */
 79     @SuppressWarnings("deprecation")
 80     public static void createCell(HSSFRow row, int id, String value, HSSFCellStyle style) {
 81         HSSFCell cell = row.createCell((short) id);
 82         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 83         cell.setCellValue(value);
 84         if (style != null) {
 85             cell.setCellStyle(style);
 86         }
 87     }
 88 
 89     /**
 90      * 创建报表文件
 91      * @param workbook
 92      * @param dir
 93      * @param filename
 94      * @throws IOException
 95      */
 96     public static void createFile(HSSFWorkbook workbook, String dir, String filename)
 97             throws IOException {
 98         dir = dir == null ? "" : dir.trim();
 99         if( !"".equals(dir) ){
100             if( !dir.endsWith(File.separator) ){
101                 dir += File.separator ;
102             }
103         }
104         logger.debug("out put dir: " + dir);
105         File outdir = new File(dir);
106         if (!outdir.exists()) {
107             outdir.mkdirs();
108         }
109         FileOutputStream fOut = new FileOutputStream(dir + filename);
110         workbook.write(fOut);
111         fOut.flush();
112         fOut.close();
113         logger.info(dir + filename + "已经生成!");
114     }
115     
116     /**
117      * 读取Excel中所有的列
118      * @param filename
119      * @return
120      * @throws IOException
121      */
122     private static List<Cell[]> jxlGetExcelColumns(String filename) throws IOException {
123         InputStream is = null;
124         jxl.Workbook rwb = null;
125         List<Cell[]> list = new ArrayList<Cell[]>();
126         try {
127             is = new FileInputStream(filename);
128             rwb = Workbook.getWorkbook(is);
129             // Sheet[] sheets = rwb.getSheets();
130             // int sheetLen = sheets.length;
131             jxl.Sheet rs = rwb.getSheet(0); // 读取第一个工作表的数据
132 
133             //getRows() 获取总共多少列...getColumn(n)获取第n列...
134             for(int i=0; i<rs.getColumns(); i++ ){
135                 list.add(rs.getColumn(i));
136             }
137         } catch (Exception ex) {
138             ex.printStackTrace();
139         } finally {
140             rwb.close();
141             is.close();
142         }
143         return list;
144     }
145     
146     /**
147      * 读取Excel中所有的行
148      * @param filename
149      * @return
150      */
151     private static List<Cell[]> jxlGetExcelRows(String filename) {
152         InputStream is = null;
153         jxl.Workbook rwb = null;
154         List<Cell[]> list = new ArrayList<Cell[]>();
155         try {
156             is = new FileInputStream(filename);
157             rwb = Workbook.getWorkbook(is);
158             // Sheet[] sheets = rwb.getSheets();
159             // int sheetLen = sheets.length;
160             jxl.Sheet rs = rwb.getSheet(0); // 默认先读取第一个工作表的数据
161             
162             //getRows() 获取总共多少行...getRow(n)获取第n行...
163             for(int i=0; i<rs.getRows(); i++ ){
164                 list.add(rs.getRow(i));
165             }
166         } catch (Exception ex) {
167             ex.printStackTrace();
168         } finally {
169             rwb.close();
170             try {
171                 is.close();
172             } catch (IOException e) {
173                 e.printStackTrace();
174             }
175         }
176         return list;
177     }
178 }