Package export.excel;
Import Java.io.BufferedInputStream;
Import Java.io.BufferedOutputStream;
Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.io.FileOutputStream;
Import Java.io.OutputStream;
Import java.util.ArrayList;
Import java.util.List;
Import Java.util.Random;
Import JXL. Cell;
Import JXL. Sheet;
Import JXL. workbook;
Import jxl.format.Alignment;
Import jxl.format.VerticalAlignment;
Import Jxl.write.Label;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
Import Export.vo.PersonVo;
/** Export Excel File
*
* @author Passerby
*
*/
public class Exportexcel {
Public list<personvo> personvolist;
Public Exportexcel ()
{
InitData ();
}
/**
* Initialization of data
*/
public void InitData ()
{
Random function
Random Random = new Random ();
Personvolist = new arraylist<personvo> ();
Initializing 100 people into a collection
Personvo personvo = null;
for (int i = 0; i < i++)
{
Personvo = new Personvo ();
Personvo.setid ("" + i);
Personvo.setname ("Passerby" + i);
if (i%2==0)
{
Personvo.setsex ("male");
}
Else
{
Personvo.setsex ("female");
}
Take a random age within 100 years
Personvo.setage ("" + random.nextint (100));
Personvolist.add (PERSONVO);
}
}
/** Export data in Excel format
* Exportname Export File name
* @param exportname
*/
public void Exportperson (String exportname)
{
try{
File Excelfile = new file (exportname + "Temp.xls");
File format
Writablecellformat format = new Writablecellformat ();
X on the left.
Format.setalignment (Alignment.right);
Y on top
Format.setverticalalignment (Verticalalignment.top);
Create a working file
Writableworkbook Writableworkbook = Workbook.createworkbook (excelfile);
Create a workbook
Writablesheet Sheet1 = writableworkbook.createsheet ("Personal Information list", 0);
Create a row number set the width of a row
Sheet1.setcolumnview (0, 10);
Sheet1.setcolumnview (1, 10);
Sheet1.setcolumnview (2, 10);
Sheet1.setcolumnview (3, 10);
Set the value of a row
Sheet1.addcell (New Label (0, 0, "ID", format));
Sheet1.addcell (New Label (1, 0, "name", format));
Sheet1.addcell (New Label (2, 0, "sex", format));
Sheet1.addcell (New Label (3, 0, "age", format));
Write the collection to Excel
int rownum = 1;
for (Personvo bean:personvolist)
{
int colspannum = 0;
Sheet1.addcell (New Label (Colspannum, RowNum, Bean.getid (), format));
Sheet1.addcell (New Label (colspannum++, RowNum, Bean.getname (), format));
Sheet1.addcell (New Label (colspannum++, RowNum, Bean.getsex (), format));
Sheet1.addcell (New Label (colspannum++, RowNum, Bean.getage (), format));
rownum++;
}
Writableworkbook.write ();
Writableworkbook.close ();
If you're downloading it on the Web, write this.
Super.gethttpservletresponse (). setContentType ("Application/x-msdownload");
String encodetittle = new String (Excelname.getbytes ("GBK"), "iso-8859-1");
Super.gethttpservletresponse (). AddHeader ("Content-disposition", "Attachment;filename=" +encodetittle+ ". xls");
FileInputStream finput = new FileInputStream (excelfile);
OutputStream output = Super.gethttpservletresponse (). Getoutputstream ();
File Fout = new file (Exportname + ". xls");
OutputStream output = new FileOutputStream (fout);
Bufferedinputstream Buffin = new Bufferedinputstream (finput);
Bufferedoutputstream buffout = new Bufferedoutputstream (output);
byte[] buffer = new byte[4096];
int count = 0;
while ((count = buffin.read (buffer, 0, buffer.length)) > 0) {
Buffout.write (buffer, 0, count);
}
Buffin.close ();
Buffout.close ();
Finput.close ();
Output.close ();
Excelfile.delete ();
}
catch (Exception e) {
E.printstacktrace ();
}
Finally
{
SYSTEM.OUT.PRINTLN ("Complete export operation");
}
}
/** import data in Excel format
*
* @param importexcel Import Excel file name
*/
public void Importperson (String importexcel)
{
try{
File Excelfile = new file (importexcel+ ". xls");
Create a working file
Workbook workbook = Workbook.getworkbook (Excelfile);
Get the first workbook there are two ways to get the sheet table, 1 is the name, and the subscript, starting from 0
Sheet Sheet = workbook.getsheet (0);
Total number of records
int allrow = Sheet.getrows ();
int allcolspan = Sheet.getcolumns ();
System.out.println (Allrow);
System.out.println (Allcolspan);
Fetch data
for (int i=0; i<allrow; i++)
{
Remove the value of each column
for (int j=0; j<allcolspan; j + +)
{
Sheet.getcell (Number of columns, number of lines);
Cell cell = Sheet.getcell (j, I);
Print out the value of the column
System.out.print (cell.getcontents () + "T");
}
System.out.println ();
}
Shut down
Workbook.close ();
}catch (Exception e) {
E.printstacktrace ();
}
Finally
{
SYSTEM.OUT.PRINTLN ("Complete import operation");
}
}
/**
* @param args
*/
public static void Main (string[] args) {
Exportexcel exportexcel = new Exportexcel ();
Set path
String Srcpath = "C:/quarantine/personvo";
Export
Exportexcel.exportperson (Srcpath);
Read
Exportexcel.importperson (Srcpath);
}
}