用到了jxl.jar和poi.jar

一些基本的操作Excel的操作方法:

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 filename119      * @return120      * @throws IOException121      */122     private static List
     
       jxlGetExcelColumns(String filename) throws IOException {123         InputStream is = null;124         jxl.Workbook rwb = null;125         List
      
        list = new ArrayList
       
        ();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
        
          jxlGetExcelRows(String filename) {152         InputStream is = null;153         jxl.Workbook rwb = null;154         List
         
           list = new ArrayList
          
           ();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