JXL操作excel总结（2）

2.       编写操作excel的程序：

package org.mingyuan;

import java.io.File;

import java.io.IOException;

import java.sql.*;

import java.util.Iterator;

import java.util.List;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

/**

* 本程序用来读取excel文件中的数据，将之处理后写入新的excel文件中

*

* @author mingyuan

*

*/

public class ReadAndWriteExcel {

       public void readExcel() throws Exception {

              Workbook wb = null;

              // 构造Workbook（工作薄）对象

              // File file = new File("G:/080628-quan-ptz01-raw data export/q.xls");

              Class.forName("com.mysql.jdbc.Driver");

              Connection conn = DriverManager

                            .getConnection("jdbc:mysql://localhost:3306/test?user=root&password=mingyuan_1986");

              PreparedStatement ps = conn

                            .prepareStatement("insert into excel(time,location,data)values(?,?,?)");

              File dir = new File("G:/a/");

              int rows = 1;// 行数统计器

              if (dir.isDirectory()) {

                     File[] files = dir.listFiles();

                     for (int j = 0; j < files.length; j++) {

                            if (files[j].getName().endsWith("xls")) {

                                   wb = Workbook.getWorkbook(files[j]);

                                   // 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了

                                   Sheet sheet = wb.getSheet(0);

                                   int count = sheet.getRows();

                                   double time = 0;

                                   String location = null;

                                   int data = 0;

                                   Cell cell = null;

                                   boolean isnull = false;

                                   for (int i = 1; i < count; i++) {

                                          int flag = 0;

                                          cell = sheet.getCell(1, i);

                                          if (cell.getContents().equals("")) {

                                                 isnull = true;

                                                 flag = 1;

                                          } else {

                                                 time = Double.valueOf(cell.getContents());

                                                 // System.out.print("time:" + cell.getContents());

                                          }

                                          cell = sheet.getCell(4, i);

                                          if (cell.getContents().equals("")) {

                                                 isnull = true;

                                                 flag = 1;

                                          } else {

                                                 location = new String(cell.getContents().getBytes(

                                                               "utf8"));

                                                 // /System.out.println();

                                                 // System.out.print(" location:" +

                                                 // cell.getContents());

                                          }

                                          cell = sheet.getCell(5, i);

                                          if (cell.getContents().equals("")) {

                                                 isnull = true;

                                                 flag = 1;

                                          } else {

                                                 data = Integer.valueOf(cell.getContents());

                                                 // System.out.println(" data:" +

                                                 // cell.getContents());

                                          }

                                          // System.out.println("----------"+isnull);

                                          if (flag == 0) {

                                                 try {

                                                        ps.setDouble(1, time);

                                                        ps.setString(2, location);

                                                        ps.setInt(3, data);

                                                        ps.execute();

                                                        System.out.println(rows++);

                                                 } catch (SQLException e) {

                                                        e.printStackTrace();

                                                        e.getLocalizedMessage();

                                                 } finally {

                                                 }

                                          } else {

                                                 System.out.println("--------");

                                          }

                                   }

                                   wb.close();

                                   System.out.println("OK");

                            }

                     }

              }

              try {

                     ps.close();

                     conn.close();

              } catch (SQLException e) {

                     e.getLocalizedMessage();

              }

       }

//程序未完，见（3）