关于 jsp servlet 将Excel表格内容写进数据库,将数据库导出到Excel中。(一)

转自:http://blog.chinaunix.net/uid-28794959-id-3773177.html

EXCEL到数据库,引入poi.jar
jsp如下

点击(此处)折叠或打开

  1. <form enctype="multipart/form-data" name=testform method=post action=Testaction>
  2.   <table>
  3.     <tr>
  4.        <td><font size=2>批量上传:</font><input type="file" name="test" size="10"><br></td>
  5.       <td><input type="submit" name="批量上传" size="10"value="批量上传"><br></td></tr></table><br>
  6.       </form>
Servlet如下

点击(此处)折叠或打开

  1. package control;

  2. import java.io.File;
  3. import java.io.FileOutputStream;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.io.PrintWriter;
  7. import java.io.RandomAccessFile;

  8. import javax.servlet.ServletException;
  9. import javax.servlet.http.HttpServlet;
  10. import javax.servlet.http.HttpServletRequest;
  11. import javax.servlet.http.HttpServletResponse;

  12. public class Testaction extends HttpServlet {

  13.     /**
  14.      *
  15.      */
  16.     private static final long serialVersionUID = 1L;

  17.     public void doGet(HttpServletRequest request, HttpServletResponse response)
  18.             throws ServletException, IOException {


  19.     }

  20.     /**
  21.      * The doPost method of the servlet. <br>
  22.      *
  23.      * This method is called when a form has its tag value method equals to post.
  24.      *
  25.      * @param request the request send by the client to the server
  26.      * @param response the response send by the server to the client
  27.      * @throws ServletException if an error occurred
  28.      * @throws IOException if an error occurred
  29.      */
  30.     public void doPost(HttpServletRequest request, HttpServletResponse response)
  31.     throws ServletException, IOException {
  32.         //接收上传文件内容中临时文件的文件名
  33.         String tempFileName = new String("tempFileName");
  34.         //tempfile 对象指向临时文件
  35.         File tempFile = new File("D:/"+tempFileName);
  36.         //outputfile 文件输出流指向这个临时文件
  37.         FileOutputStream outputStream = new FileOutputStream(tempFile);
  38.         //得到客服端提交的所有数据
  39.         InputStream fileSourcel = request.getInputStream();
  40.         //将得到的客服端数据写入临时文件
  41.         byte b[] = new byte[1000];
  42.         int n ;
  43.         while ((n=fileSourcel.read(b))!=-1){
  44.          outputStream.write(b,0,n);
  45.         }
  46.         
  47.         //关闭输出流和输入流
  48.         outputStream.close();
  49.         fileSourcel.close();
  50.         
  51.         //randomFile对象指向临时文件
  52.         RandomAccessFile randomFile = new RandomAccessFile(tempFile,"r");
  53.         //读取临时文件的第一行数据
  54.         randomFile.readLine();
  55.         //读取临时文件的第二行数据,这行数据中包含了文件的路径和文件名
  56.         String filePath = randomFile.readLine();
  57.         System.out.println(filePath);
  58.         //得到文件名
  59.         int position = filePath.lastIndexOf('\\');
  60.         CodeToString codeToString = new CodeToString();
  61.         String filename = codeToString.codeString(filePath.substring(position,filePath.length()-1));
  62.         //重新定位读取文件指针到文件头
  63.         randomFile.seek(0);
  64.         //得到第四行回车符的位置,这是上传文件数据的开始位置
  65.         long forthEnterPosition = 0;
  66.         int forth = 1;
  67.         while((n=randomFile.readByte())!=-1&&(forth<=4)){
  68.          if(n=='\n'){
  69.          forthEnterPosition = randomFile.getFilePointer();
  70.          forth++;
  71.          }
  72.         }
  73.         
  74.             //生成上传文件的目录
  75.         File fileupLoad = new File("F:/MyEclipse/Manager/WebRoot/file","upLoad");
  76.         fileupLoad.mkdir();
  77.         //saveFile 对象指向要保存的文件
  78.         File saveFile = new File("F:/MyEclipse/Manager/WebRoot/file/upLoad",filename);
  79.         RandomAccessFile randomAccessFile = new RandomAccessFile(saveFile,"rw");
  80.         //找到上传文件数据的结束位置,即倒数第四行
  81.         randomFile.seek(randomFile.length());
  82.         long endPosition = randomFile.getFilePointer();
  83.         int j = 1;
  84.         while((endPosition>=0)&&(j<=4)){
  85.          endPosition--;
  86.          randomFile.seek(endPosition);
  87.          if(randomFile.readByte()=='\n'){
  88.          j++;
  89.          }
  90.         }
  91.         
  92.         //从上传文件数据的开始位置到结束位置,把数据写入到要保存的文件中
  93.         randomFile.seek(forthEnterPosition);
  94.         long startPoint = randomFile.getFilePointer();
  95.         while(startPoint<endPosition){
  96.          randomAccessFile.write(randomFile.readByte());
  97.          startPoint = randomFile.getFilePointer();
  98.         }
  99.         randomAccessFile.close();
  100.         randomFile.close();
  101.         tempFile.delete();
  102.         
  103.         TestExcel t=new TestExcel();
  104.         t.add();
  105.         
  106.         
  107.     }
  108.  



  109. }
