java利用poi导出excel_java利用POI导出Excel功能实现

SXSSFWorkbook wb = new SXSSFWorkbook(1000);

Sheet sheet = wb.createSheet("sheet1");

// 设置excel每列宽度

sheet.setColumnWidth(0, 10000);

sheet.setColumnWidth(1, 7000);

sheet.setColumnWidth(2, 5000);

sheet.setColumnWidth(3, 4000);

sheet.setColumnWidth(4, 6000);

sheet.setColumnWidth(5, 8000);

// 创建字体样式

Font font = wb.createFont();

font.setFontName("Verdana");

font.setBoldweight((short) 100);

// font.setFontHeight((short) 300);

// font.setColor(HSSFColor.BLUE.index);

// 创建单元格样式

CellStyle styleLeft = wb.createCellStyle();

styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);

styleLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);

// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

// 设置边框

// style.setBottomBorderColor(HSSFColor.RED.index);

styleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);

styleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);

styleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);

styleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);

styleLeft.setFont(font);// 设置字体

// 创建单元格样式

CellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);

// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

// 设置边框

// style.setBottomBorderColor(HSSFColor.RED.index);

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setBorderTop(HSSFCellStyle.BORDER_THIN);

style.setFont(font);// 设置字体

// 创建单元格样式

CellStyle styleRight = wb.createCellStyle();

styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

styleRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);

// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

// 设置边框

// style.setBottomBorderColor(HSSFColor.RED.index);

styleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);

styleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);

styleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);

styleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);

styleRight.setFont(font);// 设置字体

// 创建Excel的sheet的一行

Row row = sheet.createRow(0);

// row.setHeight((short) 500);// 设定行的高度

// 创建一个Excel的单元格

Cell cell = row.createCell(0);

// 合并单元格(startRow，endRow，startColumn，endColumn)

// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

// 设置单元格内容格式

// HSSFCellStyle styleDate = wb.createCellStyle();

// styleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy年MM月dd日"));

// 给Excel的单元格设置样式和赋值

cell.setCellStyle(style);

cell.setCellValue("名称");

cell = row.createCell(1);

cell.setCellStyle(style);

cell.setCellValue("重量");

cell = row.createCell(2);

cell.setCellStyle(style);

cell.setCellValue("单价");

cell = row.createCell(3);

cell.setCellStyle(style);

cell.setCellValue("总价");

cell = row.createCell(4);

cell.setCellStyle(style);

cell.setCellValue("备注");

cell = row.createCell(5);

cell.setCellStyle(style);

cell.setCellValue("交易时间");

//DecimalFormat df = new DecimalFormat("#.#");

SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");

// 使用createRow将信息写在内存中。

int listCount = dao.getListCount(....);

int rowNum = 1;

for(int i=0;i

List modelList = dao.getListByID(...);

for (T rowMap : modelList) {

row = sheet.createRow(rowNum);

cell = row.createCell(0);

cell.setCellStyle(styleLeft);

cell.setCellValue(rowMap.getString("name"));

cell = row.createCell(1);

cell.setCellStyle(styleLeft);

cell.setCellValue(rowMap.getString("weight"));

cell = row.createCell(2);

cell.setCellStyle(styleLeft);

cell.setCellValue(rowMap.getString("price"));

cell = row.createCell(3);

cell.setCellStyle(styleRight);

cell.setCellValue(rowMap.getString("totalprice"));

cell = row.createCell(4);

cell.setCellStyle(styleRight);

cell.setCellValue(rowMap.getString("remark"));

cell = row.createCell(5);

cell.setCellStyle(styleRight);

cell.setCellValue(rowMap.getString("trading"));

rowNum++;

}

}

String fileName = "我导出的Excel文件.xlsx";

FileOutputStream os = null;

try {

File filePath = new File(

"..\\export\\excel");

if (!filePath.exists()) {

if (!filePath.mkdir()) {

throw new RuntimeException("创建目录失败！");

}

}

os = new FileOutputStream(

"..\\export\\excel\\fileName);

wb.write(os);

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} finally {

if (os != null) {

try {

os.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

return "我的文件:" + fileName;