jxl 对Excel的读写改操作

 

 

package cn.com.jyangzi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * jxl 读取
 * @author Michael sun
 */
public class JxlTest {

    /**
     * 读取 excel 文件
     * @param filePath
     * @throws Exception
     */
    private void readExcel(String filePath) throws Exception {
        InputStream is = null;
        Workbook workbook = null;
        try {
            is = new FileInputStream(filePath);
            workbook = Workbook.getWorkbook(is);
            // sheet row column 下标都是从0开始的
            Sheet sheet = workbook.getSheet(0);

            int column = sheet.getColumns();
            int row = sheet.getRows();
            System.out.println("共有" + row + "行，" + column + "列数据");

            for(int r=0;r<row;r++)
         {
             for(int c=0;c<column;c++)
             {
             
               // A1是字符
                    Cell cell = sheet.getCell(c, r);
                   System.out.print("A1 type:" + cell.getType());
                    //if (cell.getType().equals(CellType.LABEL)) {
                        System.out.print(" " + cell.getContents());
                    //}
             }
             System.out.println("");
            }
           
//            // A1是字符
//            Cell cellA1 = sheet.getCell(0, 0);
//            System.out.println("A1 type:" + cellA1.getType());
//            if (cellA1.getType().equals(CellType.LABEL)) {
//                System.out.println("A1 content:" + cellA1.getContents());
//            }
//
//            // B1是数字
//            Cell cellB1 = sheet.getCell(1, 0);
//            System.out.println("B1 type:" + cellB1.getType());
//            if (cellB1.getType().equals(CellType.NUMBER)) {
//                NumberCell numberCell = (NumberCell) cellB1;
//                double douval = numberCell.getValue();
//                System.out.println("B1 value:" + douval);
//            }
//
//            // C1是日期
//            Cell cellC1 = sheet.getCell(2, 0);
//            System.out.println("C1 type:" + cellC1.getType());
//            if (cellC1.getType().equals(CellType.DATE)) {
//                DateCell dateCell = (DateCell) cellC1;
//                Date date =dateCell.getDate();
//                System.out.println("C1 date:" + date);
//            }

            // 操作完成时，关闭对象，释放占用的内存空间
            workbook.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (is != null) {
                is.close();
            }
        }
    }

