Use Java to read Excel table data and export data to an Excel table

Recently, a large number of EXCEL tables are required for the project. We plan to make all the data into an Excel table and import the data from the Excel table to the database through the editor. The editor provides various editing operations, you can also export a database to an Excel table. With the help of Java Excel API, this problem is very simple.

I. The simplest method in history

For simple tables (plain text), you can actually use a simpler method without using Java Excel APIs! Use tabs/t to separate each field, use line breaks/N to separate each line, and change the suffix of the file to ". xls! In this way, the Excel table cannot be formatted (such as color, border, and alignment ).

Ii. Java Excel API

Java Excel is an open-source project through which Java developers can read the content of Excel files, create new Excel files, and update existing Excel files, in the project, you need to import the file named jxl. jar package. Here is just an example of its basic usage. For other advanced functions (such as images, formulas, and formats), please refer to the help document of Java excel. Here is information about it: http://jexcelapi.sourceforge.net /.

If an Excel table of user data contains ID, user name, gender, email, and other information, define a user JavaBean:

Package COM. monitor1394.excel; </P> <p>/** <br/> * User <br/> * @ author Monitor <br/> * created on 2010-12-22, 9:57:58 <br/> */<br/> public class user {<br/>/** ID */<br/> private int ID; <br/>/** user name */<br/> private string name; <br/>/** gender 1: Male 2: female */<br/> private int sex; <br/>/** email */<br/> private string email; </P> <p> public user () {<br/>}</P> <p> public user (int id, string name, int sex, string email) {<br/> This. id = ID; <br/> This. name = Name; <br/> This. sex = sex; <br/> This. email = Email; <br/>}</P> <p> Public String getemail () {<br/> return email; <br/>}</P> <p> Public void setemail (string email) {<br/> This. email = Email; <br/>}</P> <p> Public int GETID () {<br/> return ID; <br/>}</P> <p> Public void setid (int id) {<br/> This. id = ID; <br/>}</P> <p> Public String getname () {<br/> return name; <br/>}</P> <p> Public void setname (string name) {<br/> This. name = Name; <br/>}</P> <p> Public int getsex () {<br/> return sex; <br/>}</P> <p> Public void setsex (INT sex) {<br/> This. sex = sex; <br/>}</P> <p> @ override <br/> Public String tostring () {<br/> return ID + ": "+ name; <br/>}< br/>}

The Excel table operation class is as follows. The format of some cells can be specified as needed:

