Read, write, and modify Excel files in Java

Read, write, and modify Excel files in Java
Click 88doc. com to find more documents.

Java Excel is an open source project. Java developers can read the content of an Excel file, create a new Excel file, and update an existing Excel file. Using this API, non-Windows operating systems can also use Java-only applications to process Excel Data Tables. Because it is written in Java, we can use JSP and Servlet in web applications to call APIs to access Excel Data Tables.

Provides the following functions:

Reads data from files in Excel formats such as 95, 97, and 2000;
Read the Excel Formula (the formulas after Excel 97 can be read );
Generate an Excel data table in the format of Excel 97 );
Supports formatting fonts, numbers, and dates;
Supports cell shadow and color operations;
Modify an existing data table;
Able to read chart information
1. Application Example:
Including reading data from Excel, generating new Excel, and modifying Excel
Package common. util;

Import jxl .*;
Import jxl. format. underlinestyle;
Import jxl. Write .*;
Import jxl. Write. number;
Import jxl. Write. boolean;

Import java. Io .*;

/**
* Created by intellij idea.
* User: XL
* Date: 2005-7-17
* Time: 9:33:22
* To change this template use file | Settings | file templates.
*/
Public class excelhandle
{
Public excelhandle ()
{
}

/**
* Reading Excel
*
* @ Param filepath
*/
Public static void readexcel (string filepath)
{
Try
{
Inputstream is = new fileinputstream (filepath );
Workbook RWB = Workbook. getworkbook (is );
// Sheet ST = RWB. getsheet ("0") There are two ways to obtain the sheet table: 1 is the name, 2 is the subscript, starting from 0
Sheet ST = RWB. getsheet ("original ");
Cell c00 = ST. getcell (0, 0 );
// A general method for obtaining cell values. A string is returned.
String strc00 = c00.getcontents ();
// Obtain the specific cell type value
If (c00.gettype () = celltype. Label)
{
Labelcell labelc00 = (labelcell) c00;
Strc00 = labelc00.getstring ();
}
// Output
System. Out. println (strc00 );
// Close
RWB. Close ();
}
Catch (exception E)
{
E. printstacktrace ();
}
}

/**
* Excel output
*
* @ Param OS
*/
Public static void writeexcel (outputstream OS)
{
Try
{
/**
* You can only create a workbook using the factory method provided by the API, instead of using the writableworkbook constructor,
* Because the constructor of the writableworkbook class is of the protected type
* Method (1) read writableworkbook WWB = Workbook. createworkbook (new file (targetfile) from the target file directly ));
* Method (2) as shown in the following example, writableworkbook is directly written to the output stream.

*/
Writableworkbook WWB = Workbook. createworkbook (OS );
// Create an Excel worksheet and specify the name and position
Writablesheet Ws = WWB. createsheet ("test sheet 1", 0 );

// ************** Add data to the worksheet *****************

// 1. Add a label object
Label Label = new label (0, 0, "This is a label test ");
WS. addcell (Label );

// Add a formatting object with a font
Writablefont WF = new writablefont (writablefont. Times, 18, writablefont. Bold, true );
Writablecellformat WCF = new writablecellformat (WF );
Label labelcf = new label (1, 0, "This is a label test", WCF );
WS. addcell (labelcf );

// Add a formatting object with a font color
Writablefont WFC = new writablefont (writablefont. Arial, 10, writablefont. no_bold, false,
Underlinestyle. no_underline, jxl. format. colour. Red );
Writablecellformat wcffc = new writablecellformat (WFC );
Label labelcf = new label (1, 0, "This is a label cell", wcffc );
WS. addcell (labelcf );

// 2. Add a number object
Number labeln = new number (0, 1, 3.1415926 );
WS. addcell (labeln );

// Add a number object with formatting
Numberformat NF = new numberformat ("#.##");
Writablecellformat wcfn = new writablecellformat (NF );
Number labelnf = new jxl. Write. Number (3.1415926, wcfn );
WS. addcell (labelnf );

// 3. Add a Boolean object
Boolean labelb = new jxl. Write. boolean (0, 2, false );
WS. addcell (labelb );

// 4. Add a datetime object
Jxl. Write. datetime labeldt = new jxl. Write. datetime (0, 3, new java. util. Date ());
WS. addcell (labeldt );

// Add a dateformat object with formatting
Dateformat df = new dateformat ("dd mm yyyy hh: mm: SS ");
Writablecellformat wcfdf = new writablecellformat (DF );
Datetime labeldtf = new datetime (1, 3, new java. util. Date (), wcfdf );
WS. addcell (labeldtf );

// Add an image object. jxl only supports images in PNG format.
File image = new file ("F: // 2.png ");
Writableimage wimage = new writableimage (0, 1, 2, 2, image );
WS. addimage (wimage );
// Write a worksheet
WWB. Write ();
WWB. Close ();
}
Catch (exception E)
{
E. printstacktrace ();
}
}

/**
* After the object is copied, modify it. file1 is the object to be copied, and file2 is the object created after the modification.
* The original cell Formatting cannot be removed. We can still add the new cell modifier,
* To make the content of cells express in different forms
* @ Param file1
* @ Param file2
*/
Public static void modifyexcel (File file1, file file2)
{
Try
{
Workbook RWB = Workbook. getworkbook (file1 );
Writableworkbook WWB = Workbook. createworkbook (file2, RWB); // copy
Writablesheet Ws = WWB. getsheet (0 );
Writablecell WC = ws. getwritablecell (0, 0 );
// Determine the cell type and convert it accordingly
If (WC. GetType = celltype. Label)
{
Label Label = (Label) WC;
Label. setstring ("the value has been modified ");
}
WWB. Write ();
WWB. Close ();
RWB. Close ();
}
Catch (exception E)
{
E. printstacktrace ();
}
}

// Test
Public static void main (string [] ARGs)
{
Try
{
// Read Excel
Excelhandle. readexcel ("F:/testread.xls ");
// Output Excel
File filewrite = new file ("F:/testwrite.xls ");
Filewrite. createnewfile ();
Outputstream OS = new fileoutputstream (filewrite );
Excelhandle. writeexcel (OS );
// Modify the Excel file
Excelhandle. modifyexcel (new file (""), new file (""));
}
Catch (exception E)
{
E. printstacktrace ();
}
}
}

2. perform related tests in JSP and create a writeexcel. jsp file.
<%
Response. Reset (); // clear the buffer
Response. setcontenttype ("application/vnd. MS-excel ");
File filewrite = new file ("F:/testwrite.xls ");
Filewrite. createnewfile ();
New fileoutputstream (filewrite );
Excelhandle. writeexcel (New fileoutputstream (filewrite ));
%>
Browse writeexcel in IE. JSP can dynamically generate Excel documents, where response. setcontenttype ("application/vnd. MS-excel "); the statement must be required to ensure no garbled characters. In JSP, enter <% @ page contenttype =" application/vnd. MS-Excel; charset = GBK "%> NO.