import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public String implementReturnMessage(Key[] arg0, String arg1, UFDate arg2)
throws BusinessException {
//视图名称
Object view = arg0[0].getValue();
//部门名称
Object dept = arg0[1].getValue();
//公司主键
Object corp = "1001";
String sql = "select * from "+view+" where pk_dept = "+"'"+dept+"' and pk_corp=(SELECT unitname FROM bd_corp WHERE pk_corp = '"+corp+"')";
List<Map<String, Object> > list = (List<Map<String, Object> >) getDAO().executeQuery(sql, new MapListProcessor());
//查询表所有列
List<Object[]> result = (List<Object[]>) dao.executeQuery("SELECT column_name FROM user_tab_cols where table_name = upper('"+view+"')", new ArrayListProcessor());
//创建Excel文件薄
HSSFWorkbook workbook=new HSSFWorkbook();
//创建工作表sheeet
HSSFSheet sheet=workbook.createSheet();
HSSFCell cell_title = null;
//追加数据
for (int i=0;i<list.size();i++){
Map<String,Object> map =list.get(i);
HSSFRow rows=sheet.createRow(i);
//列数
int row = 0;
for (Object[] obj : result){
//列递增
cell_title=rows.createCell(row++);
//自适应列宽
setSizeColumn(sheet, row);
//第一行添加标题
if(i==0){
cell_title.setCellValue(obj[0].toString());
i=0;
}else{
//列赋值
if(map.get(obj[0])==null){
cell_title.setCellValue("");
}else{
cell_title.setCellValue(map.get(obj[0].toString()).toString());
}
}
}
}
//创建一个文件
try {
FileOutputStream fout = new FileOutputStream("E:/办公费明细.xls");
workbook.write(fout);
fout.close();
}catch (Exception e){
e.printStackTrace();
}
return null;
}
/**
* 自适应列宽
* @param sheet 表
* @param columnLength 列
*/
private static void setSizeColumn(HSSFSheet sheet, int columnLength) {
for (int columnNum = 0; columnNum <= columnLength; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow; // 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
HSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
java 创建excel 并加入数据
最新推荐文章于 2024-07-27 04:35:47 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)