真正的核心代码,分析EXCEL

点击(此处)折叠或打开

  1. package control;

  2. import java.io.FileInputStream;
  3. import java.io.FileNotFoundException;
  4. import java.io.IOException;
  5. import java.io.PrintWriter;

  6. import org.apache.commons.logging.Log;
  7. import org.apache.commons.logging.LogFactory;
  8. import org.apache.poi.hssf.usermodel.HSSFCell;
  9. import org.apache.poi.hssf.usermodel.HSSFRow;
  10. import org.apache.poi.hssf.usermodel.HSSFSheet;
  11. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  12.  import db.DB;
  13. import db.Test_table;
  14.  public class TestExcel {
  15.        //记录类的输出信息?
  16.        static Log log = LogFactory.getLog(TestExcel.class);
  17.        //获取Excel文档的路径?
  18.        public static String filePath = "F://MyEclipse//Manager//WebRoot//file//upLoad//test.xls";
  19.        public void add() {
  20.              try {
  21.                    // 创建对Excel工作簿文件的引用?
  22.                    HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
  23.                   // 在Excel文档中,第一张工作表的缺省索引是0
  24.                   // 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);?
  25.                    HSSFSheet sheet = wookbook.getSheet("Sheet1");
  26.                    //获取到Excel文件中的所有行数
  27.                    int rows = sheet.getPhysicalNumberOfRows();
  28.                    //遍历行
  29.                    for (int i = 0; i < rows; i++) {
  30.                          // 读取左上端单元格?
  31.                          HSSFRow row = sheet.getRow(i);
  32.                          // 行不为空
  33.                          if (row != null) {
  34.                                //获取到Excel文件中的所有的列?
  35.                                int cells = row.getPhysicalNumberOfCells();
  36.                               String value = "";
  37.                               //遍历列?
  38.                                for (int j = 0; j < cells; j++) {
  39.                                      //获取到列的值?
  40.                                      HSSFCell cell = row.getCell(j);
  41.                                      if (cell != null) {
  42.                                            switch (cell.getCellType()) {
  43.                                                  case HSSFCell.CELL_TYPE_FORMULA:
  44.                                                  break;
  45.                                                  case HSSFCell.CELL_TYPE_NUMERIC:
  46.                                                        value += cell.getNumericCellValue() + ",";
  47.                                                  break;
  48.                                                  case HSSFCell.CELL_TYPE_STRING:
  49.                                                        value += cell.getStringCellValue() + ",";
  50.                                                  break;
  51.                                                  default:
  52.                                                        value += "0";
  53.                                                 break;
  54.                                      }
  55.                               }
  56.                          }
  57.                         // 将数据插入到sqlserver数据库中
  58.                          String[] val = value.split(",");
  59.                          DB db=new DB();
  60.                          Test_table jBean=new Test_table();
  61.                          String sql ="insert into test_table(num1,num2,num3) values('"+val[0]+"','"+val[1]+"','"+val[2]+"')";
  62.                         
  63.                          int count=db.getInserttest(sql, jBean);
  64.                          System.out.println("------------------"+sql);
  65.                          if(count>0){
  66.                              //关闭文件输入、输出
  67.                          
  68.                             
  69.                    }
  70.                          }}
  71.        } catch (FileNotFoundException e) {
  72.              e.printStackTrace();
  73.        } catch (IOException e) {
  74.              e.printStackTrace();
  75.       }
  76.     }
  77.  }
