Apache POI是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案。
正好项目中用到了这项技术,今天抽时间整理一下,以下进入正题:
1、jar包的引入,共用到了一下jar包
2、excel导入的后台代码
/**
* excel导入
* @param sql
*
*/
@RequestMapping(value="/upload",method = RequestMethod.POST)
@ResponseBody
public PageResponse upload(@RequestParam("file")CommonsMultipartFile[] files,
HttpServletRequest request,Model model, String sql){
PageResponse page = new PageResponse();
ArrayList<Economy> list = new ArrayList<Economy>();
try {
InputStream is = files[0].getInputStream();//输入流
POIFSFileSystem fs= new POIFSFileSystem(is);
HSSFWorkbook wb= new HSSFWorkbook(fs);
HSSFSheet sheet= wb.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
ArrayList<String> sqllist =new ArrayList<String>();
//循环拼sql语句
for(int i=1;i<lastRowNum+1;i++){
HSSFRow row = sheet.getRow(i);
sql = "replace into info.etl_i_regiondata(province,county,years)values(";
HSSFCell cell = row.getCell(0);
sql+="'"+cell.getStringCellValue()+"',";
HSSFCell cell1 = row.getCell(1);
sql+="'"+cell1.getStringCellValue()+"',";
HSSFCell cell2 = row.getCell(2);
int cellValue2 = (int) cell2.getNumericCellValue();
sql+="'"+cellValue2+"',";
if(sql.endsWith(",")){
sql = sql.substring(0, sql.length()-1);
}
sql+=")";
sqllist.add(sql);
}
//System.out.println(JSONArray.fromObject(sqllist).toString());
JdbcUtil.executeSql(sql); //执行单条sql
JdbcUtil.executeSqlList(sqllist); //批量执行sql
page.setStatus(true);
page.setMessage("数据上传成功");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("数据导入出错:"+e.getMessage());
page.setStatus(false);
page.setMessage(e.getMessage());
}
return null;
}
3、Excel根据查询结果导出后台代码
/**
* excel导出
*/
@RequestMapping(value="/export")
public void export(HttpServletResponse response,ServletOutputStream outputStream,
@RequestParam(value="city",required=false)String city,
@RequestParam(value="county",required=false)String county,
@RequestParam(value="years",required=false)String years){
int MAN=Integer.valueOf(MAX); //设置最大导出参数
HashMap<String,Object> map = new HashMap<String,Object>();
map.put("years", years);
map.put("city",city);
map.put("county", county);
map.put("MAN", MAN);
try{
ArrayList<Economy> list = IEconomyService.findByExportMap(map);//获取数据
HSSFWorkbook workBook = new HSSFWorkbook();
//在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = workBook.createSheet("文件");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
String[] titles = { "省份", "县", "年份"};
for (int i = 0; i < titles.length; i++){
cell = row.createCell((i));
cell.setCellValue(titles[i]);
}
// 构建表体数据
if (list != null && list.size() > 0) {
for (int j = 0; j < list.size(); j++)
{
HSSFRow Row = sheet.createRow(j + 1);
Economy economy = list.get(j);
cell = Row.createCell(0);
cell.setCellValue(economy.getProvince());
cell = Row.createCell(1);
cell.setCellValue(economy.getCounty());
cell = Row.createCell(2);
int cellValue=Integer.valueOf(economy.getYears());
cell.setCellValue(cellValue);
}
}
outputStream = response.getOutputStream();
String fileName = URLEncoder.encode("文件","UTF8");
response.setHeader("Content-disposition", "attachment; filename="+ fileName +".xls");// 组装附件名称和格式
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
}catch(Exception e){
e.printStackTrace();
logger.error("Excel导出数据出错:"+e.getMessage());
}
}