Java import key records for exporting Excel data

The client's B/S architecture output mode

Underline this part of the code is the B/s mode used in the output mode,

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");

1. Import the Jxl.jar jar package

2. Key points in exporting data

Package com.wch.test;

Import Java.io.File;
Import java.util.List;

Import com.wch.entity.StuEntity;
Import Com.wch.service.StuService;

Import JXL. Workbook;
Import jxl.format.Alignment;
Import Jxl.format.Colour;
Import Jxl.format.UnderlineStyle;
Import jxl.format.VerticalAlignment;
Import Jxl.write.Label;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableFont;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;

/**
* Databases in MySQL are imported into Excel
*
* @author Chen
*
*/
public class Testdbtoexcel {

/**
* @param args
*/
public static void Main (string[] args) {

try {
Writableworkbook WWB = null;
To create a locally writable folder
String fileName = "D://booktest.xls";
File File = new file (fileName);
if (!file.exists ()) {
File.createnewfile ();
}
Create a workbook with filename
WWB = Workbook.createworkbook (file);
Create a worksheet
Writablesheet ws = Wwb.createsheet ("Test from data into Excel", 0);
Ws.mergecells (0, 0, 3, 0);
Create a Writablefont Font object, which in turn represents boldface, font size 18, bold, non-italic, not underlined, bright blue

Writablefont Titlefont = new Writablefont (
Writablefont.createfont ("Blackbody"), Writablefont.bold,
False, Underlinestyle.no_underline, Colour.light_blue);

Creates a Writablecellformat object, applies the object to the cell, and then sets the style of the cell

Writablecellformat TitleFormat = new Writablecellformat (titlefont);//Set Caption
Set Text Horizontal center alignment
Titleformat.setalignment (Alignment.centre);
Set Text Vertical Center alignment
Titleformat.setverticalalignment (Verticalalignment.centre);
Set up line wrapping
Titleformat.setwrap (TRUE);
Add a Label object, which in turn represents the first column, the first row, the content, the format used
Label lab_00 = new label (0, 0, "Employee salary statement", TitleFormat);
Add a defined Label object to the worksheet so that the first column of the worksheet contains the ' Learner test scores list ' and applies the TitleFormat defined style
Ws.addcell (lab_00);
Querying the database for all data
list<stuentity> list = Stuservice.getallbydb ();
To write the first row of headings
Label Labelid = new label (0, 1, "number");
Label labelname = new Label (1, 1, "name");
Label Labelsex = new Label (2, 1, "gender");
Label Labelnum = new Label (3, 1, "new Water");
Ws.addcell (Labelid);
Ws.addcell (LabelName);
Ws.addcell (Labelsex);
Ws.addcell (Labelnum);
Write the time of the database query into Excel
for (int i = 0; i < list.size (); i++) {
Label labelid_i = new label (0, i + 2, list.get (i). GetId () + "");
Label labelname_i = new label (1, i + 2, list.get (i). GetName ());
Label labelsex_i = new label (2, i + 2, list.get (i). Getsex ());
Label labelnum_i = new label (3, i + 2, list.get (i). Getnum ()
+ "");
Ws.addcell (labelid_i);
Ws.addcell (labelname_i);
Ws.addcell (labelsex_i);
Ws.addcell (labelnum_i);
}
Write the document inside
Wwb.write ();
Close a document workbook
Wwb.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}

}

2. Import the Excel data into the database.

Package com.wch.test;

Import java.util.List;

Import com.wch.entity.StuEntity;
Import Com.wch.service.StuService;
Import Com.wch.util.DBhepler;

public class Testexceltodb {

/**
* @param args
*/
public static void Main (string[] args) {
Get all the data in the table
list<stuentity> Listexcel = Stuservice.getallbyexcel ("D://booktest.xls");

Dbhepler db=new Dbhepler ();

for (stuentity Stuentity:listexcel) {
int Id=stuentity.getid ();
if (! Stuservice.isexist (ID)) {
Add if not present
String sql= "INSERT into Stu (Name,sex,num) VALUES (?,?,?)";
String[] Str=new string[]{stuentity.getname (), Stuentity.getsex (), stuentity.getnum () + ""};
Db. Addu (SQL, str);
}else {
exists on update
String sql= "Update stu set name=?,sex=?,num=? where id=? ";
String[] Str=new string[]{stuentity.getname (), Stuentity.getsex (), stuentity.getnum () + "", id+ ""};
Db. Addu (SQL, str);
}
}

}

}

/* Method of a call

/**
* Query all the data in the spreadsheet in the specified directory
* @param file Full path
* @return
*/
public static list<stuentity> Getallbyexcel (String file) {
List<stuentity> list=new arraylist<stuentity> ();
try {
Workbook rwb=workbook.getworkbook (file);
Get Create Worksheet
Sheet rs=rwb.getsheet ("Test from data into Excel");//or Rwb.getsheet (0)
int clos=rs.getcolumns ();//Get all the columns
int rows=rs.getrows ();//Get all the rows

System.out.println (clos+ "Rows:" +rows);
for (int i = 2; i < rows; i++) {
for (int j = 0; J < Clos; J + +) {
The first one is the number of columns, the second is the number of rows
String Id=rs.getcell (j + +, I). getcontents ();//The default leftmost number is also counted as a column so here's J + +
String Name=rs.getcell (j + +, I). getcontents ();
String Sex=rs.getcell (j + +, I). getcontents ();
String Num=rs.getcell (j + +, I). getcontents ();

SYSTEM.OUT.PRINTLN ("ID:" +id+ "Name:" +name+ "Sex:" +sex+ "num:" +num ");
List.add (New stuentity (Integer.parseint (ID), name, sex, Integer.parseint (num)));
}
}
} catch (Exception e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
return list;

}

*/

Java import key records for exporting Excel data