Package COM. monitor1394.excel; </P> <p> Import Java. io. file; <br/> Import Java. io. ioexception; <br/> Import Java. util. arraylist; <br/> Import Java. util. list; <br/> Import jxl. sheet; <br/> Import jxl. workbook; <br/> Import jxl. format. alignment; <br/> Import jxl. format. border; <br/> Import jxl. format. borderlinestyle; <br/> Import jxl. format. colour; <br/> Import jxl. format. verticalalignment; <br/> Import jxl. read. B IFF. biffexception; <br/> Import jxl. write. label; <br/> Import jxl. write. number; <br/> Import jxl. write. numberformats; <br/> Import jxl. write. writablecellformat; <br/> Import jxl. write. writablefont; <br/> Import jxl. write. writablesheet; <br/> Import jxl. write. writableworkbook; <br/> Import jxl. write. writeexception; </P> <p>/** <br/> * Excel table Operations <br/> * @ author Monitor <br/> * created on 2010-1 2-22, 9:50:28 <br/> */<br/> public class Excel {<br/>/** title cell format */<br/> Private Static writablecellformat titleformat = NULL; <br/>/** subject content cell format */<br/> Private Static writablecellformat bodyformat = NULL; <br/>/** comment cell format */<br/> Private Static writablecellformat noteformat = NULL; <br/>/** cell format of floating point data */<br/> Private Static writablecellformat floatformat = NULL; <br/>/** cell format of Integer Data */<br/> P Rivate static writablecellformat intformat = NULL; <br/>/** initialize data */<br/> Private Static Boolean init = false; </P> <p>/** private constructor to prevent incorrect use of the Excel class */<br/> private Excel () {<br/>}</P> <p>/** <br/> * initialization of various cell formats <br/> * @ throws writeexception initialization failed <br/> * /<br/> Private Static void Init () throws writeexception {<br/> writablefont font1, font2, font3, font4; <br/> // Arial font, No. 9, bold, cell yellow, dashboard, center alignment <br/> font1 = New writablefont (writablefont. arial, 9, writablefont. bold, false); <br/> titleformat = new writablecellformat (font1); <br/> titleformat. setbackground (colour. yellow); <br/> titleformat. setborder (border. all, borderlinestyle. thin); <br/> titleformat. setalignment (alignment. centre); <br/> // Arial font, 9, bold, yellow cell, border, align in the left-right corner, align vertically in the center, automatic line feed <br/> font2 = new writablefont (writablefont. arial, 9, writablefont. Bold, false); <br/> noteformat = new writablecellformat (font2); <br/> noteformat. setbackground (colour. yellow); <br/> noteformat. setborder (border. all, borderlinestyle. thin); <br/> noteformat. setalignment (alignment. centre); <br/> noteformat. setverticalalignment (verticalignment. centre); <br/> noteformat. setwrap (true); <br/> // Arial font, number 9, non-bold, cell light green, dashboard <br/> font3 = new writablefont (writablefont. A Rial, 9, writablefont. no_bold, false); <br/> bodyformat = new writablecellformat (font3); <br/> bodyformat. setbackground (colour. light_green); <br/> bodyformat. setborder (border. all, borderlinestyle. thin); <br/> // Arial font, 9, non-bold, cell light green, dashboard <br/> font4 = new writablefont (writablefont. arial, 9, writablefont. no_bold, false); <br/> floatformat = new writablecellformat (font4, numberformats. float); <br/> Floatformat. setbackground (colour. light_green); <br/> floatformat. setborder (border. all, borderlinestyle. thin); <br/> // Arial font, 9, non-bold, cell light green, dashboard <br/> font4 = new writablefont (writablefont. arial, 9, writablefont. no_bold, false); <br/> intformat = new writablecellformat (font4, numberformats. integer); <br/> intformat. setbackground (colour. light_green); <br/> intformat. setborder (border. all, borderlin Estyle. thin); </P> <p> init = true; <br/>}</P> <p> Public static void createuserexcelfile (list <user> userlist, file destfile) throws writeexception, ioexception {<br/> If (init = false) Init (); <br/> int index, row; <br/> writablesheet sheet = NULL; <br/> writableworkbook book = NULL; <br/> book = workbook. createworkbook (destfile); <br/> Sheet = book. createsheet ("User table", 0); <br/> Sheet. setcolumnview (0, 15); <br /> Sheet. setcolumnview (1, 15); <br/> Sheet. setcolumnview (2, 15); <br/> Sheet. setcolumnview (3, 40); <br/> // field variable name <br/> Index = 0; <br/> Sheet. addcell (new label (index ++, 0, "ID", titleformat); <br/> Sheet. addcell (new label (index ++, 0, "name", titleformat); <br/> Sheet. addcell (new label (index ++, 0, "sex", titleformat); <br/> Sheet. addcell (new label (index ++, 0, "email", titleformat); <br/> // field name <br/> Index = 0; <Br/> Sheet. addcell (new label (index ++, 1, "ID", titleformat); <br/> Sheet. addcell (new label (index ++, 1, "username", titleformat); <br/> Sheet. addcell (new label (index ++, 1, "gender", titleformat); <br/> Sheet. addcell (new label (index ++, 1, "email", titleformat); <br/> // field comment <br/> Index = 0; <br/> Sheet. addcell (new label (index ++, 2, null, noteformat); <br/> Sheet. addcell (new label (index ++, 2, null, noteformat); <br/> Sheet. Addcell (new label (index ++, 2, "1: Male/N2: female", noteformat); <br/> Sheet. addcell (new label (index ++, 2, null, noteformat); <br/> ROW = 3; <br/> for (User user User: userlist) {<br/> If (user = NULL) continue; <br/> Index = 0; <br/> Sheet. addcell (new number (index ++, row, user. GETID (), bodyformat); <br/> Sheet. addcell (new label (index ++, row, user. getname (), bodyformat); <br/> Sheet. addcell (new number (index ++, row, user. getsex (), Bodyformat); <br/> Sheet. addcell (new label (index ++, row, user. getemail (), bodyformat); <br/> row ++; <br/>}< br/> book. write (); <br/> If (book! = NULL) book. close (); <br/>}</P> <p> Public static list <user> readuserexcelfile (File file) throws ioexception, biffexception {<br/> If (file = NULL) return NULL; <br/> int row, column; <br/> string temp = NULL; <br/> workbook book = NULL; <br/> Sheet sheet = NULL; <br/> List <user> userlist = new arraylist <user> (); <br/> book = workbook. getworkbook (File); <br/> Sheet = book. getsheet (0); <br/> ROW = 3; <br/> whil E (row <sheet. getrows () {<br/> column = 0; <br/> User user = new user (); <br/> // id <br/> temp = sheet. getcell (column ++, row ). getcontents (). trim (); <br/> If (temp! = NULL &&! Temp. equals ("") & temp. matches ("// D +") user. setid (integer. parseint (temp); <br/> else break; <br/> // name <br/> temp = sheet. getcell (column ++, row ). getcontents (). trim (); <br/> If (temp! = NULL &&! Temp. equals ("") user. setname (temp); <br/> // gender <br/> temp = sheet. getcell (column ++, row ). getcontents (). trim (); <br/> If (temp! = NULL &&! Temp. equals ("") & temp. matches ("// D +") user. setsex (integer. parseint (temp); <br/> // email <br/> temp = sheet. getcell (column ++, row ). getcontents (). trim (); <br/> If (temp! = NULL &&! Temp. equals ("") user. setemail (temp); </P> <p> userlist. add (User); <br/> row ++; <br/>}< br/> If (book! = NULL) book. Close (); <br/> return userlist; <br/>}< br/>}

The Excel file to be imported is as follows:

The exported Excel table is as follows: