Java read, write, modify Excel file __java

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

Java Excel is an open source project through which Java developers can read the contents of Excel files, create new Excel files, and update existing Excel files. Using this API non-Windows operating systems can also work with Excel data tables through a pure Java application. Because it is written in Java, we can use the JSP, servlet to invoke the API to implement the access to Excel datasheet in the Web application.

Provides the following features:

Read data from files in Excel 95, 97, 2000, and so on;
Read the Excel formula (you can read the formula after Excel 97);
Generate Excel Datasheet (formatted as Excel 97);
Support the format of fonts, numbers and dates;
Support for Cell shadow operation, and color operation;
Modify the existing data table;
Ability to read chart information
1. Application Example:
This includes 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 ()
{
}

/**
* Read 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") here are two ways to get the Sheet table, 1 for the first name, 2 for the subscript, starting from 0
Sheet st = Rwb.getsheet ("original");
Cell c00 = St.getcell (0,0);
Common way to get a cell value, return a string
String strc00 = c00.getcontents ();
How to get cell specific type values
if (c00.gettype () = = Celltype.label)
{
Labelcell labelc00 = (Labelcell) c00;
STRC00 = Labelc00.getstring ();
}
Output
System.out.println (STRC00);
Shut down
Rwb.close ();
}
catch (Exception e)
{
E.printstacktrace ();
}
}

  /**
    * Output Excel
    *
    * @param os
    */
   public static void Writeexcel (OutputStream os)
   {
        try
       {
           /**
            * Workbook can only be created through the factory method provided by the API, not by using the Writableworkbook constructor,
             * Because the constructor for class Writableworkbook is protected type
             * Method (1) reads Writableworkbook WWB = workbook.createworkbook (new file (TargetFile)) directly from the target file; The
            * Method (2) is shown in the following example Writes the Writableworkbook directly to the output stream

*/
Writableworkbook WWB = workbook.createworkbook (OS);
Create Excel worksheet specify name and location
Writablesheet ws = Wwb.createsheet ("Test Sheet 1", 0);

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

1. Add Label Object
Label label = new label (0,0, "This is a label test");
Ws.addcell (label);

          //Add object with font formatting
            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 formatting object with 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 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 (1,1,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 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 a Picture object, JXL only supports PNG format pictures
File image = New file ("F://2.png");
Writableimage wimage = new Writableimage (0,1,2,2,image);
Ws.addimage (Wimage);
Writing to Worksheets
Wwb.write ();
Wwb.close ();
}
catch (Exception e)
{
E.printstacktrace ();
}
}

/**
* After the copy, make the modification, where file1 is the Copy object, file2 the object created after the modification
* Do not remove the original formatting modification of the cell, we can add the new cell modification
* To make the contents of a cell behave in a different way
* @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 type of cell and make a corresponding conversion
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
{
Reading 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 Excel
Excelhandle.modifyexcel (New file (""), New File (""));
}
catch (Exception e)
{
E.printstacktrace ();
}
}
}

2. Do the related tests in the JSP, create a writeexcel.jsp
<%
Response.reset ();/clear buffer
Response.setcontenttype (" Application/vnd.ms-excel ");
File FileWrite = new file ("F:/testwrite.xls");
Filewrite.createnewfile ();
New FileOutputStream (FileWrite);
Excelhandle.writeexcel (New FileOutputStream (FileWrite));
%>
You can dynamically generate an Excel document by browsing writeexcel.jsp in IE, where Response.setcontenttype ("application/vnd.ms-excel"), the statement must, To ensure that no garbled, in the JSP input <% @page contenttype= "APPLICATION/VND.MS-EXCEL;CHARSET=GBK"%> not.