去掉了一些敏感数据,仅供参考
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
import org.apache.commons.logging.Log;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
/**
-
公文历史数据导入
-
TODO
-
@Date 2018-12-1 上午10:57:08
*/
@Controller
@RequestMapping("/archives/out")
public class ImportData {
private static Log log = LogUtil.getLog();/**
- 跳转导入页面
- @param request
- @param model
- @return
*/
@RequestMapping(value="/importData",method = RequestMethod.GET)
public String importData(HttpServletRequest request, Model model){
return “/module/archives/out/archive_excelimp”;
}
@RequestMapping(value = “/upload”, method = RequestMethod.POST)
@ResponseBody
public String readExcel(HttpServletRequest request,HttpServletResponse response,
@RequestParam(“importFile”)MultipartFile file,
@RequestParam(“filePath”)String filePath)
throws IOException, IllegalArgumentException, SecurityException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {
filePath = URLDecoder.decode(filePath, “UTF-8”);//转码
String message = “”;
String fileName = file.getOriginalFilename();
String zipPath = request.getSession().getServletContext().getRealPath(
“/”)
+ “/upload/archives/tempfile/”;
log.info("==》存放zip文件的路径" + zipPath);
zipPath = zipPath.replace("\", “/”);
File filepath = new File(zipPath);
if(!filepath.isDirectory()){
filepath.mkdirs();//建立多级文件夹
}
File targetFile = new File(zipPath, fileName); //上传的zip文件
try {
file.transferTo(targetFile); //传送 失败就抛异常
} catch (Exception e) {
message = “导入失败!”;
e.printStackTrace();
}if( ImportFileUtil.unzip(targetFile)){ log.info(targetFile+"解压成功!"); } String unZipPath = zipPath + fileName.substring(0, fileName.lastIndexOf(".")); File fileUnZip = new File(unZipPath); if(fileUnZip.exists()){ if (fileUnZip.isDirectory()) {//判断是不是文件夹 String[] unZipList = fileUnZip.list();//文件夹的所有的文件 if(unZipList.length>0){ for(int n=0;n<unZipList.length;n++){//循环文件夹下的文件 String unZipFile = new String (unZipList[n].getBytes("UTF-8"),"UTF-8");; File unzip = new File(unZipPath + unZipFile); String fileExt = unZipFile; if (!unzip.isDirectory()){//查找excel文件 fileExt =fileExt.substring(fileExt.lastIndexOf(".")+1); if(fileExt.equals("xls") || fileExt.equals("xlsx")){ log.info("excel文件" + unZipFile + "路径:"+fileUnZip); File unzipFile = new File(fileUnZip + File.separator + unZipFile); message = readExcelOfOutAchives(unzipFile,unZipPath); break; }else{ message = "上传的压缩包未包含excel文件"; } } } } } ImportFileUtil.delFolder(unZipPath);//删除解压的文件夹及其子文件 }else{//当解压文件直接解压到目录下,而不是压缩包名文件夹下时,查找压缩包所在文件夹下是否有excel文件,如果没有就提示; File zipFilePath = new File(zipPath); if (zipFilePath.isDirectory()) {//判断是不是文件夹 String[] unZipList = zipFilePath.list();//文件夹的所有的文件 if(unZipList.length>0){ for(int n=0;n<unZipList.length;n++){//循环文件夹下的文件 String unZipFile = new String (unZipList[n].getBytes("UTF-8"),"UTF-8");; File unzip = new File(zipPath + unZipFile); String fileExt = unZipFile; if (!unzip.isDirectory()){//查找excel文件 fileExt =fileExt.substring(fileExt.lastIndexOf(".")+1); if(fileExt.equals("xls") || fileExt.equals("xlsx")){ log.info("excel文件" + unZipFile + "路径:"+zipFilePath); File unzipFile = new File(zipFilePath + File.separator + unZipFile); message = readExcelOfOutAchives(unzipFile,zipPath); break; }else{ message = "上传的压缩包未包含excel文件"; } } } } } ImportFileUtil.delFolder(zipPath);//删除解压的文件夹及其子文件 } targetFile.delete();//删除压缩文件 if(message!=null){ if(message.length()==0){ message = "导入成功!"; } }else{ message="数据格式错误,导入终止!"; } try { JSONObject jsonObject = new JSONObject(); jsonObject.put("mes", message); response.setCharacterEncoding("GBK"); response.setContentType("text/html;charset=GBK"); response.getWriter().print(jsonObject); } catch (IOException e) { e.printStackTrace(); } return null;
}
/**
- 读取Excel表格
*/
public String readExcelOfOutAchives(File file,String filePath) throws IllegalArgumentException, IllegalAccessException, InstantiationException, SecurityException, NoSuchMethodException, InvocationTargetException, IOException {
String s="";
int errorInRow = 0;
IdToName inToName = new IdToName();
List <Map<String,String>> pathList = new ArrayList<Map<String, String>> ();
try {
Workbook workBook = null;
InputStream in = new FileInputStream(file);;
String fileName =file.getName();
workBook = ImportFileUtil.getWorkBook(in,fileName);
// 创建工作表
Sheet sheet = workBook.getSheetAt(0);
IArchivesZXOutService service = (IArchivesZXOutService) SpringBeanUtil.getBean(“ArchivesZXOutService”);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
SimpleDateFormat sdf = new SimpleDateFormat(“yyyyMMddHHmmssSSS”);
if (rows > 1) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 1; r < rows; r++) {// 行循环
Map <String , String> map = new HashMap<String , String>();//用于保存标题和签发时间,每行保存一次
Row row = sheet.getRow®; //获取行
ArchivesOut archivesOut = new ArchivesOut();
Date date = new Date();
String timeStr=Long.toString(System.currentTimeMillis());//时间戳用于content的值(毫秒数)
archivesOut.setOutId(“OLDnotesHis_out” + sdf.format(date));//时间可以清楚的读出来,是当前时间精确到毫秒数的字符串
archivesOut.setFlowType(“FW”);
archivesOut.setContent(“SIarchive” + timeStr);
archivesOut.setContent2(“zhenwenshuoming” + timeStr);
archivesOut.setContent3(“zhengshiSIarchive” + timeStr);
archivesOut.setContent4(“gaozhi” + timeStr);
archivesOut.setOutType(“JUFAWEN”);
archivesOut.setZhbDepartmentId(“0”);
if(row == null){
rows++;
continue;
}
String title = ImportFileUtil.getRowCellValue(row,(short)0);//标题
if("".equals(title)){
break;
}else{
archivesOut.setTitle(title);
}
archivesOut.setSignName(ImportFileUtil.getRowCellValue(row,(short)2));//签发人
String signDate = ImportFileUtil.getRowCellValue(row,(short)1);//签发时间,转换为年.月.日的格式
archivesOut.setSignDate(signDate);
String formatDate = “”;
if(!"".equals(signDate)){
String month = signDate.substring(5,6);
String day = signDate.substring(8,9);
if(month.equals(“0”)){
month = signDate.substring(6,7);
}else{
month = signDate.substring(5,7);
}
if(day.equals(“0”)){
day = signDate.substring(9);
}else{
day = signDate.substring(8);
}
formatDate = signDate.substring(0,4)+"."+month+"."+day;
}
String needGrade = ImportFileUtil.getRowCellValue(row,(short)3);//缓急
if(!"".equals(needGrade)){
if(needGrade.replace(" “, “”).equals(“急件”)){
needGrade = “01”;
}else if(needGrade.replace(” “, “”).equals(“特急”)){
needGrade = “02”;
}else{
needGrade = “03”;
}
}
archivesOut.setNeedGrade(needGrade);
archivesOut.setSecGrade(“01”);
archivesOut.setZhsOffice(ImportFileUtil.getRowCellValue(row,(short)5));
archivesOut.setChsOffice(ImportFileUtil.getRowCellValue(row,(short)6));
//增加获取拟稿单位id的功能
String ngName = ImportFileUtil.getRowCellValue(row,(short)7).replace(” “, “”);
String ngId = “”;
ngId = inToName.getOneKeyWordByWhere(“deptid”, “flow_dept”, " super_id=‘001’ and status=‘1’ and deptname = '” + ngName + “’”);//根据拟稿单位名称获取拟稿单位的id,保存到业务表拟稿单位id字段
archivesOut.setNgDepartmentName(ngId);
archivesOut.setCreUserName(ImportFileUtil.getRowCellValue(row,(short)8) + " " + signDate);
archivesOut.setZhbDepartmentName(ngName);//主办单位存拟稿单位的名称和id(excel表中的发文单位不存了)
archivesOut.setZhbDepartmentId(ngId);
String fileType = “”;
String fileTypeStr = ImportFileUtil.getRowCellValue(row,(short)10);
if(!"".equals(fileTypeStr)){
fileTypeStr = fileTypeStr.replace(" “, “”);
if(“决定”.equals(fileTypeStr)){
fileType = “02”;
}else if(“通告”.equals(fileTypeStr)){
fileType = “03”;
}else if(“通知”.equals(fileTypeStr)){
fileType = “04”;
}else if(“通报”.equals(fileTypeStr)){
fileType = “05”;
}else if(“报告”.equals(fileTypeStr)){
fileType = “06”;
}else if(“请示”.equals(fileTypeStr)){
fileType = “07”;
}else if(“批复”.equals(fileTypeStr)){
fileType = “08”;
}else if(“意见”.equals(fileTypeStr)){
fileType = “09”;
}else if(“函”.equals(fileTypeStr)){
fileType = “10”;
}else if(“公告”.equals(fileTypeStr)){
fileType = “12”;
}else if(“决议”.equals(fileTypeStr)){
fileType = “13”;
}else if(“公报”.equals(fileTypeStr)){
fileType = “14”;
}else if(“议案”.equals(fileTypeStr)){
fileType = “15”;
}else if(“命令(令)”.equals(fileTypeStr)){
fileType = “16”;
}else if(“纪要”.equals(fileTypeStr)){
fileType = “17”;
}
}
archivesOut.setFileType(fileType);//文种
String officeSign = “”;
if(!”".equals(ImportFileUtil.getRowCellValue(row,(short)11))){
officeSign = “01”;
}
archivesOut.setOfficeSign(officeSign);
archivesOut.setSubject(ImportFileUtil.getRowCellValue(row,(short)12));
String creDate = ImportFileUtil.getDateCellValue(row,(short)13).trim();//创建时间保存到时分,格式化为 yyyy-MM-dd HH:mm
archivesOut.setCreDate(creDate);
String year = “”;
if(!"".equals(creDate)){
year = creDate.substring(0,4);
}
archivesOut.setYear(year);
archivesOut.setFileCode(ImportFileUtil.getRowCellValue(row,(short)14));
map.put(“fileName”, title+formatDate);//文件的路径名称
map.put(“table_id”,archivesOut.getOutId());
map.put(“cre_user”,ImportFileUtil.getRowCellValue(row,(short)8));
map.put(“cre_time”,archivesOut.getCreDate());
map.put(“content”,archivesOut.getContent());
pathList.add(map);
service.saveArchive(archivesOut);
errorInRow = r + 2;
}
//导入正文原文和附件
saveFiles(filePath,pathList);
}else{
s = “导入的Excel文件数据为空!”;
}
}catch (Exception ex) {
s = “第” + errorInRow + “行之前的数据已导入成功!由于第” + errorInRow + “行的数据长度不符合规定,导致本行(包括本行)之后的数据导入失败!请仔细检查本行数据!”;
ex.getMessage();
ex.printStackTrace();
}
return s;
}
/**
-
保存公文的正文原文和附件
-
TODO
-
@Date 2018-12-4 下午05:39:35
-
@param filePath
-
@param pathList
*/
@SuppressWarnings(“unchecked”)
public void saveFiles(String filePath,List<Map<String,String>> pathList){
if(filePath.indexOf("\") != -1){
//路径的转换
filePath = filePath.replace("\",File.separator);
log.info(“导入文件的真实路径为==>” + filePath);
}
//String fileCode=(String)System.getProperties().get(“file.encoding”);获取默认的编码方式
for(int i=0;i<pathList.size();i++){//每个公文循环
Map map = pathList.get(i);
Map affixMap = new HashMap();
//附件map
affixMap.put(“table_id”,map.get(“table_id”));
affixMap.put(“cre_user”,map.get(“cre_user”));
affixMap.put(“cre_time”,map.get(“cre_time”));
affixMap.put(“save_type”, “1”);String filename = (String) map.get("fileName"); String path = filePath + File.separator + filename; String Zwyw = path + File.separator + "zwyw"; String affix = path + File.separator + "affix"; File fileZw = new File(Zwyw); log.info("文件名" +i + filename); System.getProperties().get("file.encoding"); File fileAffix = new File(affix); //附件导入 if(fileAffix.exists()){ if (fileAffix.isDirectory()) {//判断是不是文件夹 String[] fileList = fileAffix.list();//文件夹的所有的文件 if(fileList.length>0){ for(int j=0;j<fileList.length;j++){//循环文件夹下的文件 //String fileName = fileList[j]; try { fileList[j] = new String (fileList[j].getBytes("UTF-8"),"UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } String affixPath = fileAffix + File.separator + fileList[j]; String fileExt=fileList[j];//文件的扩展名; fileExt =fileExt.substring(fileExt.lastIndexOf(".")+1); affixMap.put("table_name", "/ftp/tempfile/FW/addFile/"); affixMap.put("FILE_TYPE",fileExt); affixMap.put("FILE_NAME",fileList[j]); affixMap.put("TITLE",fileList[j]); AffixTool affixTool = new AffixTool(); affixTool.saveToDbAboutFile(affixMap, affixPath); } } } } //正文原文导入 if(fileZw.exists()){ if(fileZw.exists()){ if (fileZw.isDirectory()) {//判断是不是文件夹 String[] zwList = fileZw.list();//文件夹的所有的文件 if(zwList.length>0){ for(int n=0;n<zwList.length;n++){//循环文件夹下的文件 //String fileName = zwList[n]; try { zwList[n] = new String (zwList[n].getBytes("UTF-8"),"UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } String affixPath = fileZw + File.separator + zwList[n]; String fileExt=zwList[n];//文件的扩展名; fileExt =fileExt.substring(fileExt.lastIndexOf(".")+1); affixMap.put("table_name", "/ftp/tempfile/FW/body_doc/"); affixMap.put("FILE_TYPE",fileExt); affixMap.put("FILE_NAME",zwList[n]); affixMap.put("TITLE",zwList[n]); AffixTool affixTool = new AffixTool(); affixTool.saveToDbAboutFile(affixMap, affixPath); } } } } }
}
}
}