这是我写的第一篇关于excel的代码,最简单 ^_^ 以后会慢慢的变得复杂,功能也会慢慢完善。!
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javaservlets.common.util.SimpleServlet;
import javaservlets.common.util.systemBaseServlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelTest extends HttpServlet{
protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet("sheet1");
@SuppressWarnings("unused")
HSSFSheet sheet2 = workbook.createSheet("sheet2");
// 創建行
HSSFRow row1 = sheet1.createRow(1);
HSSFRow row2 = sheet1.createRow(2);
HSSFRow row3 = sheet1.createRow(3);
HSSFRow row4 = sheet1.createRow(4);
HSSFRow row5 = sheet1.createRow(5);
//單元格
HSSFCell cell1_1=row1.createCell(0),
cell1_2 = row1.createCell(1),cell1_3 = row1.createCell(2)
,cell1_4 = row1.createCell(3),cell1_5 = row1.createCell(4)
,cell1_6 = row1.createCell(5),cell1_7 = row1.createCell(6);
HSSFCell cell2_2 = row2.createCell(1),cell2_3 = row2.createCell(2)
,cell2_4 = row2.createCell(3),cell2_5 = row2.createCell(4)
,cell2_6= row2.createCell(5),cell2_7 = row2.createCell(6);
HSSFCell cell3_2 = row3.createCell(1),cell3_3 = row3.createCell(2)
,cell3_4 = row3.createCell(3),cell3_5 = row3.createCell(4)
,cell3_6= row3.createCell(5),cell3_7 = row3.createCell(6);
HSSFCell cell4_3 = row4.createCell(2);
HSSFCell cell5_3 = row5.createCell(2);
cell1_2.setCellValue("姓名");
cell1_3.setCellValue("年齡");
cell1_4.setCellValue("入職時間");
cell1_5.setCellValue("聯繫電話");
sheet1.setColumnWidth(3, 250*20); //設置列的寬度
sheet1.setColumnWidth(4, 250*20);
cell1_6.setCellValue("Email");
cell1_7.setCellValue("address");
//创建excel 超链接
//cell1_1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//cell1_1.setCellFormula("HYPERLINK(\"[workbook.xls]'sheet2'!A1\",\"sheet2\")");
cell2_2.setCellValue("王舒婷");
cell2_3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell2_3.setCellValue(20);
cell2_4.setCellValue("2011/05/16");
cell2_5.setCellValue("15012995131");
cell2_6.setCellValue("wst5921@163.com");
cell2_7.setCellValue("山東菏澤");
cell3_2.setCellValue("王秀娜");
cell3_3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell3_3.setCellValue(18);
cell3_4.setCellValue("2011/05/16");
cell3_5.setCellValue("121212");
cell3_6.setCellValue("222@163.com");
cell3_7.setCellValue("山東菏澤");
cell4_3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell4_3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell4_3.setCellValue(22);
cell5_3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell5_3.setCellFormula("SUM("+cell2_3+","+cell4_3+")/2"); //求平均年龄
//創建excel 超鏈接
String file = "D://workbook.xls";
/*
* 設置title字段样式
*
* */
Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
HSSFCellStyle cell_header_title = workbook.createCellStyle();
HSSFFont font_header_title = workbook.createFont();
font_header_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font_header_title.setFontHeight((short)(12*20)); //設置字體大小
font_header_title.setFontName("Times New Roman");
cell_header_title.setFont(font_header_title);
cell_header_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell_header_title.setWrapText(false);
styles.put("cell_header_title", cell_header_title);
cell1_2.setCellStyle(styles.get("cell_header_title"));
cell1_3.setCellStyle(styles.get("cell_header_title"));
cell1_4.setCellStyle(styles.get("cell_header_title"));
cell1_5.setCellStyle(styles.get("cell_header_title"));
cell1_6.setCellStyle(styles.get("cell_header_title"));
cell1_7.setCellStyle(styles.get("cell_header_title"));
getResultSet();
try {
FileOutputStream out = new FileOutputStream(file);
workbook.write(out);
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {
this.doGet(req, resp);
}
public void getResultSet(){
ResultSet rs=null;
Connection conn=null;
PreparedStatement statement=null;
String SQL="select * from [email].[dbo].Country";
try {
conn = SimpleServlet.eliteDWHCP.getConnection();
statement =conn.prepareStatement(SQL);
rs = statement.executeQuery();
System.out.println(rs.getFetchSize());
while(rs.next()){
System.out.println("0--->"+rs.getString("countryCode"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
SimpleServlet.eliteDWHCP.free(conn);
}
}
}
}