JAVA Excel API Learning case and javaexcelapi case

JAVA Excel API Learning case and javaexcelapi case

Paste the code first, run it, check the effect, then take a look at the comments, and then look at the basics behind the code

Create a new excel file and write data:

Public static void myExcel2 () throws IOException, WriteException {WritableWorkbook wwb = Workbook. createWorkbook (new File ("F:" + File. separator + "myExcel1.xls"); // create a sheet entry in the excel table WritableSheet = wwb. createSheet ("Page 1", 0); // The cell element in excel can have many data types, such as Number and Label. // In the API, many cell types are provided for us. // cell format editing // merged cell format sheet. mergeCells (0, 0, 5, 0 ); /* =============== Case Study of text format in cells =================* // font in cell elements (font format, color, Size Control) WritableCellFormat forFont = new WritableCellFormat (); // cell style object WritableFont font = new WritableFont (WritableFont. ARIAL); // select the font. setColour (Colour. RED); // color font. setPointSize (20); // size font. setUnderlineStyle (UnderlineStyle. DOUBLE); // underline forFont. setFont (font); // Add the font style object to the cell style object forFont. setAlignment (Alignment. CENTRE); // text center // create a cell and specify the location, content and format Label topLabel = new Label (0, 0, "merge cells, set font format ", forFont); // Add the created Cell Object to the sheet page. addCell (topLabel); sheet. setrowviews (0, 1000 ); /* ============== formatting case for numbers in cells ===================* // create a number formatting object NumberFormat numFormat = new NumberFormat ("#. 000 "); // create a cell formatting object and add a Number formatting object WritableCellFormat forNumber = new WritableCellFormat (numFormat); Number myNumber = new Number (0, 1, 3.14159, forNumber ); sheet. addCell (myNumber ); /* ============= formatting the date in the cell =================== */DateFormat dateFormat = new DateFormat (" yyyy-MM-dd "); writableCellFormat forDate = new WritableCellFormat (dateFormat); forDate. setBackground (Colour. BLUE); // set the cell color DateTime myDate = new DateTime (1, 1, new Date (), forDate); sheet. addCell (myDate); // write the wwb file. write (); // close wwb. close ();}

Using java to create a brand new Excel document, I learned the API and summarized the process as follows:

1. you need to create a Workbook object that can be written, that is, WritableWorkbook. The input parameter can also be a file output stream, it can also be an object created through File (the parameter is the File name), for example:

WritableWorkbook wwb = newWritableWorkbook (new File ("myExcel.xls "));

2. Create an editable WritableSheet object. In an Excel document, multiple sheet pages can be created. The first sheet is specified as 0, for example:

WritableSheet sheet = wwb. createSheet ("sheet1", 0 );

3. First, create a corresponding type of formatting object, such

NunberFormat dateformat = new NunberFormat ("yyyy-MM-dd") object

4. Create an Excel cell to format the object, and pass the preceding format object as a parameter. For example

WritableCellFormat datecellformat = new WritableCellFormat (dateformat );

5. Create a cell object, set the position, value, and specify the cell format object.

DateTime mydate = new DateTime (0, 0, new Date (), datecellformat );

Parameter description of the cell formatting object: here we take the constructor of the four parameters as an example:

The first parameter is the column number, the second parameter is the row number, the third parameter is the value, and the fourth parameter is optional, indicating the format, because the fourth parameter can be set through setCellFormat () method specified format

6. Add the created Cell Object to the sheet.

7. write: wwb. write ();

8. close Operation: wwb. close ()

NOTE: If an output stream exists, disable it when it is not needed.

The Number is also found in java. Therefore, we must import jsl. write. Number here; otherwise, the following statement will report an error:

Number myNumber = new Number (0, 1, 3.14159, forNumber );

Description of date format parameters:

Yyyy four-digit year MM two-digit month dd two-digit day hh two-hour digit mm two-minute digit (capital represents month) ss two-digit seconds

Common formats:

Yyyy-MM-dd hh: mm: ss (special characters in the middle can be set at will, generally including "-", "/", "")

Parameters for formatting numbers:

In java, # And 0 are used for digit formatting. The detailed explanation is as follows:

Retain the integer part and three decimal places: #. 000

Take two integers and two decimal places: 00.00

When the integer part is not enough, it will be supplemented with 0, and the number of missing parts will be supplemented with 0

%: Add a % symbol after the above format

#. ### % ("#" After the decimal point is equivalent to "0 ")

Scientific Notation: Use E letters, such as: 00.000E0

Number Division: Add a "," in the middle of each three digits

Import java. text. decimalFormat; public class TestNumberFormat {public static void main (String [] args) {double pi = 3.1415927; // The circumference rate // gets an integer System. out. println (new DecimalFormat ("0 "). format (pi); // 3 // take an integer and two decimal places System. out. println (new DecimalFormat ("0.00 "). format (pi); // 3.14 // take two integers and three decimal places. The less integer is filled with 0. System. out. println (new DecimalFormat ("00.000 "). format (pi); // 03.142 // obtain all integer parts of the System. out. println (new DecimalFormat ("#"). format (pi); // 3 // count by percentage, and take two decimal places System. out. println (new DecimalFormat ("#. # % "). format (pi); // 314.16% long c = 299792458; // speed of light // display as a scientific notation, and take the five decimal places System. out. println (new DecimalFormat ("#. ##### E0 "). format (c); // 2.99792E8 // displays the scientific notation of two integers, and takes four decimal places. out. println (new Dec ImalFormat ("00. #### E0"). format (c); // 29.9792E7 // separate each three digits with commas. System. out. println (new DecimalFormat (",###"). format (c); // 299,792,458 // embed the format into the text System. out. println (new DecimalFormat ("the speed of light is every second, ### meters. "). Format (c ));}}

To be continued, read and modify existing files as well as common format parameters

Java excel APIs

In this way, it should be in your template ..
Let's see if you will fade down when writing in this template ..
Java only writes data to this cell. The cell style is not changed.

Java Excel API documentation

Poi.apache.org/apidocs/index.html

POI

Write 123 to cell D3

Import java. io. FileOutputStream;
Import java. io. IOException;
Import java. io. OutputStream;

Import org. apache. poi. hssf. usermodel. HSSFWorkbook;

Public class ExcelUtil {

Public static void main (String [] args) throws IOException {

String file = "c :\\ test.xls ";
HSSFWorkbook workbook = new HSSFWorkbook ();

Workbook. createSheet ("my worksheet"). createRow (2). createCell (3). setCellValue ("123 ");

OutputStream output = new FileOutputStream (file );
Workbook. write (output );
Output. flush ();
Output. close ();

}

}