POI学习

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();
}

}