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 PoiExlUtil {
/*通用工具方法*/
public static HSSFWorkbook getExcel(String sql,String sheetName){
Connection conn=DBUtil.getConnection();
Statement st=null;
ResultSet rs=null;
try {
st=conn.createStatement();
rs=st.executeQuery(sql);
HSSFWorkbook workbook=new HSSFWorkbook(); //新建一个excel文件
HSSFSheet sheet=workbook.createSheet(sheetName); //建立序表名
int j=0;
//生成标题
HSSFRow row=null;
HSSFCell cell=null;
ResultSetMetaData rsmd=rs.getMetaData();
row=sheet.createRow(j);
int size=rsmd.getColumnCount();
for (int i = 0; i <size; i++) {
cell=row.createCell((short)i);//1行中有几列
cell.setCellValue(new HSSFRichTextString(rsmd.getColumnLabel(i+1)));//循环每列赋值
}
//生成数据域
while(rs.next()){
j++;
row=sheet.createRow(j);
for (int i = 0; i <size; i++) {
cell=row.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(rs.getString(i+1)));
}
sheet.autoSizeColumn((short)j);
}
return workbook;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DBUtil.close(rs, st, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
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 PoiExlUtil {
/*通用工具方法*/
public static HSSFWorkbook getExcel(String sql,String sheetName){
Connection conn=DBUtil.getConnection();
Statement st=null;
ResultSet rs=null;
try {
st=conn.createStatement();
rs=st.executeQuery(sql);
HSSFWorkbook workbook=new HSSFWorkbook(); //新建一个excel文件
HSSFSheet sheet=workbook.createSheet(sheetName); //建立序表名
int j=0;
//生成标题
HSSFRow row=null;
HSSFCell cell=null;
ResultSetMetaData rsmd=rs.getMetaData();
row=sheet.createRow(j);
int size=rsmd.getColumnCount();
for (int i = 0; i <size; i++) {
cell=row.createCell((short)i);//1行中有几列
cell.setCellValue(new HSSFRichTextString(rsmd.getColumnLabel(i+1)));//循环每列赋值
}
//生成数据域
while(rs.next()){
j++;
row=sheet.createRow(j);
for (int i = 0; i <size; i++) {
cell=row.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(rs.getString(i+1)));
}
sheet.autoSizeColumn((short)j);
}
return workbook;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DBUtil.close(rs, st, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
step2.
HSSFWorkbook workbook=PoiExlUtil.getExcel(sql, "sheet名");
try {
//设置编码格式
String showName = URLEncoder.encode("文件名"+DateUtil.getCurrentDateStr("yyyyMMddhhmmmss")+".xls", "UTF-8");
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment; filename="
+ showName);
workbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}