package poi;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class POIExcelReader {
/**
* 读Excel文件的例子
* @param args
* @throws Exception
* date:20080129
*/
public static void main(String[] args) throws Exception {
//以Excel文件创建文件输入流
InputStream myxls = new FileInputStream("src/poi/name.xls");
//以文件输入流创建文档对象
HSSFWorkbook wb = new HSSFWorkbook(myxls);
//获取第一张工作表
HSSFSheet sheet = wb.getSheetAt(0);
//遍历工作表的第一行
Iterator it = sheet.rowIterator();
while(it.hasNext()){
HSSFRow row = (HSSFRow)it.next();
// for(short i=0; i<3 ; i++){
// HSSFCell cell = row.getCell(i); //这里getCell的参数要求是short型的
// if(cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
// System.out.println("单元格是字符串,值是:"+cell.getRichStringCellValue().getString());
// }
// else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
// System.out.println("单元格是数字,值是:"+cell.getNumericCellValue());
// }else{
// System.out.println("单元格的值不是字符串或数值!");
// }
//
// }
//遍历行的第一列(上面的用直接设置i也可以)
Iterator cellIt = row.cellIterator();
while(cellIt.hasNext()){
HSSFCell cell = (HSSFCell)cellIt.next();
//在读取单元值之前,先获取单元格的数据类型
if(cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
System.out.println("单元格是字符串,值是:"+cell.getRichStringCellValue().getString());
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
System.out.println("单元格是数字,值是:"+cell.getNumericCellValue());
}else{
System.out.println("单元格的值不是字符串或数值!");
}
}
}
}
}
创建Excel文档
package poi;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class MySqlExcel {
/**
* 创建Excel文档
* @param args
*/
public MySqlExcel() throws Exception{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost/test";
conn = DriverManager.getConnection(url,"root","admin");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
stmt = conn.createStatement();
String sql = "select * from userinfo";
rs = stmt.executeQuery(sql);
short i = 0;
while(rs.next()){
//创建电子表格的一行
HSSFRow row = sheet.createRow(i);
for(short j=1; j<4 ; j++){
HSSFCell cell = row.createCell((short)(j-1));
HSSFRichTextString strCell = new HSSFRichTextString(rs.getString(j));;
cell.setCellValue(strCell);
}
i++;
}
OutputStream out = new FileOutputStream("src/poi/导出的电子表格.xls");
wb.write(out);
out.close();
System.out.println("从数据库中导出成功");
rs.close();
stmt.close();
conn.close();
}
public static void main(String[] args) throws Exception {
new MySqlExcel();
}
}