读取和生成excel实例

package org.tarena.dang.zuobiao;


import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


import org.tarena.dang.dao.impl.DaoImpl;


public class CreateDataPart {


public static void main(String[] args) throws Exception {
String path = "E:\\景点坐标.xls";
getTxtData(path);

}


private static void getTxtData(String path) throws Exception {
File file = getNewFileForId(path);
//在数据库中更新企业的径纬度坐标
changeZuoBiao(file);
File ifile = new File("E:\\result.txt");
if(ifile.exists())
{
ifile.delete();
}
else
{
ifile.createNewFile();
}
PrintWriter pw = new PrintWriter(ifile,"utf-8");
InputStream is = new FileInputStream(file);
Workbook wb = Workbook.getWorkbook(is);
Sheet[] shs = wb.getSheets();
Sheet sh = shs[0];
for(int i=0;i<sh.getRows();i++)
{
Cell cell = sh.getCell(0,i);
String str = cell.getContents();
if (str!=null) {
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
   Matcher m = p.matcher(str);
str = m.replaceAll("");
}
if(str==null)
{
str="";
}
if(!str.equals(""))
{
String xml = "<panourl id='"+str+"' url='http://10.194.4.3:6005/PanoService/'></panourl>";
pw.println(xml);
}
}
pw.close();
}
//获取企业数据，得到企业的ID
private static File getNewFileForId(String path) throws Exception {
List<Enterprise> alist = readExcel(path);
File file = new File("E:\\企业.xls");
WritableWorkbook book = Workbook.createWorkbook(file);
//创建第一页，名为企业径纬度
WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
WritableSheet sheet = book.createSheet("企业径纬度",0);
Label label1 = new Label(0,0,"企业ID");
Label label2 = new Label(1,0,"企业名称");
Label label3 = new Label(2,0,"企业径度");
Label label4 = new Label(3,0,"企业纬度");
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);

NumberFormat nf = new jxl.write.NumberFormat("#.######"); //设定带小数点数字格式
WritableCellFormat wcfN = new jxl.write.WritableCellFormat(wfc,nf);//设定带小数数字单元格格式，wcf见上

for(int i=0;i<alist.size();i++)
{
Enterprise en = alist.get(i);
String sql = "select id " +
" from t_bas_enterprise where entername='"+en.getName()+"' " +
"or entername like '%"+en.getName()+"%'";
System.out.println(sql);
DaoImpl di = new DaoImpl();
List<Integer> list = di.getEnterId(sql);
System.out.println(list);
System.out.println("====================");
Label la1 = new Label(0,i+1,list.get(0)+"");
Label la2 = new Label(1,i+1,en.getName());

jxl.write.Number number2 = new jxl.write.Number(2,i+1,Double.parseDouble(en.getLo()),wcfN); //将数字3.14159 放到单元格D5
jxl.write.Number number3 = new jxl.write.Number(3,i+1,Double.parseDouble(en.getLa()),wcfN); //将数字3.14159 放到单元格D5
// Label la3 = new Label(2,i+1,en.getLo());
// Label la4 = new Label(3,i+1,en.getLa());
sheet.addCell(la1);
sheet.addCell(la2);
sheet.addCell(number2);
sheet.addCell(number3);
//<panourl id="1146" url="http://10.194.4.3:6005/PanoService/"></panourl>
}
book.write();
book.close();
return file;
}
public static List<Enterprise> readExcel(String filePath) throws Exception
{
InputStream is = new FileInputStream(filePath);
Workbook wrb = Workbook.getWorkbook(is);
DaoImpl di = new DaoImpl();
ArrayList<Enterprise> list = new ArrayList<Enterprise>();
Sheet[] sts = wrb.getSheets();//得到文件所有页的名字
//for(int i=0;i<sts.length;i++)
//{
for(int j=1;j<sts[0].getRows();j++)
{
Enterprise en = new Enterprise();
String lo = "";
String la = "";
String name = "";
for(int k=0;k<sts[0].getColumns();k++)
{
Cell coo = sts[0].getCell(k, j);
String str = coo.getContents().trim();
if (str!=null) {
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
   Matcher m = p.matcher(str);
str = m.replaceAll("");
}
if(!str.equals("")&&str!=null)
{
if(k==0)
{
lo = str;
}
if(k==1)
{
la = str;
}
if(k==2)
{
name = str;
}
}
en.setLa(la);
en.setLo(lo);
en.setName(name);
list.add(en);
}
}
return list;
}


private static void changeZuoBiao(File file) throws Exception {
DaoImpl di = new DaoImpl();
InputStream is = new FileInputStream(file);
Workbook wrb = Workbook.getWorkbook(is);
Sheet[] sts = wrb.getSheets();//得到文件所有页的名字
//for(int i=0;i<sts.length;i++)
//{
int i = 0;
String tableName = sts[i].getName().trim();
for(int j=1;j<sts[i].getRows();j++)
{
int id=0;
String lo = "";
String la = "";
for(int k=0;k<sts[i].getColumns();k++)
{
Cell coo = sts[i].getCell(k, j);
String str = coo.getContents().trim();
if (str!=null) {
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
   Matcher m = p.matcher(str);
str = m.replaceAll("");
}
if(str==null)
{
str="";
}

if(k==0&&str!=null&&!str.equals(""))
{
id=Integer.parseInt(str);
}
if(k==2&&str!=null&&!str.equals(""))
{
lo=str;
}
if(k==3&&str!=null&&!str.equals(""))
{
la=str;
}

}
String sql = "update T_Bas_enterprise set LONGITUDE='"+lo+"' " +
", LATITUDE='"+la+"'  , isalarm=1 where id="+id;
System.out.println(sql);
//di.insertData(sql);
}
}
}
class Enterprise{
private String name;
private String lo;
private String la;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLo() {
return lo;
}
public void setLo(String lo) {
this.lo = lo;
}
public String getLa() {
return la;
}
public void setLa(String la) {
this.la = la;
}
}