public class PoiTest {
// 输出文件
public void outputFile(HSSFWorkbook wb, String fileName) throws Exception{
FileOutputStream fileOut = new FileOutputStream("d:\test\"+fileName);
wb.write(fileOut);
fileOut.close();
}
@Test
public void test1() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("d:\test\test.xls");
wb.write(fileOut);
fileOut.close();
}
// create a excel with sheet
@Test
public void test2() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
wb.createSheet("sheet1");
wb.createSheet("sheet2");
FileOutputStream fileOut = new FileOutputStream("d:\test\test2.xls");
wb.write(fileOut);
fileOut.close();
}
// create a file with row and cell
@Test
public void test3() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(0);
// 创建一个单元格,第1列
HSSFCell cell = row.createCell(0);
// 设置单元格的值
cell.setCellValue(1);
outputFile(wb, "test3.xls");
}
@Test
public void test4() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(0);
// 创建一个单元格,第1列
row.createCell(0).setCellValue(1);
row.createCell(1).setCellValue(1.2); // 第二列
row.createCell(2).setCellValue("字符串类型"); // 第三列
row.createCell(3).setCellValue(false);
row.createCell(4).setCellValue(new Date().toString());
outputFile(wb, "test4.xls");
}
// 给单元格设置时间格式
@Test
public void test5() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(0);
// 设置单元格样式
HSSFCreationHelper creationHelper = wb.getCreationHelper();
HSSFCellStyle cellStyle = wb.createCellStyle(); // 单元格样式类
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
row.createCell(0).setCellValue(new Date());
HSSFCell cell = row.createCell(1);// 第二列
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());
outputFile(wb, "test5.xls");
}
private String getValue(HSSFCell cell) {
switch (cell.getCellType()) {
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
default:
return String.valueOf(cell.getStringCellValue());
}
}
// 遍历工作簿
@Test
public void test6() throws Exception{
FileInputStream is = new FileInputStream("d:\test\二货名单.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
// get first sheet
HSSFSheet hssfSheet = wb.getSheetAt(0);
// 遍历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();
}
}
// 直接提取工作簿的文本
@Test
public void test7() throws Exception{
FileInputStream is = new FileInputStream("d:\test\二货名单.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());
}
// 设置对齐方式
@Test
public void test8() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(2);
// 设置行高
row.setHeightInPoints(30);
createCell(wb, row, (short) 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
createCell(wb, row, (short) 1, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
createCell(wb, row, (short) 2, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
createCell(wb, row, (short) 3, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
outputFile(wb, "test8.xls");
}
/**
* 创建一个单元格并为其设置指定对齐方式
* @param wb 工作簿
* @param row 行
* @param column 列
* @param halign 水平对齐方式
* @param valign 垂直对齐方式
*/
private void createCell(Workbook wb, Row row, short column, HorizontalAlignment halign, VerticalAlignment 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);
}
// 设置边框和颜色
@Test
public void test9() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue(4);
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置边框的颜色和样式
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
cell.setCellStyle(cellStyle);
outputFile(wb, "test9.xls");
}
// 设置背景颜色
@Test
public void test10() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("XX");
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex()); // 背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充样式
cell.setCellStyle(cellStyle);
outputFile(wb, "test10.xls");
}
// 合并单元格
@Test
public void test11() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("单元格合并测试");
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 2));
outputFile(wb, "test11.xls");
}
// 设置字体
@Test
public void test12() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(1);
// 创建字体处理类
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is test of fonts");
cell.setCellStyle(cellStyle);
outputFile(wb, "test12.xls");
}
// 读取和重写工作簿
@Test
public void test13() throws Exception{
FileInputStream inp = new FileInputStream("d:\test\test13.xls");
POIFSFileSystem fs = new POIFSFileSystem(inp);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
if (null == row) {
row = sheet.createRow(0);
}
HSSFCell cell = row.getCell(0);
if (null == cell) {
cell = row.createCell(3);
}
cell.setCellType(CellType.STRING);
cell.setCellValue("测试单元格");
outputFile(wb, "test13.xls");
}
// 单元格内换行
@Test
public void test14() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 创建一个行
HSSFRow row = sheet.createRow(2);
HSSFCell cell = row.createCell(2);
cell.setCellValue("我要换行
成功了吗?");
HSSFCellStyle cs = wb.createCellStyle();
// 设置可以换行
cs.setWrapText(true);
cell.setCellStyle(cs);
// 调整下行的高度
row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints());
// 调整单元格宽度
sheet.autoSizeColumn(2);
outputFile(wb, "test14.xls");
}
// 设置数据格式
@Test
public void test15() throws Exception{
// 定义一个新的工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
CellStyle style;
DataFormat dataFormat = 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(dataFormat.getFormat("0.0")); // 设置数据格式
cell.setCellStyle(style);
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(111111.25);
style = wb.createCellStyle();
style.setDataFormat(dataFormat.getFormat("#,##0.000")); // 设置数据格式
cell.setCellStyle(style);
outputFile(wb, "test15.xls");
}
// 循环填数据
private 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());
}
}
}
// 导出excel
private 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 outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}
// 用模板填数据
private Workbook fillExcelDataWithTemplate(ResultSet rs, String templateFileName) throws Exception {
FileInputStream inp = new FileInputStream("d:\test\模板.xls");
POIFSFileSystem fs = new POIFSFileSystem(inp);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// 获取列数
int cellNum = sheet.getRow(0).getLastCellNum();
int rowIndex = 1;
while (rs.next()) {
Row row = sheet.createRow(rowIndex++);
for (int i = 0; i < cellNum; i++) {
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}
}