excelwriteread.java

Excel Report是一款基于Excel的报表生成工具

/*
 * Created on 2006-6-1
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package other;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellFeatures;
import jxl.CellType;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.format.Orientation;
import jxl.write.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.VerticalAlignment;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @author juny
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */ 
public class ExcelWriteRead {

    /**
     * 
     */
    public ExcelWriteRead() {
    }
    
    public static void modify(WritableWorkbook w) throws Exception {
        WritableSheet sheet = w.getSheet(0);
        
        WritableCell cell = null;
        CellFormat cf = null;
        Label l = null;
        
        int rows = sheet.getRows();
        
        long begin = System.currentTimeMillis();
        for(int i=0; i<rows; i++){
            Cell[] cell1 = sheet.getRow(i);
            for(int j=0; j<cell1.length; j++){
                String contain = cell1[j].getContents();
                if(contain.equals("[billid]")){
                    WritableCell wc = sheet.getWritableCell(cell1[j].getColumn(),cell1[j].getRow());
                    if (wc.getType() == CellType.LABEL){
                        Label lab = (Label)wc;
                        lab.setString("20060525004196");
                    }
                }
            }
        }
        System.out.println(" time=" + String.valueOf(System.currentTimeMillis() - begin));
        
    }

    public static void main(String[] args) {
        File inputWorkbook = new File("aaa.xls");
        File outputWorkbook = new File("w2.xls");
        System.out.println("begin");
        try{
	        Workbook w1 = Workbook.getWorkbook(inputWorkbook);
	
	        //WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);
	        WritableWorkbook wTemplet = Workbook.createWorkbook(outputWorkbook, w1);
	        WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook);
	        System.out.println(" begin modify");
	        // modify(w2);
	        
	        ExcelWriteRead test1 = new ExcelWriteRead();
	        
	        //test1.write(w2);
	        /*test1.copy(w2, wTemplet);
            w2.write();
            w2.close();
            test1.printExcelInfo(w1);
            */
	        ///*
	        test1.copyCell(wTemplet);
	        wTemplet.write();
	        wTemplet.close();
	        //*/
	        
            System.out.println(" complete");
            
        }catch (Exception e){
            System.out.println(e);
        }
    }
    
    public void copy(WritableWorkbook to, WritableWorkbook from){
        try {
            WritableSheet sheet = to.createSheet("工单配件信息", 0);
            WritableSheet sheetold = from.getSheet(1);
            
            WritableCell cell = null;
            CellFormat cf = null;
            Label l = null;
            
            int rows = sheetold.getRows();
            
            long begin = System.currentTimeMillis();
            int cols = sheetold.getColumns();
            for(int i=0; i<rows; i++){
                System.out.print("row-" + i + "  ");
                Cell[] row = sheetold.getRow(i);
                sheet.insertRow(i);
                for(int j=0; j<row.length; j++){
                    cell = null;
                    WritableCell c = sheetold.getWritableCell(j,i);
                    if(null != c)
                        cell = c.copyTo(j,i );
                    //WritableCell newCell = new WritableCell();
                    if(null != cell){
                        if ((cell.getType() != CellType.EMPTY) ){
                        //System.out.print(cell.getContents());
                            //CellFormat f = c.getCellFormat();
                            //WritableCell newCell = new WritableCell(j,i);
                            //cell.setCellFormat(f);
                            //sheet.addCell(cell);
                            System.out.print( " " + j + ":" +cell.getType());
                        }
                    }
                }
                System.out.println();
            }
            System.out.println(" time=" + String.valueOf(System.currentTimeMillis() - begin));
        }
        catch (Exception ex) {
            System.out.println(ex);
        }
    }
    public void delete(WritableWorkbook to){
        WritableSheet sheetold = to.getSheet(0);
        try{
            int rows = sheetold.getRows();
            int cols = sheetold.getColumns();
            System.out.println(" delete one row!");
            for(int i=0; i< rows;i++){
                sheetold.removeRow(1);
            }
            System.out.println(" delete one row success!");
        }catch(Exception e){
            System.out.println(e);
        }
    }
    
    public void copyCell(WritableWorkbook to){

        WritableSheet sheetold = to.getSheet(0);
        try{
            int rows = sheetold.getRows();
            int cols = sheetold.getColumns();
            //拷贝单元格
            System.out.println("rows=" + rows + " cols=" + cols);
            for(int row=0; row<rows; row++){
                sheetold.insertRow(row+rows);
                for(int col=0; col<cols; col++){
			        WritableCell c2 = sheetold.getWritableCell(col,row).copyTo(col,row+rows);
			        sheetold.addCell(c2);
                }
            }
            for(int row=0; row<rows; row++){
                sheetold.insertRow(row+rows);
                for(int col=0; col<cols; col++){
			        WritableCell c2 = sheetold.getWritableCell(col,row).copyTo(col,row+rows);
			        sheetold.addCell(c2);
                }
            }
            //拷贝图象
            int images = sheetold.getNumberOfImages();
            WritableImage imgnew = null;
            for(int i=0; i<images; i++){
                WritableImage img = sheetold.getImage(i);
                System.out.print("image row=" + img.getRow() + " col=" + img.getColumn());
                System.out.println("  height=" + img.getHeight() + " width=" + img.getWidth());
                imgnew = new WritableImage(img.getColumn(),
                        img.getRow()+rows,
                        img.getWidth(),
                        img.getHeight(),
                        img.getImageData());
                sheetold.addImage(imgnew);
            }
            /*imgnew = sheetold.getImage(0);
            sheetold.insertRow(3);
            Label label = new Label(0,3, " insert row3");
            sheetold.addCell(label);
            sheetold.insertRow(4);
            label = new Label(0,4, " insert row4");
            sheetold.addCell(label);
            sheetold.insertRow(5);
            label = new Label(0,5, " insert row5");
            sheetold.addCell(label);
            sheetold.insertRow(6);
            label = new Label(0,6, " insert row6");
            sheetold.addCell(label);*/
//            WritableImage imgnew1 = new WritableImage(8,
//                    259,
//                    imgnew.getWidth(),
//                    imgnew.getHeight(),
//                    imgnew.getImageData());
//            sheetold.addImage(imgnew1);
        }catch(Exception e){
            System.out.println(e);
        }
    }
    
    public void printExcelInfo(Workbook wb){
        Sheet sheet = wb.getSheet(0);
        Cell cell[] = sheet.getRow(0);
        
        for(int i=0; i<sheet.getColumns(); i++){
            System.out.println("i=" + sheet.getColumnWidth(i));
        }
        System.out.println("row=" + sheet.getRowHeight(0));
        
    }
    int size[] = { 36*110,
            36*206,
            36*83,
            36*86,
            36*149,
            36*68,
            36*114,
            36*81,
            36*81,
            36*113
            };
    public void write(WritableWorkbook wb) throws WriteException{
        WritableSheet sheet = wb.createSheet("配件工单", 0);
       
        WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 9,
                WritableFont.NO_BOLD); //字体
       WritableFont arial10font1 = new WritableFont(WritableFont.ARIAL, 9); //字体
       
       WritableCellFormat wfTitle = new WritableCellFormat(arial10font);
       wfTitle.setBackground(Colour.YELLOW); //背景
       wfTitle.setAlignment(Alignment.CENTRE);
       wfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
       wfTitle.setWrap(true);
       
       CellView cv = new CellView();
       for(int i=0; i<size.length; i++){
           cv.setSize(size[i]);
           sheet.setColumnView(i, cv);
       }
       
       sheet.addCell(new Label(0, 0, "工单编号", wfTitle));
       sheet.addCell(new Label(1, 0, "车型", wfTitle));
       sheet.addCell(new Label(2, 0, "配件销售SAP客户号", wfTitle));
       sheet.addCell(new Label(3, 0, "维修站编号", wfTitle));
       sheet.addCell(new Label(4, 0, "维修站名称", wfTitle));
       sheet.addCell(new Label(5, 0, "所属配件分中心", wfTitle));
       sheet.addCell(new Label(6, 0, "配件代号", wfTitle));
       sheet.addCell(new Label(7, 0, "配件名称", wfTitle));
       sheet.addCell(new Label(8, 0, "所需数量", wfTitle));
       sheet.addCell(new Label(9, 0, "投诉内容", wfTitle));
       sheet.setRowView(0,540);
       
       WritableCellFormat wf1 = new WritableCellFormat(arial10font1);
       wf1.setBackground(Colour.TAN);
       sheet.addCell(new Label(0, 1, "1", wf1));
       sheet.addCell(new Label(1, 1, "1", wf1));
       sheet.addCell(new Label(2, 1, "1", wf1));
       sheet.addCell(new Label(3, 1, "1", wf1));
       sheet.setRowView(1,540);
    }
}