Package com.netlang.excel

Class ExcelDocument

java.lang.Object
com.netlang.excel.ExcelDocument
public class ExcelDocument extends Object
A simple implementation of an excel document that is based on POI to create, read and write XLS and XLSX documents.
This class do not work very well with big XLSX documents. If you are having performance issues please us XLSDocumentReader instead.

Example of modifying an existing Excel document:

ExcelDocument xls1 = new ExcelDocument(new File("C:\\Temp\\test.xls"),true);
ExcelSheet firstSheet=xls1.getFirstSheet();
firstSheet.set(1,1,"Hello");
xls1.close();

  • Field Details

    • COMMAND_ADD

      public static int COMMAND_ADD

    • COMMAND_UPDATE

      public static int COMMAND_UPDATE

    • COMMAND_DELETE

      public static int COMMAND_DELETE

    • SET_KEYS

      public static String SET_KEYS

    • SET_DATA

      public static String SET_DATA

  • Constructor Details

    • ExcelDocument

      public ExcelDocument() throws com.net4z.APIException
      Initializes a newly created ExcelDocument
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(String resourceid, String path, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from the specified resource id
      Parameters:
      resourceid -
      path -
      createIfNeeded -
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(String path, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from local path.
      Parameters:
      path -
      createIfNeeded -
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(File file, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from local path.
      Parameters:
      file -
      createIfNeeded -
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(NetlangFile file, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from local path.
      Parameters:
      file -
      createIfNeeded -
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(com.netlang.session.NetlangSession session, NetlangFile file, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from local path.
      Parameters:
      session -
      file -
      createIfNeeded -
      Throws:
      com.net4z.APIException

    • ExcelDocument

      public ExcelDocument(com.netlang.session.NetlangSession session, String resourceid, String path, boolean createIfNeeded) throws com.net4z.APIException
      Creates or read Excel Document from local path.
      Parameters:
      session -
      resourceid -
      path -
      createIfNeeded -
      Throws:
      com.net4z.APIException

  • Method Details

    • open

      public void open() throws com.net4z.APIException
      Open the file for read or write. Always called from the constructor.
      Throws:
      com.net4z.APIException

    • close

      public void close() throws com.net4z.APIException
      Close this document
      Throws:
      com.net4z.APIException

    • getWorksheets

      public org.apache.poi.ss.usermodel.Sheet[] getWorksheets() throws Exception
      List all work sheets.
      Returns:
      Throws:
      Exception

    • getWorksheetNames

      public List<String> getWorksheetNames() throws Exception
      List all worksheets name
      Returns:
      Throws:
      Exception

    • getWorkbook

      public org.apache.poi.ss.usermodel.Workbook getWorkbook()
      Get the workbook
      Returns:

    • getFirstSheet

      public ExcelSheet getFirstSheet() throws Exception
      Get the first sheet in this excel document.
      Returns:
      Throws:
      Exception

    • getSheet

      public ExcelSheet getSheet(String name) throws Exception
      Get sheet by name.
      Parameters:
      name -
      Returns:
      Throws:
      Exception

    • getOrCreateSheet

      public ExcelSheet getOrCreateSheet(String name) throws Exception
      Get the sheet if exist otherwise create.
      Parameters:
      name -
      Returns:
      Throws:
      Exception

    • removeSheet

      public void removeSheet(String sheetName) throws Exception
      Remove sheet by name.
      Parameters:
      sheetName -
      Throws:
      Exception

    • createSheet

      public ExcelSheet createSheet(String name) throws Exception
      Create a new sheet.
      Parameters:
      name -
      Returns:
      Throws:
      Exception

    • save

      public byte[] save() throws Exception
      Save this document to byte.
      Returns:
      Throws:
      Exception

    • save

      public void save(String path) throws com.net4z.APIException
      Save this document to the local file system
      Parameters:
      path -
      Throws:
      com.net4z.APIException

    • save

      public void save(File file) throws com.net4z.APIException
      Save this document to the local file system
      Parameters:
      file -
      Throws:
      com.net4z.APIException

    • save

      public void save(NetlangFile file) throws com.net4z.APIException
      Save this document to the local file system
      Throws:
      com.net4z.APIException

    • save

      public void save(String resourceid, String filepath) throws com.net4z.APIException
      Save this document to the resource id
      Parameters:
      resourceid -
      filepath -
      Throws:
      com.net4z.APIException

    • save

      public void save(com.netlang.session.NetlangSession session, String resourceid, String filepath) throws com.net4z.APIException
      Save this document to the resource id
      Parameters:
      session -
      resourceid -
      filepath -
      Throws:
      com.net4z.APIException

    • saveWorkBook

      public void saveWorkBook() throws Exception
      Save the workbook
      Throws:
      Exception

    • saveWorkBook

      public void saveWorkBook(String filename) throws Exception
      Throws:
      Exception

    • insertRows

      public void insertRows(String sheetName, int insertAtRow, int numRowsToInsert) throws Exception
      Insert new empty rows.
      Parameters:
      sheetName -
      insertAtRow -
      numRowsToInsert -
      Throws:
      Exception

    • removeRows

      public void removeRows(String sheetName, int startRow, int endRow) throws Exception
      Remove row from the sheet.
      Parameters:
      sheetName -
      startRow -
      endRow -
      Throws:
      Exception

    • removeAllRows

      public void removeAllRows(String sheetName) throws Exception
      Remove all rows from the sheet.
      Parameters:
      sheetName -
      Throws:
      Exception

    • get

      public String get(String sheetName, int row, String column) throws Exception
      Returns the cell data.
      Parameters:
      sheetName -
      row -
      column -
      Returns:
      Throws:
      Exception

    • search

      public int search(String sheetName, int startRow, String column, String data, boolean caseSensitive) throws Exception
      Search for data.
      Parameters:
      sheetName -
      startRow -
      column -
      data -
      caseSensitive -
      Returns:
      Throws:
      Exception

    • getLastRow

      public int getLastRow(String sheetName) throws Exception
      Get the last row number from the sheet.
      Parameters:
      sheetName -
      Returns:
      Throws:
      Exception

    • getLastEmptyRow

      public int getLastEmptyRow(String sheetName) throws Exception
      Returns the last empty row from the sheet.
      Parameters:
      sheetName -
      Returns:
      Throws:
      Exception

    • set

      public void set(String sheetName, int row, String column, String value) throws Exception
      Set cell data.
      Parameters:
      sheetName -
      row -
      column -
      value -
      Throws:
      Exception

    • set

      public void set(String sheetName, int row, String column, double value) throws Exception
      Set cell data.
      Parameters:
      sheetName -
      row -
      column -
      value -
      Throws:
      Exception

    • set

      public void set(String sheetName, int row, String column, Date value) throws Exception
      Set cell data.
      Parameters:
      sheetName -
      row -
      column -
      value -
      Throws:
      Exception

    • query

      public com.intellus.sql.Query query(String sql) throws SQLException
      Set cell data.
      Parameters:
      sql -
      Returns:
      Throws:
      SQLException