     /** 
     * 写入 excel 文件 
     * @param filePath 
     * @throws Exception 
     */ 
    private void writeExcel(String filePath) throws Exception {  
        OutputStream os = null;  
        try {  
  System.out.println("ddd");
            // 构建Workbook对象  
            os = new FileOutputStream(filePath);  
            WritableWorkbook wwb = Workbook.createWorkbook(os);    
            // 构建Excel sheet  
            WritableSheet sheet = wwb.createSheet("test write sheet", 0);  
            // 设置标题格式  
            WritableFont wfTitle = new jxl.write.WritableFont(  
                    WritableFont.ARIAL, 18, WritableFont.BOLD, true);  
            WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);  
            // 设置水平对齐方式  
            wcfTitle.setAlignment(Alignment.CENTRE);  
            // 设置垂直对齐方式  
            wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);  
            // 设置是否自动换行  
            wcfTitle.setWrap(true);  
            // 合并A1->C2  
            sheet.mergeCells(0, 0, 2, 1);  
            Label titleCell = new Label(0, 0, "Title Cell ", wcfTitle);  
            sheet.addCell(titleCell);  
 
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,  
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
                    Colour.BLUE);  
            WritableCellFormat wcf = new WritableCellFormat(wf);  
 
            // A3  
            Label labelCell = new Label(0, 2, "Label Cell ");  
            sheet.addCell(labelCell);  
            // B3  
            Label labelCellFmt = new Label(1, 2,  
                    "Label Cell with WritableCellFormat ", wcf);  
            sheet.addCell(labelCellFmt);  
 
            // A4 添加jxl.write.Number对象  
            jxl.write.Number labelN = new jxl.write.Number(0, 3, 3.1415926);  
            sheet.addCell(labelN);  
            // B4 添加Number对象 jxl.write.NumberFormat  
            NumberFormat nf = new NumberFormat("#.##");  
            WritableCellFormat wcfN = new WritableCellFormat(nf);  
            jxl.write.Number labelNF = new jxl.write.Number(1, 3, 3.1415926,  
                    wcfN);  
            sheet.addCell(labelNF);  
 
            // A5 添加jxl.write.Boolean对象  
            jxl.write.Boolean labelB = new jxl.write.Boolean(0, 4, true);  
            sheet.addCell(labelB);  
 
            // A6 添加 jxl.write.DateTime对象  
            jxl.write.DateTime labelDT =
             new jxl.write.DateTime(0, 5, new Date());  
            sheet.addCell(labelDT);  
            // B6 添加DateTime对象 jxl.write.DateFormat  
            jxl.write.DateFormat df = new jxl.write.DateFormat(  
                    "yyyy-MM-dd HH:mm:ss");  
            WritableCellFormat wcfDF = new WritableCellFormat(df);  
            jxl.write.DateTime labelDTF =
             new jxl.write.DateTime(1, 5, new Date(), wcfDF);  
            sheet.addCell(labelDTF);  
            //先调用write();再调用close();  
            wwb.write();  
            wwb.close();  
            os.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            if (null != os) {  
                os.close();  
            }  
        }  
 
    } 
   
    /**
     * 插入图片
     * @param filePath
     * @throws Exception
     */
    private void writeImg(String filePath) throws Exception {  
      
        OutputStream os = null;  
        try {  
            String imgPath = "K:\\网厅项目文件\\网厅\\需求\\IMG_0537.png";  
            os = new FileOutputStream(filePath);  
            WritableWorkbook wwb = Workbook.createWorkbook(os);  
            WritableSheet ws = wwb.createSheet("write img", 0);  
            File imgFile = new File(imgPath);  
 
            // WritableImage(col, row, width, height, imgFile);  
            WritableImage image = new WritableImage(2, 1, 8, 20, imgFile);  
            ws.addImage(image);  
            wwb.write();  
            wwb.close();  
 
        } catch (Exception e) {  
            System.out.println(e);  
        } finally {  
            if (null != os) {  
                os.close();  
            }  
        }  
    }  
 

    /** 
     * 更新Excel
     * @param filePath 
     */ 
    private void doUpdate(String filePath) {  
 
        try {  
            // 获得原Excel文件  
            Workbook wb = Workbook.getWorkbook(new File(filePath));  
            // 打开一个文件的副本，并且指定数据写回到原文件  
            WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath),wb);  
            // 对第一个工作簿的A1 更新  
            WritableSheet wsheet0 = wwb.getSheet(0);  
            WritableCell wc00 = wsheet0.getWritableCell(0, 0);  
            if (wc00.getType() == CellType.LABEL) {  
                Label label00 = (Label) wc00;  
                label00.setString("updata data");  
            }  
            // 添加一个工作表  
            WritableSheet sheet = wwb.createSheet("新增工作簿", 1);  
            // 写入一些测试数据  
            sheet.addCell(new Label(0, 0, "test data"));  
 
            // 关闭工作薄对象  
            wwb.write();  
            wwb.close();  
            wb.close();  
        } catch (Exception e) {  
            System.out.println(e);  
        }  
    }  

    //----------------------------main()方法-----------------------------
        
    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
     
     JxlTest jxltest = new JxlTest();
     /**
      * 读文件
      */
        String filePath = "K:\\网厅项目文件\\网厅\\需求\\read.xls";
        jxltest.readExcel(filePath);
       
        /**
         * 写文件
         */
        String filePath2 = "K:\\网厅项目文件\\网厅\\需求\\write.xls";  
        jxltest.writeExcel(filePath2);  
        
        /**
         * 插入图片
         */
       
        String filePath3 = "K:\\网厅项目文件\\网厅\\需求\\img.xls";  
        jxltest.writeImg(filePath3);  
       
        /**
         *更新Excel
         */
       
        String filePath4 = "K:\\网厅项目文件\\网厅\\需求\\read.xls";  
        jxltest.doUpdate(filePath4);  
       
       
    }
}