Use java to import and export Excel data, and use java Excel to import and export data.

Source: Internet
Author: User

Use java to import and export Excel data, and use java Excel to import and export data.

Jxl is used. jar files can be downloaded from the official website. The exported data adopts fake data. The example is very simple.

Model class:

public class Model {private String name;private String score;public Model(String name, String score) {super();this.name = name;this.score = score;}public Model() {super();// TODO Auto-generated constructor stub}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getScore() {return score;}public void setScore(String score) {this.score = score;}@Overridepublic String toString() {return "Model [name=" + name + ", score=" + score + "]";}}

 

Read code:

@ Testpublic void read () throws BiffException, IOException {List <Model> list = new ArrayList <Model> (); Workbook book = Workbook. getWorkbook (new File ("test.xls"); int columns = 0; int rows = 0; String name = ""; String score = ""; Sheet [] sheet = book. getSheets (); Model m = null; for (int I = 0; I <sheet. length; I ++) {rows = sheet [I]. getRows (); // The number of data records in the System. out. print ("no." + (I + 1) + "page data:" + rows); columns = sheet [I]. getColumns (); // The number of fields System. out. println ("\ t No." + (I + 1) + "page field:" + columns); for (int j = 0; j <rows; j ++) {m = new Model (); for (int c = 0; c <columns; c ++) {Cell cell = sheet [I]. getCell (c, j); if (c = 1) {name = cell. getContents (); m. setName (name);} else {score = cell. getContents (); m. setScore (score) ;}} list. add (m) ;}for (Model m1: list) {System. out. println (m1.toString ());}}


 

Export Code:

@ Testpublic void write () throws IOException, RowsExceededException, WriteException {List <Model> list = new ArrayList <Model> (); Model m1 = new Model ("test1 ", "22"); Model m2 = new Model ("test2", "33"); Model m3 = new Model ("test3", "44 "); model m4 = new Model ("test4", "55"); Model m5 = new Model ("test5", "66"); list. add (m1); list. add (m2); list. add (m3); list. add (m4); list. add (m5); WritableWorkbook book = Workbook. createWorkbook (new File ("test4.xls"); // create an Excel File WritableSheet sheet = book. createSheet ("Sheet", 0); // create a Sheet Label label = null; int s = 2; // control the number of outgoing columns for (int I = 0; I <list. size (); I ++) {for (int j = 0; j <s; j ++) {if (j = 0) {label = new Label (j, i, list. get (I ). getName (); sheet. addCell (label);} else {label = new Label (j, I, list. get (I ). getScore (); sheet. addCell (label) ;}} book. write (); book. close ();}



How can I use java to import Excel data to a database?

Public static void main (String args []) throws BiffException, IOException, WriteException {
// 1 read the data table from the Excel file

// Java Excel apiyou can read the Excel data table from an input stream either from a file (.xls) of the internal file system.
// The first step to read an Excel data table is to create a Workbook. The following code snippet illustrates how to perform this operation:
// (For the complete code, see ExcelReading. java)

Try
{
// Construct a Workbook object, read-only Workbook object
// Create a Workbook directly from a local file
// Create a Workbook from the input stream
InputStream is = new FileInputStream ("D:/user.xls ");
Jxl. Workbook rwb = Workbook. getWorkbook (is );

// Once a Workbook is created, we can use it to access the Excel Sheet (term: Worksheet ). Refer to the following code snippet:

// Obtain the first Sheet
Sheet rs = (Sheet) rwb. getSheet (0 );
// We can access it either by the Sheet name or by subscript. If it is accessed by subscript,
// Note that the subscript starts from 0, just like an array.

// Once the Sheet is obtained, we can use it to access the Excel Cell (term: Cell ). Refer to the following code snippet:

// Obtain the value of the first row and column
Cell c00 = (jxl. Sheet) rs). getCell (0, 0 );
String strc00 = c00.getContents ();

// Obtain the value of the first and second columns
Cell c10 = (jxl. Sheet) rs). getCell (1, 0 );
String strc10 = c10.getContents ();

// Obtain the value of the second row and the second column
Cell c11 = (jxl. Sheet) rs). getCell (1, 1 );
String strc11 = c11.getContents ();

System. out. println ("Cell (0, 0)" + "value:" + strc00 + "; type:" + c00.getType ());
System. out. println ("Cell (1, 0)" + "value:" + strc10 + "; type:" + c10.getType ());
System. out. println ("Cell (1, 1)" + "value:" + strc11 + "; type:" + c11.getType ());

// If only the Cell value is obtained, we can easily use getConten ...... the remaining full text>

How to import excel data in a java program

Import java. io. BufferedInputStream;
Import java. io. File;
Import java. io. FileInputStream;
Import java. io. FileNotFoundException;
Import java. io. IOException;
Import java. text. DecimalFormat;
Import java. text. SimpleDateFormat;
Import java. util. ArrayList;
Import java. util. Arrays;
Import java. util. Date;
Import java. util. List;

Import org. apache. poi. hssf. usermodel. HSSFCell;
Import org. apache. poi. hssf. usermodel. HSSFDateUtil;
Import org. apache. poi. hssf. usermodel. HSSFRow;
Import org. apache. poi. hssf. usermodel. HSSFSheet;
Import org. apache. poi. hssf. usermodel. HSSFWorkbook;
Import org. apache. poi. poifs. filesystem. POIFSFileSystem;

Public class ReadExcel2 {

/**
* @ Param args
* @ Throws IOException
* @ Throws FileNotFoundException
*/
Public static void main (String [] args) throws FileNotFoundException, IOException {
File file = new File ("src/test.xls ");

String [] [] data = getData (file, 0 );
PrintStringArray (data );

}

Public static void printStringArray (String [] [] data ){
For (int I = 0; I <data. length; I ++ ){
For (int j = 0; j <data [I]. length; j ++ ){
System. out. print (data [I] [j] + "\ t ");
}
System. out. print ("\ n ");
}
}

/**
*
* When reading the Excel content, the first-dimensional array stores the value of Ge in a row and the number of rows stored in the two-dimensional array.
* @ Param file: Source Excel for reading data
* @ Param ignoreRows: the number of rows that are ignored when reading data. This is equivalent to 1 in the line header.
* @ Return refers to the data in the Excel file read.
* @ Throws FileNotFoundException
* @ Throws IOException
*/

Public static String [] [] getData (File file, int ignoreRows)

Throws... the remaining full text>

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.