Import, export excel in Java

Source: Internet
Author: User
Tags dateformat italic font ming set background string format

First, Introduction

The current B/S model has become the mainstream of application development, and in the Enterprise Office system, often have customers such sub-requirements: You want our report directly with Excel open (telecommunications system, banking system). Or: We're used to printing in Excel. So in our actual development, many times need to implement the import, export of Excel applications.

Currently, there are two Jakarta poi and Java Excel Technologies that are commonly used to implement Java Import and export Excel.

I'll explain how to use these two techniques to import, export Excel

Ii. Importing and exporting Excel using Jakarta POI

Jakarta POI is a set of Java APIs for accessing Microsoft format documents. Jakarta POI has a number of components, including HSSF for manipulating Excel-formatted files and HWPF for manipulating word, and only the HSSF that are currently used to manipulate Excel are relatively mature in various components. Official Homepage Http://poi.apache.org/index.html,API Document Http://poi.apache.org/apidocs/index.html

2.1 Environment Configuration 2.1.1 Download jar

Official Download: http://poi.apache.org/download.html Here you can download to its latest version and documentation, currently the latest version is 3.7, which uses a more stable version 3.6.

2.1.2 Adding a jar package

Copy the Poi-3.6-20091214.jar and Lib directories under the root directory three generic packages Commons-logging-1.1.jar Junit-3.8.1.jar Log4j-1.2.13.jar to the project Lib

2.2 Jakarta POI HSSF API component

HSSF (the component used to manipulate Excel) provides users with objects in the Rg.apache.poi.hssf.usermodel package, including Excel objects, styles and formatting, and auxiliary operations. There are several objects:

Common components:

Hssfworkbook Document objects for Excel

Hssfsheet Excel Forms

Hssfrow lines of Excel

Hssfcell Excel's lattice unit

Hssffont Excel Fonts

Hssfdataformat Date format

Hssfheader Sheet Head

Hssffooter Sheet tail (you can see the effect only when you print)

Style:

Hssfcellstyle cell style

Secondary operations include:

Hssfdateutil Date

Hssfprintsetup Printing

Hssferrorconstants error Message Table

2.3 Basic Operation steps

First, to understand the organization of an Excel file, an Excel file corresponds to a workbook (Hssfworkbook), a workbook can have multiple sheet (hssfsheet) composition, A sheet is made up of multiple row (Hssfrow), and a row is made up of multiple cells (Hssfcell).

  Basic Operation steps:

1. Open or create an "Excel file Object" with Hssfworkbook

2. Return or create sheet object with Hssfworkbook object

3. Return the Row object with the sheet object and get the cell object with the Row object

4. Read and write to the Cell object.

Here's an example of a dynamically generated Excel file:

Create Hssfworkbook object Hssfworkbook WB = new Hssfworkbook ();//Create Hssfsheet object Hssfsheet sheet = wb.createsheet ("sheet0");// Create Hssfrow object Hssfrow row = Sheet.createrow (0);//Create Hssfcell Object Hssfcell Cell=row.createcell (0);// Set the cell value Cell.setcellvalue ("Chinese in Cell");//output Excel file FileOutputStream output=new fileoutputstream ("D:\\workbook.xls"); Wkb.write (output); Output.flush ();

HSSF read the file also use these objects, just the corresponding createxxx method into the GetXXX method can be. As long as the understanding of the principle, whether it is read or write or a specific format can be easily implemented, is so-called know it more to know its why.

2.4 Exporting an Excel application instance

In the 2.3 we have a few lines of code is actually implemented to export Excel a simple example, below we look at how to implement the exported Excel table?

The code is as follows: (The actual development should be encapsulated in the business layer component and then called in the control layer.) This is written directly in the control layer component, such as the Doget/dopost method of the servlet or the Execute method of the Struts framework)

Create Hssfworkbook object (Excel Document Object) Hssfworkbook WB = new Hssfworkbook ();//Create a new Sheet object (Excel form) Hssfsheet Sheet=wkb.creat Esheet ("score table");//Create the first row in sheet, the parameter is the row index (the line of Excel), can be any one hssfrow row1=sheet.createrow between 0~65535 (0);//Create cell (Excel cell      , the parameter is a column index, which can be any one hssfcell between 0~255 Cell=row1.createcell (0); Set the contents of the cell cell.setcellvalue ("Student exam results list");//Merge cell cellrangeaddress The construction parameter is the starting row, up to row, start column, and up to column sheet.addmergedregion (          New Cellrangeaddress (0,0,0,3));//Create a second row in sheet Hssfrow row2=sheet.createrow (1);      Create a cell and set the cell contents Row2.createcell (0). Setcellvalue ("name");          Row2.createcell (1). Setcellvalue ("class");          Row2.createcell (2). Setcellvalue ("Written test results"); Row2.createcell (3). Setcellvalue ("Test results");      Create a third row in sheet Hssfrow row3=sheet.createrow (2);      Row3.createcell (0). Setcellvalue ("Li Ming");      Row3.createcell (1). Setcellvalue ("As178");          Row3.createcell (2). Setcellvalue (87);      Row3.createcell (3). Setcellvalue (78); //..... Omit part of code//output Excel file OutputStream output=response.getoutputstreAM ();    Response.reset (); Response.setheader ("Content-disposition", "attachment;    Filename=details.xls ");            Response.setcontenttype ("Application/msexcel");    Wkb.write (output); Output.close (); Retrun null;

Underline this part of the code is the B/s mode used in the output mode, rather than output to the local specified disk directory. The code indicates that the Details.xls Excel file is output to the requesting client browser through the answer entity (response), which the client can either save or open directly.

2.5 style settings

Excel files that are exported in real-world applications often need to be read and printed, which requires formatting and styling of the output Excel document, including merging cells, setting cell styles, setting font styles, and so on.

2.5.1 Cell Merging

Using the Hssfsheet addmergedregion () method

public int addmergedregion (cellrangeaddress region)

The parameter cellrangeaddress represents the merged area, constructed as follows:

cellrangeaddress (int firstrow, int lastrow, int firstcol, int lastcol)

The construction parameters, in turn, represent the starting row, the ending line, the Start column, and the column. Example code Reference 2.4 part

2.5.2 set the row height and column width of a cell
Hssfsheet Sheet=wb.createsheet (); sheet.setdefaultrowheightinpoints (10);//Set default column High Sheet.setdefaultcolumnwidth (20) ;//Set the default column width//Set the column width of the specified columns, 256 * 50 This is because the width parameter unit is one of 256 points of a single character sheet.setcolumnwidth (Cell.getcolumnindex (), 256 * 50);
2.5.2 cell style

1. Create Hssfcellstyle

Hssfcellstyle Cellstyle=wkb.createcellstyle ();

2. Set style

