- 创建一个工作簿
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
FileOutputStream fileOut = new FileOutputStream("c:\\用Poi搞出来的工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 创建Sheet页
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
wb.createSheet("第二个Sheet页"); // 创建第二个Sheet页
FileOutputStream fileOut = new FileOutputStream("c:\\用Poi搞出来的Sheet页.xls");
wb.write(fileOut);
fileOut.close();
- 创建Row和Cell,并给单元格设置值
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(0); // 创建一个行
Cell cell = row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(1); // 给单元格设置值
row.createCell(1).setCellValue(1.2); // 创建一个单元格 第2列 值是1.2
row.createCell(2).setCellValue("这是一个字符串类型"); // 创建一个单元格 第3列 值为一个字符串
row.createCell(3).setCellValue(false); // 创建一个单元格 第4列 值为布尔类型
FileOutputStream fileOut = new FileOutputStream("c:\\用Poi搞出来的Cell.xls");
wb.write(fileOut);
fileOut.close();
- 创建一个时间格式的单元格
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(0); // 创建一个行
Cell cell = row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(new Date()); // 给单元格设置值
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle(); //单元格样式类
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
cell = row.createCell(1); // 第二列
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
cell = row.createCell(2); // 第三列
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 处理不同类型格式的单元格
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(0); // 创建一个行
Cell cell = row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(new Date()); // 给单元格设置值
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("一个字符串");
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue(HSSFCell.CELL_TYPE_NUMERIC);
row.createCell(5).setCellValue(false);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 遍历工作簿的行和列
InputStream is = new FileInputStream("c:\\二货名单.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
if(hssfSheet == null){
return;
}
// 遍历行Row
for(int rowNum = 0;rowNum <= hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow == null){
continue;
}
// 遍历列Cell
for(int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++){
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if(hssfCell == null){
continue;
}
System.out.print(" " + getValue(hssfCell));
}
System.out.println();
}
private static String getValue(HSSFCell hssfCell){
if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
- 文本提取(不需要遍历,将所有文本内容抽取出来)
InputStream is = new FileInputStream("c:\\二货名单.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
ExcelExtractor excelExtractor = new ExcelExtractor(wb);
excelExtractor.setIncludeSheetNames(false);// 我们不需要Sheet页的名字
System.out.println(excelExtractor.getText());
- 设置单元格的样式
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(2); // 创建一个行
row.setHeightInPoints(30);
createCell(wb, row, (short)0, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short)1, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short)2, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP);
createCell(wb, row, (short)3, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
/**
* 创建一个单元格并为其设定指定的对其方式
* @param wb 工作簿
* @param row 行
* @param column 列
* @param halign 水平方向对其方式
* @param valign 垂直方向对其方式
*/
private static void createCell(Workbook wb,Row row,short column,short halign,short valign){
Cell cell = row.createCell(column); // 创建单元格
cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值
CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式
cellStyle.setAlignment(halign); // 设置单元格水平方向对其方式
cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对其方式
cell.setCellStyle(cellStyle); // 设置单元格样式
}
- 边框处理
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(1); // 创建一个行
Cell cell = row.createCell(1); // 创建一个单元格
cell.setCellValue(4);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色
cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); // 右边边框颜色
cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
cell.setCellStyle(cellStyle);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 单元格填充颜色
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(1); // 创建一个行
Cell cell = row.createCell(1);
cell.setCellValue("XX");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 背景色
cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
cell.setCellStyle(cellStyle);
Cell cell2 = row.createCell(2);
cell2.setCellValue("YYY");
CellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色
cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell2.setCellStyle(cellStyle2);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 合并单元格
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(1); // 创建一个行
Cell cell = row.createCell(1);
cell.setCellValue("单元格合并测试");
sheet.addMergedRegion(new CellRangeAddress(
1, // 起始行
2, // 结束行
1, // 其实列
2 // 结束列
));
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 字体处理
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(1); // 创建一个行
// 创建一个字体处理类
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
CellStyle style = wb.createCellStyle();
style.setFont(font);
Cell cell = row.createCell((short)1);
cell.setCellValue("This is test of fonts");
cell.setCellStyle(style);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 读取并重写工作簿
InputStream inp = new FileInputStream("c:\\工作簿.xls");
POIFSFileSystem fs = new POIFSFileSystem(inp);
Workbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.getSheetAt(0); // 获取第一个Sheet页
Row row = sheet.getRow(0); // 获取第一行
Cell cell = row.getCell(0); // 获取单元格
if(cell == null){
cell = row.createCell(3);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("测试单元格");
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 单元格换行
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row = sheet.createRow(2); // 创建一个行
Cell cell = row.createCell(2);
cell.setCellValue("我要换行 \n 成功了吗?");
CellStyle cs = wb.createCellStyle();
// 设置可以换行
cs.setWrapText(true);
cell.setCellStyle(cs);
// 调整下行的高度
row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints());
// 调整单元格宽度
sheet.autoSizeColumn(2);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 创建数据格式
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
CellStyle style;
DataFormat format = wb.createDataFormat();
Row row;
Cell cell;
short rowNum = 0;
short colNum = 0;
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(111111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0")); // 设置数据格式
cell.setCellStyle(style);
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(1111111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.000"));
cell.setCellStyle(style);
FileOutputStream fileOut = new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
- 导出Excel练习1-普通导出
public String export()throws Exception{
Connection con = null;
try {
con = dbUtil.getCon();
Workbook wb = new HSSFWorkbook();
String headers[] = {"编号","姓名","电话","Email","QQ"};
ResultSet rs = userDao.userList(con, null);
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(ServletActionContext.getResponse(), wb,
"导出excel.xls");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
public class ExcelUtil {
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)
throws Exception{
int rowIndex = 0;
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(rowIndex++);
for(int i = 0;i < headers.length; i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row = sheet.createRow(rowIndex++);
for(int i= 0;i < headers.length; i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
}
}
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o)
throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}
/**
* 导出Excel
*/
public static void export(HttpServletResponse response,Workbook wb,
String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="
+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
- 导出Excel练习2-模板导出
public String export2()throws Exception{
Connection con=null;
try {
con = dbUtil.getCon();
ResultSet rs = userDao.userList(con, null);
Workbook wb = ExcelUtil
.fillExcelDataWithTemplate(
userDao.userList(con, null),"userExporTemplate.xls");
ResponseUtil.export(ServletActionContext.getResponse(),
wb, "利用模版导出excel.xls");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
public class ExcelUtil {
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)
throws Exception{
int rowIndex=0;
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
}
public static Workbook fillExcelDataWithTemplate(ResultSet rs,
String templateFileName)
throws Exception{
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"
+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
// 获取列数
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}
}
导入Excel练习
public String upload()throws Exception{
POIFSFileSystem fs = new POIFSFileSystem(
new FileInputStream(userUploadFile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
if(hssfSheet != null){
for(int rowNum = 1;rowNum <= hssfSheet.getLastRowNum(); rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow == null){
continue;
}
User user = new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
Connection con = null;
try{
con = dbUtil.getCon();
userDao.userAdd(con, user);
}catch(Exception e){
e.printStackTrace();
}finally{
dbUtil.closeCon(con);
}
}
}
JSONObject result = new JSONObject();
result.put("success", "true");
ResponseUtil.write(ServletActionContext.getResponse(), result);
return null;
}
public class ExcelUtil {
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)
throws Exception{
int rowIndex = 0;
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(rowIndex++);
for(int i = 0;i < headers.length; i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row = sheet.createRow(rowIndex++);
for(int i = 0;i < headers.length; i++){
row.createCell(i).setCellValue(rs.getObject(i + 1).toString());
}
}
}
public static Workbook fillExcelDataWithTemplate(ResultSet rs,
String templateFileName)
throws Exception{
InputStream inp = ExcelUtil.class
.getResourceAsStream("/com/java1234/template/"+templateFileName);
POIFSFileSystem fs = new POIFSFileSystem(inp);
Workbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.getSheetAt(0);
// 获取列数
int cellNums = sheet.getRow(0).getLastCellNum();
int rowIndex = 1;
while(rs.next()){
Row row = sheet.createRow(rowIndex++);
for(int i = 0;i < cellNums; i++){
row.createCell(i).setCellValue(rs.getObject(i + 1).toString());
}
}
return wb;
}
public static String formatCell(HSSFCell hssfCell){
if(hssfCell == null){
return "";
}else{
if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()== HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
}