Java operations on Excel tables, javaexcel tables

Java operations on Excel tables, javaexcel tables

Import java. io. File; // introduces a class
Import java. io. IOException;
Import java. util. collections;
Import jxl. Cell;
Import jxl. Sheet;
Import jxl. Workbook;
Import jxl. format. UnderlineStyle;

Import jxl. write. DateFormat;
Import jxl. write. DateTime;
Import jxl. write. Label;
Import jxl. write. NumberFormat;
Import jxl. write. WritableCellFormat;
Import jxl. write. WritableFont;

Import jxl. write. WritableSheet;
Import jxl. write. WritableWorkbook;

Public class TextOperatorExcel {// class used to operate Excel files

/**
* Generate an Excel file
*/
Public static void writeExcel (String fileName ){
WritableWorkbook wwb = null;
Try {
// Create a writeable Workbook object
Wwb = Workbook. createWorkbook (new File (fileName ));
} Catch (IOException e) {// catch a stream exception
E. printStackTrace ();
}
If (wwb! = Null ){
// Create a writable Worksheet
// The createSheet method of the Workbook has two parameters: the first is the worksheet name, and the second is the position of the worksheet in the Workbook.
WritableSheet ws = wwb. createSheet ("sheet1", 0 );
For (int I = 0; I <10; I ++) {// Add cells cyclically
For (int j = 0; j <5; j ++ ){
Label labelC = new Label (j, I, "This is the first" + (I + 1) + "line, the first"
+ (J + 1) + "column ");
Try {
Ws. addCell (labelC); // Add the generated cells to the worksheet.
} Catch (Exception e) {// catch an Exception
E. printStackTrace ();
}

}
}
Try {
Wwb. write (); // write data to the file from the memory
Wwb. close (); // write data to the file from the memory
} Catch (Exception e) {// catch an Exception
E. printStackTrace ();
}
}
System. out. println ("generate an Excel file:" + fileName + "success! ");
}

/**
* Write content
* @ Param fileName
* @ Throws Exception
*/
Public static void writeContentToExcel (String fileName) throws Exception {
File tempFile = new File (fileName );
WritableWorkbook workbook = Workbook. createWorkbook (tempFile );
WritableSheet sheet = workbook. createSheet ("TestCreateExcel", 0 );
// Some temporary variables for writing to excel
Label l = null;
Jxl. write. Number n = null;
Jxl. write. DateTime d = null;

// Pre-defined fonts and formats. It is best not to have too many font characters in the same Excel file.
WritableFont headerFont = new WritableFont (WritableFont. ARIAL, 12, WritableFont. BOLD, false, UnderlineStyle. NO_UNDERLINE, jxl. format. Colour. BLUE );
WritableCellFormat headerFormat = new WritableCellFormat (headerFont );

WritableFont titleFont = new WritableFont (WritableFont. ARIAL, 10, WritableFont. NO_BOLD, false, UnderlineStyle. NO_UNDERLINE, jxl. format. Colour. RED );
WritableCellFormat titleFormat = new WritableCellFormat (titleFont );

WritableFont detFont = new WritableFont (WritableFont. ARIAL, 10, WritableFont. NO_BOLD, false, UnderlineStyle. NO_UNDERLINE, jxl. format. Colour. BLACK );
WritableCellFormat detFormat = new WritableCellFormat (detFont );

NumberFormat nf = new NumberFormat ("0.00000"); // used for the Number format
WritableCellFormat priceFormat = new WritableCellFormat (detFont, nf );

DateFormat df = new DateFormat ("yyyy-MM-dd"); // used for date
WritableCellFormat dateFormat = new WritableCellFormat (detFont, df );

L = new Label (0, 0, "Excel file used for testing", headerFormat); // create some cells and add them to sheet.
Sheet. addCell (l );
// Add a title
Int column = 0;
L = new Label (column ++, 2, "title", titleFormat );
Sheet. addCell (l );
L = new Label (column ++, 2, "date", titleFormat );
Sheet. addCell (l );
L = new Label (column ++, 2, "currency", titleFormat );
Sheet. addCell (l );
L = new Label (column ++, 2, "price", titleFormat );
Sheet. addCell (l );
// Add content
Int I = 0;
Column = 0;
L = new Label (column ++, I + 3, "title" + I, detFormat );
Sheet. addCell (l );
D = new DateTime (column ++, I + 3, new java. util. Date (), dateFormat );
Sheet. addCell (d );
L = new Label (column ++, I + 3, "CNY", detFormat );
Sheet. addCell (l );
N = new jxl. write. Number (column ++, I + 3, 5.678, priceFormat );
Sheet. addCell (n );
I ++;
Column = 0;
L = new Label (column ++, I + 3, "title" + I, detFormat );
Sheet. addCell (l );
D = new DateTime (column ++, I + 3, new java. util. Date (), dateFormat );
Sheet. addCell (d );
L = new Label (column ++, I + 3, "SGD", detFormat );
Sheet. addCell (l );
N = new jxl. write. Number (column ++, I + 3, 98832, priceFormat );
Sheet. addCell (n );
// Set the column width
Column = 0;
Sheet. setColumnView (column ++, 20 );
Sheet. setColumnView (column ++, 20 );
Sheet. setColumnView (column ++, 10 );
Sheet. setColumnView (column ++, 20 );
Workbook. write ();
Workbook. close ();
System. out. println ("content writing" + fileName + "successful ");
}

Public static void readExcelInfo (String fileName)
Throws Exception {// obtain the number of rows and columns in an Excel file
Workbook book = Workbook. getWorkbook (new File (fileName); // construct a Workbook object
Sheet sheet = book. getSheet (0 );
// Obtain the cell in the first row of the first column // obtain the first worksheet object
Int columnum = sheet. getColumns (); // obtain the number of Columns
Int rownum = sheet. getRows (); // get the number of rows
System. out. println (columnum );
System. out. println (rownum );
For (int I = 0; I <rownum; I ++) // read and write cyclically
{
For (int j = 0; j <columnum; j ++ ){
Cell cell1 = sheet. getCell (j, I );
String result = cell1.getContents ();
System. out. print (result );
System. out. print ("\ t ");
}
System. out. println ();
}
Book. close (); // close (work thin) object
}

Public static void main (String [] args) {// main Entrance of the java program
Try {
System. out. println ("1. Create an Excel file, enter the Excel file name (including path and suffix )");
Repeated scan = new partition (System. in );
Final String fileName = scan. next (); // obtain the keyboard Value
WriteExcel (fileName); // call the Excel Generation Method

System. out. println ("2. Writing content to an Excel file ");
WriteContentToExcel (fileName); // call the method for writing content to Excel

System. out. println ("3. reading Excel files ");
ReadExcelInfo (fileName );
} Catch (Exception e) {// catch an Exception
E. printStackTrace ();
}
}
}