Set the horizontal and vertical alignment of the cell, the specific parameters are not listed, refer to Hssfcellstyle  cellstyle.setalignment (hssfcellstyle.align_justify);  Cellstyle.setverticalalignment (hssfcellstyle.vertical_center);  /* Set the cell's fill, and the foreground color and background color   three points note:   1. If you need a foreground color or background color, be sure to specify the Fill method, the order does not matter;   2. If both foreground and background colors exist, the foreground color setting should be written in front;   3. The foreground color is not a font color.  *  ///Set Fill mode (fill pattern)  cellstyle.setfillpattern (hssfcellstyle.diamonds);  Set foreground color  cellstyle.setfillforegroundcolor (HSSFColor.RED.index);  Set Background color  cellstyle.setfillbackgroundcolor (HSSFColor.LIGHT_YELLOW.index);  Set the border and its style and color at the bottom of the cell  //The bottom border is only set here, and the left, right and top borders are similarly set  Cellstyle.setborderbottom (hssfcellstyle.border_ SLANTED_DASH_DOT);  Cellstyle.setbottombordercolor (HSSFColor.DARK_RED.index);  Sets the display style for date data  Cellstyle.setdataformat (Hssfdataformat.getbuiltinformat ("M/d/yy h:mm"));

3. Apply styles to cells

  Cell.setcellstyle (CellStyle);  Apply styles to rows, but some styles only work on cells  Row.setrowstyle (cellstyle);
2.5.2 Setting font styles

1. Create a Hssffont object (call Hssfworkbook's CreateFont method)

Hssfworkbook wb=new Hssfworkbook (); Hssffont  Fontstyle=wb.createfont (); Hssfworkbook wb=new Hssfworkbook ();

2, set the font of various styles

  Set font style  fontstyle.setfontname ("Arial");    Set the font height  fontstyle.setfontheightinpoints ((short));    Set Font color  font.setcolor (HSSFColor.BLUE.index);  Set the Bold  fontstyle.setboldweight (hssffont.boldweight_bold);  Set Italic font.setitalic (TRUE);//Set Underline font.setunderline (Hssffont.u_single);

3. Set font to cell style

Fonts are also part of cell formatting, so you assign a font object to a cell style object Cellstyle.setfont (font) from hssfcellstyle//, or apply a cell style to a cell cell.setcellstyle ( CellStyle);
2.6 Importing an Excel app instance

The basic steps to implement importing data from an existing Excel file into the system are similar to exporting, with the key to understanding the structure of the Excel file to be imported, such as what columns the data is in, where the data is starting to start (valid data starts from the first few rows), and so on. In real-world projects, because these data (Excel files) often come from business data generated by daily office workers or other system platforms, the data formats of these Excel files need to be unified and provide access interfaces (referring to access paths), This allows the data to be obtained by invoking the method in the system of the desired data by providing this provider. A solution that uses Web service is a good choice. Here, we're going to import 2. 4 generated Excel table for example, focus on how to write import Excel code

Public list<scoreinfo> Loadscoreinfo (String xlspath) throws ioexception{    List temp = new ArrayList (); FileInputStream Filein = new FileInputStream (xlspath)//import Excel from the specified file input stream to produce Workbook object Workbook wb0 = new Hssfworkbook (Filein);//Gets the first form in an Excel document sheet sht0 = wb0.getsheetat (0);//iterates over each line in sheet for        (row r:sht0) {        // If the line number of the current line (starting at 0) does not reach 2 (the third row) then the new loop if (R.getrownum () <1) {continue;} Create an entity class Scoreinfo info=new scoreinfo ();//Remove the 1th cell data from the current row and encapsulate it on the info entity stuname attribute info.setstuname (R.getcell (0). Getstringcellvalue ()); Info.setclassname (R.getcell (1). Getstringcellvalue ()); Info.setrscore (R.getCell (2). Getnumericcellvalue ());            Info.setlscore (R.getcell (3). Getnumericcellvalue ()); Temp.add (info);        }        Filein.close ();            return temp;        }
Third, use Java Excel to manipulate Excel files

Java Excel is an open source project through which Java developers can read the contents of an Excel file, create a new Excel file, and update an existing Excel file. JXL, with its compact and easy-to-use features, has gradually replaced the poi-excel status, becoming the preferred choice for more and more Java developers to generate Excel files. Features of Java Excel:

Supports all versions of Excel 95-2000
Generate Excel 2000 Standard format
Supports font, number, date formatting operations
Supports adding shading and coloring to cells;

Modify the existing worksheet;
Support for images and charts

Log records can be customized

Smaller, faster, and less memory

It should be said that the above features have been able to roughly meet our needs. The key is that this API is pure Java, and does not rely on windows, even if running under Linux, it can also correctly handle Excel files. It is also important to note that this set of APIs has limited support for graphics and charts, and only the PNG format is recognized. Online Help Document Http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/index.html

Here we will learn to master reading, new, and updated, including the settings of common formats: fonts, colors, backgrounds, merged cells, and so on, which are basically enough to deal with most of the problems.

3.1 Environment Configuration 3.1.1 Download

http://www.andykhan.com/jexcelapi/

3.1.2 Adding a jar package

Copy the Jxl.jar to the project Lib

3.2 Exporting Excel files using the Java Excel Api

Here we look at how to use Java Excel implementation to export Excel table?

The code is as follows: (The actual development should be encapsulated in the business layer component and then called in the control layer.) This is written directly in the control layer component, such as the Doget/dopost method of the servlet or the Execute method of the Struts framework)

Get the output stream, the output media of the output stream is the client browser OutputStream output=response.getoutputstream ();  Response.reset ();           Response.setheader ("Content-disposition", "attachment;  Filename=temp.xls ");  Response.setcontenttype ("Application/msexcel"); Create a writable Excel workbook, and the content will be written to the output stream and exported to the client via output stream
Writableworkbook Wk=workbook.createworkbook (output);///Create a writable Excel worksheet Writablesheet sheet=wk.createsheet ("score table", 0) ;//merge the cell (column, row) into the cell (Column1, row1). MergeCells (column, row, Column1, row1); Sheet.mergecells (0,0, 4,0);//Cell Merge method//Create Writablefont Font object, the parameters are bold, font size 12, bold, non-italic, not underlined, bright blue writablefont titlefont= New Writablefont (Writablefont.createfont ("Blackbody"), 12,writablefont.bold,false,underlinestyle.no_underline, Colour.light_blue);//Create a Writablecellformat object, apply the object to the cell and set the cell style Writablecellformat titleformat=new Writablecellformat ();//Set Font format titleformat.setfont (titlefont);//Set Text horizontal center align Titleformat.setalignment ( Alignment.centre);//Set text vertically centered aligned titleformat.setverticalalignment (verticalalignment.centre);// Set the background color titleformat.setbackground (colour.gray_25);//Set the line wrap Titleformat.setwrap (TRUE);//Add a Label object, which in turn represents the first column, the first row, Content, using the Format label Lab_00=new label (0,0, "Student exam score List", titleformat);//Add a defined Label object to the worksheet so that the first row of the first column of the worksheet contains ' Student exam Results list ' and applied TitleFormat defined style Sheet.addcell (lab_00); Writablecellformat CloumntitleformAt=new Writablecellformat (); Cloumntitleformat.setfont (New Writablefont (Writablefont.createfont ("Arial"), 10, Writablefont.bold,false)); Cloumntitleformat.setalignment (alignment.centre); Label Lab_01=new label (0,1, "name", Cloumntitleformat); Label Lab_11=new label (1, 1, "Class", Cloumntitleformat); Label Lab_21=new label (2,1, "written test score", Cloumntitleformat);
Label Lab_31=new label (3,1, "on-machine score", Cloumntitleformat); Label Lab_41=new label (4,1, "Test Date", Cloumntitleformat); Sheet.addcell (LAB_01); Sheet.addcell (Lab_11); Sheet.addcell (lab_21); Sheet.addcell (lab_31); Sheet.addcell (lab_41); Sheet.addcell (New Label (0,2, "Li Ming")); Sheet.addcell (New Label ("As178"));//define the number format NumberFormat nf=new numberformat ("0.00"); Writablecellformat wcf=new Writablecellformat (NF);//similar to a Label object, the difference between a label representation of text data and a number representing numeric data numbers numlab_22=new Number (2,2,78,WCF); Sheet.addcell (numlab_22); Sheet.addcell (Newnumber (3,2,87,new Writablecellformat (new NumberFormat ("#.##")));//define date format DateFormat df=new dateformat ("Yyyy-mm-dd hh:mm:ss");// Creates a Writablecellformat object Writablecellformat datewcf=new Writablecellformat (DF);//similar to a Label object, which distinguishes the label from the text data, DateTime represents datetime dtlab_42=new datetime (4,2,new date (), DATEWCF), Sheet.addcell (dtlab_42); Outputs the defined worksheet to the previously specified media (here is the client browser) wk.write ();//When the operation is complete, close the object, freeing up the occupied memory Space Wk.close ();

Underline this part of the code is the B/s mode used in the output mode, rather than output to the local specified disk directory. This code means that the Temp.xls Excel file is output to the requesting client browser via the answer entity (response), which is downloaded to the client locally (either saved or opened directly). To output directly to a disk file, replace the underlined part of the code with the following code

3.3 Advanced Operation 3.3.1 Data formatting

No complex data types are involved in Excel, and the ability to handle strings, numbers, and dates can be a good fit for general applications.

The formatting of the data involves elements such as font, weight, and font size, which are mainly responsible for the Writablefont and Writablecellformat classes. For example:

①writablefont font=new Writablefont (Writablefont.createfont ("Song Body"), 12,writablefont.no_bold);

②writablecellformat format1=new Writablecellformat (font);

③label label=new Label (0,0, "Data 4 test", FORMAT1);

which

I. Specified string format: font is Arial, size 16, bold display. Writablefont has a very rich set of constructs for use in different situations, with a detailed list of Jexcelapi Java-doc, which are no longer listed here.

II. The code uses the Writablecellformat class, which is important to specify the various properties of the cell, as shown in the previous example code.

III. The constructor of the label class is used, specifying the display position, text content, and the format that the string is given.

A number, a datetime, that is similar to a label, a label that represents text data, a numeric value data that enables NumberFormat to format data, and a datetime representation of date data that can be applied dateformat formatted data.

3.3.2 Cell Operations

An important part of Excel is the operation of cells, such as row height, column width, cell merging, and so on, fortunately JEXCELAPI provides these support. These operations are relatively straightforward and only the relevant APIs are described below.

1 , Merge Cells

Writablesheet.mergecells (int m,int n,int p,int q);//functions are all merged from (M,n) to (P,Q) cells, for example: Writablesheet sheet=book.createsheet ("first page", 0);//Merge all cells Sheet.mergecells (0,0,5,0) from the first row of the first column to the first row in column sixth;//the merge can be either horizontal or vertical. Merged cells cannot be merged again, otherwise an exception is triggered.

2 , row height and column width

3.4 Reading a data table from an Excel file

Let's take an example of how to write an imported Excel code (the code encapsulated in the business layer method), as the Excel table produced in Import 3.2.

Public list<scoreinfo> Loadscoreinfo (String xlspath) throws IOException, biffexception{//import an existing Excel file,        Get read-only workbook FileInputStream fis=new FileInputStream (Xlspath);        Workbook Wk=workbook.getworkbook (FIS);        Get the first Sheet table Sheet sheet=wk.getsheet (0);        Gets the total number of rows int rownum=sheet.getrows ();//iterates through the data rows for each line for (int i=2;i<rownum;i++) {scoreinfo info=new scoreinfo (); Getcell (Column,row), which represents the cell (cell)//getcontents () that obtains the specified row of the designated column, gets the contents of the cell and returns the string data.            Cells for character data//Use entity classes to encapsulate cell data info.setstuname (Sheet.getcell (0, I). getcontents ());            Info.setclassname (Sheet.getcell (1, i). getcontents ()); Determines the type of cell, the main type of cell, label, number, DATE if (Sheet.getcell (2,i). Gettype==celltype.number) {//Convert to numeric cell Numberce ll numcell= (Numbercell) Sheet.getcell (2,i)//numbercell GetValue () method gets the numeric data of the cell Info.setrscore (Numcell.getvalue () );} if (Sheet.getcell (3,i). Gettype==celltype.number) {Numbercell numcell= (Numbercell) Sheet.getcell (3,i); Info.setrscore (Numcell.getvaLue);} if (Sheet.getcell (4,i). gettype==celltype.date) {Datecell datecell= (Datecell) Sheet.getcell (4,i);//datecell getdate        () method Gets the date type data of the cell Info.setdate (Datecell.getdate ());}        } fis.close (); Wk.close ();}
3.4 Updating an existing Excel file

Change the class name of the first record of the Excel table (Temp.xls) generated by 3.2 (the third line of the Excel file) to As179 with the following code:

File File=new file ("D://temp.xls");//import an existing Excel file, get read-only workbook Workbook wk=workbook.getworkbook (file);// Creates a writable Excel workbook object based on a read-only workbook (wk)         writableworkbook wwb=workbook.createworkbook (file, wk);//Read First         sheet Writablesheet sheet=wwb.getsheet (0);///Get the Cell object to edit         Writablecell Cell=sheet.getwritablecell (1, 2);        Determine the type of cell, make the appropriate conversion if (Cell.gettype () ==celltype.label) {            label lable= (label) cell;            Modify the contents of the cell lable.setstring ("As179");        }    Wwb.write ();     Wwb.close ();     

To update an existing Excel file is to get the existing workbook (but read-only) and then convert the fetched read-only workbook into a writable Excel workbook object (Writableworkbook), Other parts are edited by writable Writablesheet objects and writable Writablecell objects.

Import, export Excel

in Java

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.