- 论坛徽章:
- 0
|
excel,一个电子表格。如今用户不在满足于网页上显示表格的形式,一些数据(例如财务方面)需要直接保存为excel表格。华表虽然好但是价格在那,
再说了,一个有追求的程序员是不可能没有自己的理想的,所以我选择了poi。一个开源的项目,但是我还 不算一个优秀的程序员
- package org.yzq;
- import java.util.LinkedList;
- public class ExcelBean
- {
- private LinkedList listData = new LinkedList();
-
- public void setListData(LinkedList listData)
- {
- this.listData = listData;
- }
-
- public void setListData(String[] strListData)
- {
- this.listData.add(strListData);
- }
-
- public LinkedList getListData()
- {
- return listData;
- }
- }
复制代码- package org.yzq;
- import org.apache.poi.hssf.usermodel.*;
- import java.io.*;
- import java.util.*;
- import java.sql.*;
- public class ExcelCreate
- {
- private ResultSet rs = null;
- private Connection con = null;
- private Statement stmt = null;
- FileOutputStream file = null;
- private String excelFile = "d:\poi\friends.xls";
-
- private void createExcel()
- {
- try
- {
- ExcelBean eb = new ExcelBean();
- Class.forName("org.gjt.mm.mysql.Driver").newInstance();
- con =
- DriverManager.getConnection("jdbc:mysql://127.0.0.1/demo","yzq","8383055");
- stmt = con.createStatement();
- rs =
- stmt.executeQuery("select id,name,sex,age,mobile,address,memo from
- friends");
- while(rs.next())
- {
-
- eb.setListData(new
- String[]{(rs.getInt("id")+"").toString().trim(),rs.getString("name").toString().trim(),rs.getString("sex").toString().trim(),(rs.getInt("age")+"").toString().trim(),rs.getString("mobile").toString().trim(),rs.getString("address").toString().trim(),rs.getString("memo").toString().trim()});
- }
- String[] titleKeys = new String[]{"编号","姓名","性别","年龄","手机","地址","备注"};
- LinkedList listData = eb.getListData();
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("sheet0");
- //sheet.setDefaultColumnWidth((short)100);
- HSSFRow row = sheet.createRow((short)0);
- for(int i=0;i<titleKeys.length;i++)
- {
- HSSFCell cell = row.createCell((short)i);
- HSSFCellStyle cellStyle = wb.createCellStyle();
- HSSFFont cellFont = wb.createFont();
- cellFont.setBoldweight(cellFont.BOLDWEIGHT_BOLD);
- cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- cellStyle.setFont(cellFont);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellStyle(cellStyle);
- cell.setCellValue(titleKeys[i]);
- }
- for(int j=0;j<listData.size();j++)
- {
- row = sheet.createRow((short)j+1);
- for(int k=0;k<titleKeys.length;k++)
- {
-
- HSSFCell cell =
- row.createCell((short)k);
-
- HSSFCellStyle cellStyle =
- wb.createCellStyle();
-
- HSSFFont cellFont =
- wb.createFont();
-
-
- cellFont.setColor(cellFont.COLOR_RED);
- cellStyle.setFont(cellFont);
-
-
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellStyle(cellStyle);
-
- String[] list =
- (String[])listData.get(j);
-
-
- //sheet.setDefaultColumnWidth((short)list[k].length());
- cell.setCellValue(list[k]);
- }
- }
- file = new FileOutputStream(excelFile);
- wb.write(file);
- System.out.println("已成功生成excel文件");
-
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- finally
- {
- if(stmt != null)
- {
- try
- {
- stmt.close();
- System.out.println("释放SQL句柄");
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- if(rs != null)
- {
- try
- {
- rs.close();
- System.out.println("释放记录集");
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- if(con != null)
- {
- try
- {
- con.close();
- System.out.println("释放连接");
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- if(file != null)
- {
- try
- {
- file.close();
- System.out.println("关闭文件流");
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- }
- }
-
- public static void main(String args[])
- {
- new ExcelCreate().createExcel();
- }
- }
复制代码- package org.yzq;
- import java.sql.*;
- import org.apache.poi.hssf.usermodel.*;
- import java.util.*;
- import java.io.*;
- public class ExcelToDatabase
- {
- private Connection con = null;
- private PreparedStatement pstmt = null;
- private String excelFile = "d:\poi\friends.xls";
-
- private void insertDataFromExcel()
- {
- try
- {
- //ExcelBean eb = new ExcelBean();
- String[] temp = new String[6];
- HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
- HSSFSheet sheet = wb.getSheet("sheet1");
- Class.forName("org.gjt.mm.mysql.Driver").newInstance();
- con =
- DriverManager.getConnection("jdbc:mysql://127.0.0.1/demo","yzq","8383055");
- pstmt =
- con.prepareStatement("INSERT INTO
- friends(name,sex,age,mobile,address,memo) VALUES(?,?,?,?,?,?)");
- //持久化数据
- for(int i=0;i<=sheet.getLastRowNum();i++)
- {
- ExcelBean eb = new ExcelBean();
- HSSFRow row = sheet.getRow(i);
- for(short j=0;j<row.getLastCellNum();j++)
- {
- HSSFCell cell = row.getCell(j);
-
-
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
-
- temp[j] =
- cell.getStringCellValue();
- //System.out.println(temp[j]);
- }
- eb.setListData(temp);
- //将数据插入到数据库中
- LinkedList listData = eb.getListData();
- for(int m=0;m<listData.size();m++)
- {
- for(int n=0;n<temp.length;n++)
- {
-
-
- System.out.print(temp[n]+" ");
- if(n==2)
- {
-
-
-
- pstmt.setInt(n+1,Integer.parseInt(temp[n].toString().trim()));
- }
- else
- {
-
-
- pstmt.setString(n+1,temp[n].toString().trim());
- }
- }
- pstmt.execute();
- //System.out.println();
-
-
- /*pstmt.setString(1,temp[0].toString().trim());
-
-
- pstmt.setString(2,temp[1].toString().trim());
-
-
- pstmt.setInt(3,Integer.parseInt(temp[2].toString().trim()));
-
-
- pstmt.setString(4,temp[3].toString().trim());
-
-
- pstmt.setString(5,temp[4].toString().trim());
-
-
- pstmt.setString(6,temp[5].toString().trim());
- pstmt.execute();*/
-
-
- System.out.println("成功插入了"+(m+1)+"条");
- }
- }
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- finally
- {
- if(pstmt != null)
- {
- try
- {
- pstmt.close();
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- if(con != null)
- {
- try
- {
- con.close();
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
- }
- }
-
- public static void main(String args[])
- {
- new ExcelToDatabase().insertDataFromExcel();
- }
- }
复制代码- package org.yzq;
- import org.apache.poi.hssf.usermodel.*;
- import java.io.*;
- public class ReadExcel
- {
- private String excelFile = "d:\poi\friends.xls";
-
- private void showExcel()
- {
- try
- {
- HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
- HSSFSheet sheet = wb.getSheet("sheet0");
- for(int i=0;i<sheet.getLastRowNum();i++)
- {
- HSSFRow row = sheet.getRow(i);
- for(short j=0;j<row.getLastCellNum();j++)
- {
- HSSFCell cell = row.getCell(j);
-
-
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- if((j==3)&&(i!=0))
- {
-
-
- System.out.print(cell.getNumericCellValue()+" ");
- }
- else
- {
-
-
- System.out.print(cell.getStringCellValue()+" ");
- }
- }
- System.out.println();
- }
- }
- catch(Exception err)
- {
- err.printStackTrace();
- }
- }
-
- public static void main(String args[])
- {
- new ReadExcel().showExcel();
- }
- }
复制代码 至于if(n==2)和if((j==3)&&(i!=0))是我当时一时没想到如何读取非String的数据而临时代替的,当然现在在真正的项目里就不这样写啦,呵呵
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u/460/showart_66303.html |
|