Java integrated pageoffice export to Excel

1, Requirements introduction
At present, B/S mode has become the mainstream of application development, but in the process of developing enterprise office system, there are often customers' requirements like this: export the data in the system database to excel, and open the report directly with excel when users view the report. Or: users are used to printing in Excel, which requires that data must be imported into excel file. So in our actual development, we need to realize the application of exporting excel. At present, there are three commonly used technologies to export excel from Java found on the Internet: POI, JXL, and PageOffice. (CSV technology will not be discussed, but only generates comma separated text files). Let me analyze these three solutions.

2, Scheme analysis
POI is a subproject of apache. Its goal is to process ole 2 objects. It provides a set of Java API s for manipulating Windows documents. HSSF interface is used to operate excel format files. It deals with MS Excel objects. It is not like the text file that we generate with CSV, which has no format and can be converted by Excel. Instead, it simulates the operation of Excel objects. You can control some attributes, such as sheet,cell and so on. HSSF is an abbreviation of the Horrible SpreadSheet Format, which translates into "annoying spreadsheet format". The name of HSSF is not serious, but with HSSF, you can use java code to modify the contents of Excel files. HSSF provides two kinds of APIs for read operations: usermodel and eventusermodel, namely "user model" and "event user model". The interface is abstract and hard to understand.  

JXL is an open source project, through which you can also modify the data content in Excel files. Because of its small size and easy to use, JXL has gradually replaced the position of POI excel and become the first choice for more java developers to generate Excel files. Features of JXL: support Excel 2000 standard format, support font, number, date format operation, support cell shading and coloring, smaller and more memory saving than poi. These functions can roughly meet the general needs of users.

PageOffice encapsulates the Office VBA interface as an excel simplified object model called by Java. It refers to a group of Excel access objects completely implemented by Java, which is convenient for the server-side java code to call directly. This set of object model is not simply imitating the VBA object interface of Excel, but a set of practical and simple object library which is abstracted from the common programming code of developers and combined with the characteristics of database data access. It simplifies the reading and writing operation of the original complex excel, and solves the online opening problem of Excel files. Users can view and edit e directly in the web page Xcel, no need to upload and download.

The space is limited. In order to save your reading time, I will only discuss the relatively good PageOffice scheme.

With PageOffice, Excel files can be generated in simple call mode or in advanced call mode. Simple calling method refers to setting all table styles in excel template file first, and only writing code to fill in data for excel template when developing. This method has the least amount of code, and the effect is not bad, which is also the calling method recommended by PageOffice. Advanced calling method can generate beautiful table (even single) in a blank excel file Meta lattice assignment formula), but the workload of programming is much.

3, Simple call mode
1. Make excel template:

  

2. Code:
   

    PageOfficeCtrl poCtrl1 = new PageOfficeCtrl(request);
    poCtrl1.setServerPage(request.getContextPath()+"/poserver.zz"); 
    //Define the Workbook object
    Workbook workBook = new Workbook();
    //Define the sheet object, Sheet1 is the name of the open Excel form
    Sheet sheet = workBook.openSheet("Sheet1");
    //Defining Table objects
    Table table = sheet.openTable("B4:F13");
    for(int i=0; i < 50; i++)
    { 
        table.getDataFields().get(0).setValue("product " + i);
        table.getDataFields().get(1).setValue("100");
        table.getDataFields().get(2).setValue(String.valueOf(100+i));
        table.nextRow();
    }
    table.close();
        
    poCtrl1.setWriter(workBook);
    //Open Excel file
    poCtrl1.webOpen("doc/test.xls", OpenModeType.xlsNormalEdit, "Zhang San");

2. Effect of Excel generation:

  

4, Advanced call mode

