WorkBook Manipulation(Java)

 
top
Read/Write xls/csv file

load/save workbook from/to xls/csv files.

//load xls file
workBook.read("template.xls");
//load csv file
workBook.CSVSeparator = ';';
workBook.read("template.csv");
//save xls file
workBook.write("result.xls");
//save csv file
workBook.writeCSV("result.csv");

 
top
Read/Write xlsx/xlsm file

load/save workbook from/to xlsx files.

//load xlsx file
workBook.readXLSX("template.xlsx");
//save xlsx file
workBook.writeXLSX("result.xlsx");

 
top
Read/Write xlsb(Excel2007-2016 Binary) file

load/save workbook from/to xlsb files (current support features like cell value/formula/validation/data filter/hyperlink/conditional formatting/picture)

//load xlsb file
workBook.readXLSB(new java.io.FileInputStream("template.xlsb"));
//save xlsb file
workBook.writeXLSB(new java.io.FileOutputStream("result.xlsb"));

 
top
Encrypt/Decrypt Excel97-2003 xls file

encrypt/decrypt xls files.

workBook.read("in.xls", "password");
workBook.write("out.xls", "password");

 
top
Encrypt/Decrypt Excel2007-2016 xlsx/xlsm file

encrypt/decrypt xlsx files.

workBook.readXLSX("in.xlsx", "password");
workBook.writeXLSX("out.xlsx", "password");

 
top
Convert Excel file to PDF file

export the workbook to pdf file

workBook.setPrintHeader("My header");
workBook.setPrintHeader("");
workBook.setPrintFooter("Page &P");
workBook.setPrintPaperSize(11906, 16838);
workBook.setPrintTopMargin(1.01); //1.05
workBook.setPrintBottomMargin(0.99); //1.03
workBook.setPrintHeaderMargin(0);
workBook.setPrintLeftMargin(0);
workBook.setPrintRightMargin(0);
workBook.setPrintHCenter(true);
// workBook.addRowPageBreak(1);
// workBook.addColPageBreak(1);

//export the current select worksheet to pdf file
workBook.exportPDF("out.pdf");

//export the whole workbook to pdf file
//workBook.exportPDF("out.pdf", null, false);

 
top
Export sheet data to image

export partial range data to image file

workBook.setPrintGridLines(true);
//export range A1:K37 to png file
workBook.sheetRangeToImage(0, 0, 37, 10, "out.png"); //only png format support

 
top
Setting Calculation Options

enable/disable auto calculation,calculate manually

//disable auto calculation
workBook.setAutoRecalc(false);
//calculation max iteration
workBook.setIterationMax(256);
//calculation max iteration change value
workBook.setIterationMaxChange(0.01);
//calculte the whole workbook
workBook.recalc();