用excel2007直接画出表格，然后拖拽到项目对应的目录下，之后直接引用这个路径，或者用框架封装的配置文件去调用这个文件。
方法一、
Excel 2007版本的(后缀.xlsx) 用 XSSFWorkbook这个poi 方法一
Excel 2003版本的（后缀名是.xls） 用HSSFWorkbook这个poi 方法二
这两个方法都用了ExcelExpUtils---框架封装好的，方法三自己设置的Excel
@Override
public String getXDSJCDL_Excel(List<String> a0000s, EPrintConfig tpid, EPrintType type, String ids, List<String> result, String userId){
//这里是获取文件 框架封装的用配置文件调用的，如果自己直接获取文件参考方法二
String expFile = DownFileDirUtil.getTempFilePath(EHyFile.DOWNLOAD).getAbsolutePath();
String expName = "选调生基层锻炼考核评定表";
File file = new File(expFile + "/" + expName + Consiants.Excel_XLSX);
ArrayList<Map<String, Object>> list = new ArrayList<>();
// 根据字段查数据库的 这里是直接调用sql 返回全部字符串 可以用方法二 自己拼接sql 返回不同的实体类对象，在取值
List<Map<String, Object>> maps = dao.getMcListByXdsjcdlkhpd(ids);
ArrayList<String[]> listArr = new ArrayList<>();
int y=0;
int n=1;
for (Map<String, Object> tmp : maps) {//循环主键
tmp.put("num",n++);//序号暂时没用
String[] arr = {"A0101", tmp.get("a0101") + "", Consiants.String_Null, "第" + (y++) + "人员"};
listArr.add(arr);
list.add(tmp);
}
try {
FileInputStream in=new FileInputStream(this.getRootPath()+tpid.name);
XSSFWorkbook wook=new XSSFWorkbook(in);
XSSFSheet sheet=wook.getSheetAt(0);
if(list.size()>0) {
for(int i=0;i<list.size();i++) {//对列做操作转换成xml，下面就可以直接设值
XSSFRow row = sheet.createRow(i+4);//前4行无数据
for (int j=0; j< 14;j++){
row.createCell(j);
}
//姓名 给对应的列插入值 字段名和sql查出的字段名相同
row.getCell(0).setCellValue(list.get(i).get("a0101").toString());
if(list.get(i).get("a0192")!=null){//防止空指针
row.getCell(1).setCellValue(list.get(i).get("a0192").toString());
} if(list.get(i).get("a0104")!=null){
row.getCell(2).setCellValue(list.get(i).get("a0104").toString());
}
}
//输出Excel文件
FileOutputStream output = new FileOutputStream(file);
wook.write(output);
wook.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file.getParent();
}
方法二、
@Override
@Transactional
public String ExportDXXDExcel(JSONObject pagedata,String userId,String xdsRoleResult) throws IOException {
Session session = entityManager.unwrap(org.hibernate.Session.class);
//根据路径获取文件
File file = new File("./xdsdxxd.xls");
String rootpath = StringUtils.substringBefore(file.getCanonicalPath(),"xdsdx") + "target\\classes\\static\\exportexcel\\";
FileInputStream in=new FileInputStream(rootpath+"xdsdxxd.xls");
//Excel 2003 版本的
HSSFWorkbook wook=new HSSFWorkbook(in);
HSSFSheet sheet=wook.getSheetAt(0);
//用HBsession获取数据 封装成对象
Query query = session.createQuery("from SysUser s where s.userId = ?");
query.setParameter(0,userId);
List<SysUser> list = query.list();
Query query1 = session.createQuery("from XA01 x where x.b0111 = ?");
query1.setParameter(0,list.get(0).getOtherinfo());
List<XA01> list2 = query1.list();
String b0114 = "";
for (int i =0;i<list2.size();i++){
if (!list2.get(i).getB0114().equals(null))
b0114 = list2.get(i).getB0114(); break;
}
sb.append("and x.x0196='1' and substr(x.x0198,0,4) = '2022' order by x.b0111 asc,x.x0102 asc ");
//执行这个拼接好的sql 这里面都是一个表的字段
Query query2 = session.createQuery(sb.toString());
List<XA01> listResult = query2.list();
String path = null;
if(listResult.size()>0) {
for(int i=0;i<listResult.size();i++) {
HSSFRow row = sheet.createRow(i+5);
for (int j=0; j< 13;j++){
row.createCell(j);
}
//单位名称
row.getCell(0).setCellValue(listResult.get(i).getX0101());
//职位
row.getCell(1).setCellValue(listResult.get(i).getX0102());
//计划数
row.getCell(2).setCellValue(listResult.get(i).getX0103());
//学历要求
String xl= listResult.get(i).getX0104();
switch (xl){//不是设值数值 是√
case "001": row.getCell(3).setCellValue("√");
break;
case "002": row.getCell(4).setCellValue("√");
break;
case "003": row.getCell(5).setCellValue("√");
}
row.getCell(9).setCellValue("0".equals(listResult.get(i).getX0106())?"否":"是");
row.getCell(10).setCellValue(listResult.get(i).getX0107());
row.getCell(11).setCellValue(listResult.get(i).getX0108());
row.getCell(12).setCellValue(listResult.get(i).getX0109());
}
String xdsExcel = StringUtils.substringBefore(file.getCanonicalPath(),"xdsd")+"temp\\download_file\\XDSJHSB\\";
File xdsFile = new File(xdsExcel);
if(!xdsFile.exists() || !xdsFile.isDirectory()){
xdsFile.mkdirs();
}
path = xdsExcel+UUID.randomUUID()+".xls";
FileOutputStream fileOutputStream = new FileOutputStream(path);
wook.write(fileOutputStream);
fileOutputStream.close();
return path;
}
return "";
}
方法三、
获取文件比较长，没有用ExcelUptil ，自己设置excel格式
方法分离，没把列转换成xml,sql没有分开写
@SuppressWarnings("unused")
@PageEvent("ExpGird")
public int ExpGird(String param) throws RadowException{
param = this.request.getParameter("param");;
FileInputStream fis = null;
FileOutputStream fos = null;
FileInputStream is = null;
try{
String path = FileUtil.getPath4Excelload();
File file1=new File(path);
if (!file1 .exists() && !file1 .isDirectory())
{
file1 .mkdirs();
}
String pathdata=path+"个人重大事项汇总表.xls";
String ss=File.separatorChar+"";
pathdata=pathdata.replace("\\", ss);
pathdata=pathdata.replace("/", ss);
File file2=new File(pathdata);
if(!file2.exists()||!file2.isFile()){
file2.createNewFile();
}
///gbdjdsys/WebContent/pages/grzdsx/grzdsxcxlb.xls
String mbpath=this.request.getSession().getServletContext().getRealPath("/")+"pages\\grzdsx\\个人重大事项汇总表.xls";
mbpath=mbpath.replace("\\", ss);
mbpath=mbpath.replace("/", ss);
File file = new File(mbpath);
fis = new FileInputStream(file);
String file_copy=pathdata;
fos = new FileOutputStream(file_copy);
byte[] buf = new byte[1024*10];
int len = -1;
while((len = fis.read(buf)) != -1){
fos.write(buf, 0, len);
}
fis.close();
fos.close();
is=new FileInputStream(file_copy);
@SuppressWarnings("resource")
Workbook workbook = new HSSFWorkbook(is); //默认读取2003版的Excel
int sheetNums=workbook.getNumberOfSheets();
Font font2 =workbook.createFont();
font2.setFontName("仿宋_GB2312");
font2.setFontHeightInPoints((short) 11);
CellStyle cellstyle=workbook.createCellStyle();
cellstyle.setAlignment(HorizontalAlignment.CENTER);
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellstyle.setBorderLeft(BorderStyle.THIN);
cellstyle.setBorderRight(BorderStyle.THIN);
cellstyle.setBorderLeft(BorderStyle.THIN);
cellstyle.setBorderBottom(BorderStyle.THIN);
cellstyle.setFont(font2);
cellstyle.setWrapText(true);
Sheet sheet = null;
sheet=workbook.getSheetAt(0);
exportData(sheet,workbook, cellstyle,param.replace("^", "'"));
fos = new FileOutputStream(file_copy);
workbook.write(fos);
fos.flush();
fos.close();
workbook.close();
System.gc();
//压缩文件
String zippath=path+"个人重大事项汇总表.xls";
this.setSelfDefResData("2@@@"+zippath);
return EventRtnType.NORMAL_SUCCESS;
}catch(Exception e){
e.printStackTrace();
throw new RadowException(e.getMessage());
}finally{
try{
if(fis!=null){
fis.close();
}
if(fos!=null){
fos.close();
}
if(is!=null){
is.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}/**
* 写入数据
* @param sheet
* @param workbook
* @param cellstyle
* @param grzdsxid
* @throws Exception
*/
public void exportData(Sheet sheet,Workbook workbook,CellStyle cellstyle,
String grzdsxid) throws Exception{
try{
CommQuery cq=new CommQuery();
//selec DOCNO,to_char(ACCEPTDATE,'yyyymmdd') ACCEPTDATE,ACCEPTDEPT,REPORTERNAME,REPORTERDUTY,UNITTYPE,(select code_name from code_value where code_type='ZDSX001' and code_value=UNITTYPE) UNITTYPENAME,ITEMTYPE,(select code_name from code_value where code_type='ZDSX002' and code_value=ITEMTYPE) ITEMTYPENAME,CONTENT,to_char(AGGRPROCTIME,'yyyymmdd') AGGRPROCTIME,APPROVELEADER,PISHICONTENT,to_char(APPROVETIME,'yyyymmdd') APPROVETIME,MEMO from ZDSX_PERSONALRPT
String sql="select DOCNO,to_char(ACCEPTDATE,'yyyymmdd') ACCEPTDATE,ACCEPTDEPT,REPORTERNAME, "
+ "(select code_name from code_value where code_type='ZDSX001' and code_value=UNITTYPE) UNITTYPE,REPORTERDUTY, "
+ "(select code_name from code_value where code_type='ZDSX002' and code_value=ITEMTYPE) ITEMTYPE, "
+ "CONTENT,to_char(AGGRPROCTIME,'yyyymmdd') AGGRPROCTIME,APPROVELEADER,PISHICONTENT,to_char(APPROVETIME,'yyyymmdd') APPROVETIME,MEMO "
+ " from ZDSX_PERSONALRPT where oid in ( "+grzdsxid+") order by DOCNO desc";
System.out.println(sql);
List<HashMap<String, Object>> list=cq.getListBySQL(sql);
Cell cell=null;HashMap<String, Object> map=null;
for(int i=0;i<list.size();i++){
map=list.get(i);
Row row=sheet.createRow(i+2);
row.setHeight((short)(25*20));
int m=0;
cell=row.createCell(m++);cell.setCellStyle(cellstyle);cell.setCellValue(i+1);
cell=row.createCell(m++);cell.setCellStyle(cellstyle);cell.setCellValue((String)map.get("docno"));
cell=row.createCell(m++);cell.setCellStyle(cellstyle);cell.setCellValue((String)map.get("acceptdate"));
cell=row.createCell(m++);cell.setCellStyle(cellstyle);cell.setCellValue((String)map.get("acceptdept"));
cell=row.createCell(m++);cell.setCellStyle(cellstyle);cell.setCellValue((String)map.get("reportername"));
}
}catch(Exception e){
e.printStackTrace();
throw new Exception(e.getMessage());
}
}
294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
