正文
用poi来导出数据到excel文档
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
package cn.com.dyg.work.common.utils; import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbookFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory; import java.util.List; /**
* 导出Excel公共方法
*
* @author yxb
* @version 1.0
*/
public class ExportExcelTool { //显示的导出表的标题
private String title;
//导出表的列名
private String[] rowName; private List<Object[]> dataList;
private boolean xssf; private String[] fieldNames; //构造方法，传入要导出的数据
public ExportExcelTool(String title, String[] rowName, String[] fieldNames, List<Object[]> dataList, boolean xssf) {
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.xssf = xssf;
this.fieldNames = fieldNames;
} /**
* Create a new empty Workbook, either XSSF or HSSF depending
* on the parameter
*
* @return The created workbook
*/
public Workbook create() {
if (xssf) {
return XSSFWorkbookFactory.createWorkbook();
} else {
return HSSFWorkbookFactory.createWorkbook();
}
} /*
* 导出数据
* */
public Workbook export() {
try {
Workbook workbook = create(); // 创建工作簿对象
Sheet sheet = workbook.createSheet(title); // 创建工作表 // 产生表格标题行
Row rowm = sheet.createRow(0);
Cell cellTiltle = rowm.createCell(0); rowm.setHeight((short) (25 * 35)); //设置高度 //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length - 1)));
cellTiltle.setCellValue(title); // 定义所需列数
int fieldRow = fieldNames.length;
Row fieldName = sheet.createRow(1); // 在索引2的位置创建行(最顶端的行开始的第二行) fieldName.setHeight((short) (25 * 25)); //设置高度 // 将列头设置到sheet的单元格中
for (int n = 0; n < fieldRow; n++) {
Cell cellRowName = fieldName.createCell(n); //创建列头对应个数的单元格
RichTextString text;
if (xssf)
text = new XSSFRichTextString(fieldNames[n]);
else
text = new HSSFRichTextString(fieldNames[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
} // 定义所需列数
int columnNum = rowName.length;
Row rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行) rowRowName.setHeight((short) (25 * 25)); //设置高度 // 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
Cell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
RichTextString text;
if (xssf)
text = new XSSFRichTextString(rowName[n]);
else
text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
} //将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) { Object[] obj = dataList.get(i);//遍历每个对象
Row row = sheet.createRow(i + 3);//创建所需的行数 row.setHeight((short) (25 * 20)); //设置高度 for (int j = 0; j < obj.length; j++) {
Cell cell; //设置单元格的数据类型
cell = row.createCell(j, CellType.STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
Cell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
} }
//
// if (workbook != null) {
// try {
// String fileName = "C:\\Users\\yangxiaobo\\Desktop/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString();
// if (xssf)
// fileName += ".xlsx";
// else
// fileName += ".xls";
// FileOutputStream out = new FileOutputStream(fileName);
// workbook.write(out);
// out.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// } // String fileName = "" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString();
// if (xssf)
// fileName += ".xlsx";
// else
// fileName += ".xls";
return workbook;
} catch (Exception e) {
e.printStackTrace();
} return null;
}
}