1. Code to Excel

 Workbook wb = new Workbook();
    // Setting background
    Table backGroundTable = wb.openSheet("Sheet1").openTable("A1:P200");
    backGroundTable.getBorder().setLineColor(Color.white);

    // Set title
    wb.openSheet("Sheet1").openTable("A1:H2").merge();
    wb.openSheet("Sheet1").openTable("A1:H2").setRowHeight(30);
    Cell A1 = wb.openSheet("Sheet1").openCell("A1");
    A1.setHorizontalAlignment(XlHAlign.xlHAlignCenter);
    A1.setVerticalAlignment(XlVAlign.xlVAlignCenter);
    A1.setForeColor(new Color(0, 128, 128));
    A1.setValue("Travel expense budget");
    
    //Set font
    wb.openSheet("Sheet1").openTable("A1:A1").getFont().setBold(true);
    wb.openSheet("Sheet1").openTable("A1:A1").getFont().setSize(25);
    
    // Picture header    
    Border C4Border = wb.openSheet("Sheet1").openTable("C4:C4").getBorder();
    C4Border.setWeight(XlBorderWeight.xlThick);
    C4Border.setLineColor(Color.yellow);
    
    Table titleTable = wb.openSheet("Sheet1").openTable("B4:H5");
    titleTable.getBorder().setBorderType(XlBorderType.xlAllEdges);
    titleTable.getBorder().setWeight(XlBorderWeight.xlThick);
    titleTable.getBorder().setLineColor(new Color(0, 128, 128));
    
    // Painting the form
    Table bodyTable = wb.openSheet("Sheet1").openTable("B6:H15");
    bodyTable.getBorder().setLineColor(Color.gray);
    bodyTable.getBorder().setWeight(XlBorderWeight.xlHairline);

    Border B7Border = wb.openSheet("Sheet1").openTable("B7:B7").getBorder();
    B7Border.setLineColor(Color.white);

    Border B9Border = wb.openSheet("Sheet1").openTable("B9:B9").getBorder();
    B9Border.setBorderType(XlBorderType.xlBottomEdge);
    B9Border.setLineColor(Color.white);

    Border C6C15BorderLeft = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();
    C6C15BorderLeft.setLineColor(Color.white);
    C6C15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);
    
    Border C6C15BorderRight = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();
    C6C15BorderRight.setLineColor(Color.yellow);
    C6C15BorderRight.setLineStyle(XlBorderLineStyle.xlDot);
    C6C15BorderRight.setBorderType(XlBorderType.xlRightEdge);

    Border E6E15Border = wb.openSheet("Sheet1").openTable("E6:E15").getBorder();
    E6E15Border.setLineStyle(XlBorderLineStyle.xlDot);
    E6E15Border.setBorderType(XlBorderType.xlAllEdges);
    E6E15Border.setLineColor(Color.yellow);

    Border G6G15BorderRight = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();
    G6G15BorderRight.setBorderType(XlBorderType.xlRightEdge);
    G6G15BorderRight.setLineColor(Color.white);

    Border G6G15BorderLeft = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();
    G6G15BorderLeft.setLineStyle(XlBorderLineStyle.xlDot);
    G6G15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);
    G6G15BorderLeft.setLineColor(Color.yellow);

    Table bodyTable2 = wb.openSheet("Sheet1").openTable("B6:H15");
    bodyTable2.getBorder().setWeight(XlBorderWeight.xlThick);
    bodyTable2.getBorder().setLineColor(new Color(0, 128, 128));
    bodyTable2.getBorder().setBorderType(XlBorderType.xlAllEdges);

    // Draw tail
    Border H16H17Border = wb.openSheet("Sheet1").openTable("H16:H17").getBorder();
    H16H17Border.setLineColor(new Color(204, 255, 204));

    Border E16G17Border = wb.openSheet("Sheet1").openTable("E16:G17").getBorder();
    E16G17Border.setLineColor(new Color(0, 128, 128));

    Table footTable = wb.openSheet("Sheet1").openTable("B16:H17");
    footTable.getBorder().setWeight(XlBorderWeight.xlThick);
    footTable.getBorder().setLineColor(new Color(0, 128, 128));
    footTable.getBorder().setBorderType(XlBorderType.xlAllEdges);

    // Set row height and column width
    wb.openSheet("Sheet1").openTable("A1:A1").setColumnWidth(1);
    wb.openSheet("Sheet1").openTable("B1:B1").setColumnWidth(20);
    wb.openSheet("Sheet1").openTable("C1:C1").setColumnWidth(15);
    wb.openSheet("Sheet1").openTable("D1:D1").setColumnWidth(10);
    wb.openSheet("Sheet1").openTable("E1:E1").setColumnWidth(8);
    wb.openSheet("Sheet1").openTable("F1:F1").setColumnWidth(3);
    wb.openSheet("Sheet1").openTable("G1:G1").setColumnWidth(12);
    wb.openSheet("Sheet1").openTable("H1:H1").setColumnWidth(20);

    wb.openSheet("Sheet1").openTable("A16:A16").setRowHeight(20);
    wb.openSheet("Sheet1").openTable("A17:A17").setRowHeight(20);

    // Set font size to 10 in the table
    for (int i = 0; i < 12; i++) {//excel table line number
        for (int j = 0; j < 7; j++) {//excel table column number
            wb.openSheet("Sheet1").openCellRC(4 + i, 2 + j).getFont().setSize(10);
        }
    }

    // Fill cell background color
    for (int i = 0; i < 10; i++) {
        wb.openSheet("Sheet1").openCell("H" + (6 + i)).setBackColor(new Color(255, 255, 153));
    }

    wb.openSheet("Sheet1").openCell("E16").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("F16").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("G16").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("E17").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("F17").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("G17").setBackColor(new Color(0, 128, 128));
    wb.openSheet("Sheet1").openCell("H16").setBackColor(new Color(204, 255, 204));
    wb.openSheet("Sheet1").openCell("H17").setBackColor(new Color(204, 255, 204));

    //Fill cell text and formula
    Cell B4 = wb.openSheet("Sheet1").openCell("B4");
    B4.getFont().setBold(true);
    B4.setValue("Travel expense budget");
    Cell H5 = wb.openSheet("Sheet1").openCell("H5");
    H5.getFont().setBold(true);
    H5.setValue("Total");
    H5.setHorizontalAlignment(XlHAlign.xlHAlignCenter);
    Cell B6 = wb.openSheet("Sheet1").openCell("B6");
    B6.getFont().setBold(true);
    B6.setValue("Air fare");
    Cell B9 = wb.openSheet("Sheet1").openCell("B9");
    B9.getFont().setBold(true);
    B9.setValue("Hotel");
    Cell B11 = wb.openSheet("Sheet1").openCell("B11");
    B11.getFont().setBold(true);
    B11.setValue("Restaurant");
    Cell B12 = wb.openSheet("Sheet1").openCell("B12");
    B12.getFont().setBold(true);
    B12.setValue("Transportation cost");
    Cell B13 = wb.openSheet("Sheet1").openCell("B13");
    B13.getFont().setBold(true);
    B13.setValue("Recreation & Entertainment");
    Cell B14 = wb.openSheet("Sheet1").openCell("B14");
    B14.getFont().setBold(true);
    B14.setValue("gift");
    Cell B15 = wb.openSheet("Sheet1").openCell("B15");
    B15.getFont().setBold(true);
    B15.getFont().setSize(10);
    B15.setValue("Other expenses");

    wb.openSheet("Sheet1").openCell("C6").setValue("Ticket price (to)");
    wb.openSheet("Sheet1").openCell("C7").setValue("Ticket price (return)");
    wb.openSheet("Sheet1").openCell("C8").setValue("Other");
    wb.openSheet("Sheet1").openCell("C9").setValue("Cost per night");
    wb.openSheet("Sheet1").openCell("C10").setValue("Other");
    wb.openSheet("Sheet1").openCell("C11").setValue("Daily cost");
    wb.openSheet("Sheet1").openCell("C12").setValue("Daily cost");
    wb.openSheet("Sheet1").openCell("C13").setValue("Total");
    wb.openSheet("Sheet1").openCell("C14").setValue("Total");
    wb.openSheet("Sheet1").openCell("C15").setValue("Total");

    wb.openSheet("Sheet1").openCell("G6").setValue("  Zhang");
    wb.openSheet("Sheet1").openCell("G7").setValue("  Zhang");
    wb.openSheet("Sheet1").openCell("G9").setValue("  night");
    wb.openSheet("Sheet1").openCell("G10").setValue("  night");
    wb.openSheet("Sheet1").openCell("G11").setValue("  day");
    wb.openSheet("Sheet1").openCell("G12").setValue("  day");

    wb.openSheet("Sheet1").openCell("H6").setFormula("=D6*F6");
    wb.openSheet("Sheet1").openCell("H7").setFormula("=D7*F7");
    wb.openSheet("Sheet1").openCell("H8").setFormula("=D8*F8");
    wb.openSheet("Sheet1").openCell("H9").setFormula("=D9*F9");
    wb.openSheet("Sheet1").openCell("H10").setFormula("=D10*F10");
    wb.openSheet("Sheet1").openCell("H11").setFormula("=D11*F11");
    wb.openSheet("Sheet1").openCell("H12").setFormula("=D12*F12");
    wb.openSheet("Sheet1").openCell("H13").setFormula("=D13*F13");
    wb.openSheet("Sheet1").openCell("H14").setFormula("=D14*F14");
    wb.openSheet("Sheet1").openCell("H15").setFormula("=D15*F15");

    for (int i = 0; i < 10; i++) {
        //Set data to display in currency
        wb.openSheet("Sheet1").openCell("D" + (6 + i)).setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
        wb.openSheet("Sheet1").openCell("H" + (6 + i)).setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    }

    Cell E16 = wb.openSheet("Sheet1").openCell("E16");
    E16.getFont().setBold(true);
    E16.getFont().setSize(11);
    E16.setForeColor(Color.white);
    E16.setValue("Total travel expenses");
    E16.setVerticalAlignment(XlVAlign.xlVAlignCenter);
    Cell E17 = wb.openSheet("Sheet1").openCell("E17");
    E17.getFont().setBold(true);
    E17.getFont().setSize(11);
    E17.setForeColor(Color.white);
    E17.setFormula("=IF(C4>H16,\"Below budget\",\"Excess budget\")");
    E17.setVerticalAlignment(XlVAlign.xlVAlignCenter);
    Cell H16 = wb.openSheet("Sheet1").openCell("H16");
    H16.setVerticalAlignment(XlVAlign.xlVAlignCenter);
    H16.setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    H16.getFont().setName("Arial");
    H16.getFont().setSize(11);
    H16.getFont().setBold(true);
    H16.setFormula("=SUM(H6:H15)");
    Cell H17 = wb.openSheet("Sheet1").openCell("H17");
    H17.setVerticalAlignment(XlVAlign.xlVAlignCenter);
    H17.setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    H17.getFont().setName("Arial");
    H17.getFont().setSize(11);
    H17.getFont().setBold(true);
    H17.setFormula("=(C4-H16)");

    // Fill data
    Cell C4 = wb.openSheet("Sheet1").openCell("C4");
    C4.setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    C4.setValue("2500");
    Cell D6 = wb.openSheet("Sheet1").openCell("D6");
    D6.setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    D6.setValue("1200");
    wb.openSheet("Sheet1").openCell("F6").getFont().setSize(10);
    wb.openSheet("Sheet1").openCell("F6").setValue("1");
    Cell D7 = wb.openSheet("Sheet1").openCell("D7");
    D7.setNumberFormatLocal("¥#,##0.00;¥-#,##0.00");
    D7.setValue("875");
    wb.openSheet("Sheet1").openCell("F7").setValue("1");

    //Open file
    PageOfficeCtrl poCtrl1 = new PageOfficeCtrl(request);
    poCtrl1.setWriter(wb);
    poCtrl1.setServerPage(request.getContextPath()+"/poserver.zz"); 
    poCtrl1.webOpen("doc/test.xls", OpenModeType.xlsNormalEdit, "");

2. Effect of Excel generation:

5, Source download
1. Visit: http://www.zhuzhengsoft.com/dowm/, Download PageOffice for Java;

2. Extract the PageOffice development package, copy the Samples4 folder to the Webapps directory of Tomcat, and visit: http://localhost:8080/Samples4/index.html;

3. View examples: 1. 23. Assign a value to an area in Excel and add lines automatically; 3. 8. Dynamically generate excel file by full programming
 

Tags: Programming Excel Java Database

Posted on Wed, 15 Jan 2020 05:32:24 -0800 by josborne