DB数据库连接类

点击(此处)折叠或打开

  1. public class DB {
  2.     
  3.     private static DB db = null;
  4.     private static Connection conn = null;
  5.     private static PreparedStatement pstmt = null;
  6.     private static Statement stmt = null;
  7.      //单例模式,new DB类的时候,创建唯一对象,只初始化一次,
  8.      //注意:不要显式闭此static中的Connection和Statement对象,否则抛空指针异常
  9.      static{
  10.      try {
  11.           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  12.           conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=Market" ,"sa","a");
  13.           stmt = conn.createStatement();
  14.      System.out.println("--------初始化---------");
  15.      } catch (ClassNotFoundException e) {
  16.      System.out.println("---------- 加载数据库驱动类时发生异常: ----------");
  17.      e.printStackTrace();
  18.      } catch (SQLException e) {
  19.      System.out.println("------------ getConnection()方法发生异常--------------");
  20.      }
  21.      }
  22.      public PreparedStatement prepareStmt(String sql){
  23.          PreparedStatement pstmt = null;
  24.          try {
  25.          pstmt = conn.prepareStatement(sql);
  26.          } catch (SQLException e) {
  27.          System.out.println("-------------prepareStmt()方法发生异常-------------------");
  28.          e.printStackTrace();
  29.          }
  30.          return pstmt;
  31.          }
  32.      //执行查询所有记录操作
  33.      public ResultSet exeQuery(String sql){
  34.      ResultSet rs = null;
  35.      try {
  36.           rs = stmt.executeQuery(sql);
  37.      } catch (SQLException e) {
  38.      System.out.println("------------exeQuery()方法发生异常: --------------------");
  39.      e.printStackTrace();
  40.      }
  41.      return rs;
  42.      }
  43.      public void exeUpdate(String sql){
  44.           try{
  45.           stmt.executeUpdate(sql);
  46.           } catch(SQLException e){
  47.           System.out.println("------------- exeUpdate()方法发生异常------------------");
  48.           e.printStackTrace();
  49.           }
  50.           }
  51.      //关闭PreparedStatement对象
  52.      public void closePstmt(PreparedStatement pstmt){
  53.      try{
  54.      pstmt.close();
  55.      pstmt = null;
  56.      }catch(SQLException e){
  57.      System.out.println("-------------------- DB.closePstmt()方法发生异常 -------------------------");
  58.      e.printStackTrace();
  59.      }
  60.      }
  61.      //关闭ResultSet对象
  62.      public void closeRs(ResultSet rs){
  63.      try{
  64.           if(rs!=null)
  65.      rs.close();
  66.      // rs = null;
  67.      }catch(SQLException e){
  68.      System.out.println("-------------------- DB.closePstmt()方法发生异常 -------------------------");
  69.      e.printStackTrace();
  70.      }
  71.      }
  72.      public Connection getDB()
  73.      {
  74.      return conn ;
  75.      }
数据库  表名为 test_table  字段名称为num1, num2,num3。


评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值