/**
* 导出
* @param request
* @param response
*/
@RequestMapping(value="exportInfo")
public void exportInfo(HttpServletRequest request,HttpServletResponse response ){
try {
String[] fi_ids={"0001","0002"};
//根据业务查询需要下载的信息
List<Map<String, Object>> selectExportInfo = backgroundService.selectExportInfo(fi_ids);
String file_name=System.currentTimeMillis()+"";
String fileDir="E:"+File.separator+"upload"+File.separator+file_name+".xls";
String sheetName="信息";
//String[] titleRow={"企业名称","核心联系人","状态","类别"};
String[] title={"name","cc_name","state","fi_tpye"};
ExcelUtils2.createExcel_OneSheet(fileDir, sheetName, title);
ExcelUtils2.writeToExcel_OneSheet(fileDir, sheetName, selectExportInfo, title);
String fileName=fileDir.substring(fileDir.lastIndexOf("/")+1);
byte[] buffer=null;
buffer = ExcelUtils.downFileByte(fileDir) ;
String fileSuffixName= fileName.substring(fileName.lastIndexOf(".")+1);
response.reset(); //清除缓存
response.setContentType("application/" +fileSuffixName + ";" +"charset = UTF-8"); //设置字符集和文件后缀名
String name="导出信息";
name = new String(name.getBytes(), "ISO-8859-1");
response.setHeader("Content-Disposition","attachment; filename=" +name+"."+fileSuffixName); // 设置文件名称
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (Exception e) {
e.printStackTrace();
Log4jUtil.getLog4jUtil().error("导出信息异常"+e.getMessage());
}
}
/**
* 创建一个新excel. 一个sheet
*
* @param fileDir
* excel的路径
* @param sheetName
* 要创建的表格索引
* @param titleRow
* excel的第一行即表格头
*/
public static void createExcel_OneSheet(String fileDir, String sheetName, String titleRow[]) throws Exception {
// 创建workbook
workbook = new HSSFWorkbook();
// 添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
HSSFSheet sheet1 = workbook.createSheet(sheetName);
// 新建文件
FileOutputStream out = null;
try {
// 添加表头
HSSFRow row = workbook.getSheet(sheetName).createRow(0); // 创建第一行
for (short i = 0; i < titleRow.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 往excel中写入(已存在的数据无法写入).
*
* @param fileDir
* 文件路径
* @param sheetName
* 表格索引
* @param mapList
* 需要写入的数据
* @param titleRow[]
* excel的第一行即表格头
* @throws Exception
*/
public static void writeToExcel_OneSheet(String fileDir, String sheetName, List<Map<String, Object>> mapList,String titleRow[]) throws Exception {
OutputStream out = null;
// 创建workbook
File file = new File(fileDir);
Workbook workBook = getWorkbok(file);
Sheet sheet = workBook.getSheet(sheetName);
// 删除原有数据,除了属性列
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
Row row0 = sheet.getRow(0);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
sheet.removeRow(row);
}
// 往Excel中写新数据
for (int j = 0; j < mapList.size(); j++) {
// 创建一行:从第二行开始,跳过属性列
Row row = sheet.createRow(j + 1);
Map<String, Object> dataMap = mapList.get(j);
System.out.println(dataMap);
int count = row0.getLastCellNum();
for (int k = 0; k <count; k++) {
for (int i = 0; i < titleRow.length; i++) {
Cell cell = row.createCell(k);
if (dataMap.get(titleRow[k])!=null) {
cell.setCellValue(dataMap.get(titleRow[k]).toString());
}
}
}
}
// 创建文件输出流,准备输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(fileDir);
workBook.write(out);
}
/**
* 下载文件
* 返回byte[]
* @param fileName 需要下载的文件名
* @return
* @throws Exception
*/
public static byte[] downFileByte(String downLoadPath) throws Exception{
byte[] return_arraybyte=null;
InputStream ins=new FileInputStream(downLoadPath );
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
byte[] buf = new byte[1024];
int bufsize = 0;
while ((bufsize = ins.read(buf, 0, buf.length)) != -1) {
byteOut.write(buf, 0, bufsize);
}
return_arraybyte = byteOut.toByteArray();
byteOut.close();
ins.close();
return return_arraybyte;
}
/**
* 根据业务查询需要下载的信息
* @param fi_ids
* @return
* @throws Exception
*/
@Override
public List<Map<String, Object>> selectExportInfo(String[] fi_ids) throws Exception {
String join = StringUtils.join(fi_ids, ",");
List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(" SELECT f.name,c.cc_name,f.state,f.fi_tpye from tb_firm_info f LEFT JOIN tb_industry_commerce i on f.fi_id=i.fi_id LEFT JOIN (SELECT * from tb_core_contacts GROUP BY fi_id) c on f.fi_id=c.fi_id where FIND_IN_SET(f.fi_id,'"+join+"') ");
return queryForList;
}