Poi of Apache

In the current project, a function is to import data from the Excel table to sqlserver2000, and the development language is Java. It has been found online for a long time, finally, we can see that poi, an open-source project of Apache, has such a function implementation. If anyone uses C # To implement this function, I hope to share it with you.

Why poi?
A very frustrating fact is that it is difficult to interact with Microsoft's proprietary file format. However, Apache Software FoundationPOorOBfuscationIMplementation,
That is, poor fuzzy implementation. The goal of poi is to provide a set of Java APIs to enable
Microsoft Office files in compound document format are easy to operate. Some poi APIs are only the most commonly used
Microsoft Office files are developed in Word and Excel, while other APIs are used in general Ole 2 compound.
Document and attribute files. The Apache poi website provides a wealth of additional information about the poi project and its APIs. Its URL is http://jakarta.apache.org/poi/index.html.

Reading and rewriting workbooks

    POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream("workbook.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell((short)3);
    if (cell == null)
        cell = row.createCell((short)3);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

Insert data to database

for(int row = 0; row < spreadsheetModel.getRowCount(); row++) {
   sqlRowInsertQuery = "INSERT INTO " + sqlFacade.getUser() 
      + "." + spreadsheetName + sqlColumnNames + " VALUES (";
   for(int col = 0; col < spreadsheetModel.getColumnCount()-1; col++) {
if (col > 0) {
sqlRowInsertQuery += ", ";
}
sqlRowInsertQuery += "'" + 
   ((HSSFCell)(spreadsheetModel.getRow(row).get(col))).getStringCellValue() + "'";
   }
   sqlRowInsertQuery += " ); ";

You have imported data from an Excel table to a database.
Source code download