导入代码片段
InputStream excelStream = formFile.getInputStream();
Workbook workbook = Workbook.getWorkbook(excelStream);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
label: for (int i = 1; i < rows; i++) {
String jgh = sheet.getCell(0, i).getContents().trim();
if (jgh == null || "".equals(jgh)) {
result.append("第" + Integer.toString(i + 1)
+ "行:‘教工号’为空,导入失败。\r\n");
failcount++;
continue label;
}
List jghlist = this.getQzDao().execSqlQueryToArrays("select * from tb_jg where jgh='"+jgh+"'");
if (jghlist.size() != 0) {
result.append("第" + Integer.toString(i + 1)
+ "行:‘教工号’已存在,导入失败。\r\n");
failcount++;
continue label;
}
String xm = sheet.getCell(1, i).getContents().trim();
if (xm == null || "".equals(xm)) {
result.append("第" + Integer.toString(i + 1)
+ "行:‘教师姓名’为空,导入失败。\r\n");
failcount++;
continue label;
}
String xb = sheet.getCell(2, i).getContents().trim();
String dwmc = sheet.getCell(3, i).getContents().trim();
String dwh="";
if (dwmc == null || "".equals(dwmc)) {
result.append("第" + Integer.toString(i + 1)
+ "行:‘所属单位’为空,导入失败。\r\n");
failcount++;
continue label;
}else{
List<Map<String,Object>> ls= this.getQzDao().execSqlQueryToMap("select * from xx0301 where dwmc='"+dwmc+"'");
if(ls.isEmpty()){
result.append("第" + Integer.toString(i + 1)
+ "行:‘所属单位’不存在,导入失败。\r\n");
failcount++;
continue label;
}else{
dwh=ls.get(0).get("dwh").toString();
}
}
String lxdh = sheet.getCell(4, i).getContents().trim();
String sql="INSERT INTO tb_jg VALUES ('"+Sequence.getInstance().getSequence(32)+"','"+jgh+"','"+xm+"','"+xb+"','"+dwh+"','"+lxdh+"')";
this.getQzDao().execSqlUpdate(sql);
successcount++;
}
String filepath = null;
filepath = this.servlet.getServletContext().getRealPath("/template/jg.txt");
String fileContent ="绩点信息导入\r\n成功导入:" + successcount + "条数据,失败导入:" + failcount
+ "条数据\r\n" + result.toString();
导出代码片段
try {
request.setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
try {
response.setHeader("Content-Disposition","attachment;filename="+ new String("工训成绩一览表".getBytes("GBK"), "ISO-8859-1") + ".xls");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}//指定下载的文件名
//response.setContentType("application/vnd.ms-excel");
response.setContentType("application/x-download");
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("工训成绩列表");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("单元成绩");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("课程");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
if(list!=null&&list.size()>0){
for(int i=0;i<list.size();i++){
Object[] print = (Object[]) list.get(i);
row = sheet.createRow((int) i + 1);
row.createCell((short) 0).setCellValue(print[1]==null?"":print[1].toString());
row.createCell((short) 1).setCellValue(print[2]==null?"":print[2].toString());
row.createCell((short) 2).setCellValue(print[3]==null?"":print[3].toString());
}
}
sheet.autoSizeColumn((short)0); //调整第一列宽度
sheet.autoSizeColumn((short)1); //调整第二列宽度
sheet.autoSizeColumn((short)2);
try {
OutputStream fout = response.getOutputStream();
wb.write(fout);
fout.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}