该博客主要展示了Java代码实现的数据处理和文件格式转换功能。包括根据项目名解析数据并以邮件形式发送文件，根据时间获取所在周日期和周数，还实现了DWG转Excel、PDF转Word等文件格式转换操作。
package org.jeecg.modules.materialExcel.service.impl;
import com.aliyun.oss.ServiceException;
import com.aspose.pdf.Document;
import com.aspose.pdf.Page;
import com.aspose.pdf.SaveFormat;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.;
import org.jeecg.common.api.vo.Result;
import org.jeecg.config.vo.Path;
import org.jeecg.modules.attachment.entity.SysAttachmentManagement;
import org.jeecg.modules.materialExcel.entity.UMaterialExcel;
import org.jeecg.modules.materialExcel.mapper.UMaterialExcelMapper;
import org.jeecg.modules.materialExcel.service.IUMaterialExcelService;
import org.jeecg.modules.materialExcel.utils.SendEmail;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.file.Paths;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.;
import java.util.zip.CRC32;
import java.util.zip.CheckedOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import static org.springframework.util.FileCopyUtils.BUFFER_SIZE;
@Service
public class UMaterialExcelServiceImpl extends ServiceImpl<UMaterialExcelMapper, UMaterialExcel> implements IUMaterialExcelService {
@Autowired
private UMaterialExcelMapper uMaterialExcelMapper;
@Autowired
private SendEmail sendEmail;
@Override
@Transactional
public Result<?> importByExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
String project = request.getParameter("project");
if(StringUtils.isBlank(project)){
throw new Exception("请输入导出项目");
}
MultipartFile file = fileMap.get("file");
if (file == null) {
return Result.error("文件上传失败！");
}
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
XSSFSheet sheet = null;
if (workbook != null && (sheet = workbook.getSheetAt(0)) != null) {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
if (sheet.getLastRowNum() >= 3) {
XSSFRow row0 = sheet.getRow(0);//获取标题行
XSSFRow row2 = sheet.getRow(1);//获取标题行
int columnNum = 0; //获取行里的总列数
HashSet<String> titles = new HashSet<>();//标题列表
HashMap<Integer, String> columnsMap = new HashMap<>();
HashMap<Integer, String> columnsDateMap = new HashMap<>();
if (row0 != null && (columnNum = row0.getPhysicalNumberOfCells()) > 0) {//列大于0
HashMap<String, String> titlesColumnMap = new HashMap<>();//自定义标题列表对应字段
titlesColumnMap.put("Supplier", "supplier");
titlesColumnMap.put("SKU", "sku");
titlesColumnMap.put("Part#", "part");
titlesColumnMap.put("GTK PN", "gtkpn");
titlesColumnMap.put("Description", "description");
titlesColumnMap.put("Ship To", "shipto");
titlesColumnMap.put("Transit Mode", "transitmode");
//从5行，第7列获取表头信息
for (int i = 0; i < columnNum; i++) {
if(i < 5){
//从第2行，获取表头数据
XSSFCell cell = row2.getCell(i);
if(cell != null){
String title = cell.getStringCellValue();
if(StringUtils.isNotBlank(title) && !titles.contains(title)) {//判断表头字段是否存在重复
titles.add(title);
String invokeMethod = titlesColumnMap.get(title);
if (StringUtils.isNotBlank(invokeMethod) ) {
invokeMethod = "set"+invokeMethod.substring(0, 1).toUpperCase() + invokeMethod.substring(1);
columnsMap.put(i,invokeMethod);
}
}
}
}else{
//从第6行获取表头数据
XSSFCell cell = row0.getCell(i);
if(cell != null){
//0代表是数字类型
int a = cell.getCellType();
if(cell.getCellType() == 0 || cell.getCellType() == 2){
String title = dateFormat.format(cell.getDateCellValue());
columnsDateMap.put(i,title);
}else{
String stringCellValue = cell.getStringCellValue();
if(StringUtils.isNotBlank(stringCellValue)){
columnsDateMap.put(i,stringCellValue);
}
}
}
}
}
}
Class<? extends UMaterialExcel> classType = UMaterialExcel.class;
int lastRowNum = sheet.getLastRowNum();
ArrayList<UMaterialExcel> insertList = new ArrayList<>();
for(int m = 2; m <= lastRowNum; m++) {//从第6行开始读取数据
XSSFRow rowData = sheet.getRow(m);//获取标题行
UMaterialExcel uMaterialExcel = new UMaterialExcel();
for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
XSSFCell cell = rowData.getCell(column.getKey());
String invokeMethod = column.getValue();
if (cell != null){
cell.setCellType(CellType.STRING);
Method declaredMethod = classType.getMethod(invokeMethod, String.class);
if (cell.getCellType() == 0) {
declaredMethod.invoke(uMaterialExcel, String.valueOf(cell.getNumericCellValue()));
} else {
declaredMethod.invoke(uMaterialExcel, cell.getStringCellValue());
}
}
}
for (Map.Entry<Integer, String> column : columnsDateMap.entrySet()) {
XSSFCell cell = rowData.getCell(column.getKey());
String date = column.getValue();
if (cell != null) {
String contentData = null;
if(cell.getCellType() == 0 || cell.getCellType() == 2){
contentData = new BigDecimal(cell.getNumericCellValue()).toString();
}else {
contentData = cell.getStringCellValue();
}
if(contentData != null){
UMaterialExcel materialExcel = new UMaterialExcel();
BeanUtils.copyProperties(uMaterialExcel, materialExcel);
materialExcel.setContentdate(date);
materialExcel.setContentdata(contentData);
//本周周一
materialExcel.setImportdates(dateFormat.format(getWeekDay()[1]));
//上周五
String zhouEr = dateFormat.format(getWeekDay()[1]);
Calendar calendar = Calendar.getInstance();
calendar.setTime(dateFormat.parse(zhouEr));
calendar.set(Calendar.DATE, calendar.get(Calendar.DATE) - 4);
String zhouWu = dateFormat.format(calendar.getTime());
materialExcel.setImportdate(zhouWu);
//第几周
materialExcel.setWeeknum(getWeeksInMonthOfDate(dateFormat.format(new Date())));
//表头日期是第几周
materialExcel.setTitleweek(getWeeksInMonthOfDate(date));
materialExcel.setProject(project);
insertList.add(materialExcel);
}
}
}
}
List<String> importDate = new ArrayList<>();
for (UMaterialExcel uMaterialExcel : insertList) {
if (!importDate.contains(uMaterialExcel.getImportdate())){
importDate.add(uMaterialExcel.getImportdate());
}
}
QueryWrapper<UMaterialExcel> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("importdate",importDate.get(0))
.eq("project",project);
long count = super.count();
if (count>0){
super.remove(queryWrapper);
}
super.saveBatch(insertList);
return Result.OK();
}else {
return Result.OK();
}
}
return Result.error("文件导入失败！");
}
@Override
public Result<?> exportXlsx(String project, HttpServletRequest request, HttpServletResponse response) {
String projectName = project;
if(StringUtils.isBlank(projectName)){
return Result.error("请选择导出项目");
}
// List uMaterialExcels = uMaterialExcelMapper.list(projectName);
// List uMaterialExcelsG = uMaterialExcelMapper.listG(projectName);
// List uMaterialExcelsTotal = uMaterialExcelMapper.listTotal(projectName);
// List<List> listList = Arrays.asList(uMaterialExcels,uMaterialExcelsG,uMaterialExcelsTotal);
if (projectName!=null){
new Thread(()->{
String projectNameTh = projectName;
excelExportXlsx(projectNameTh);
}).start();
return Result.OK(“数据解析解析中…，文件将以邮件形式发送”);
}else {
return Result.error(“无数据！”);
}
}
@Override
public Result<?> dataConversion(HttpServletRequest request, HttpServletResponse response) throws Exception {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
String project = request.getParameter("project");
if(StringUtils.isBlank(project)){
throw new Exception("请输入导出项目");
}
MultipartFile file = fileMap.get("file");
if (file == null) {
return Result.error("文件上传失败！");
}
int hang = 0;
int sheetNum = 0;
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
if (workbook != null){
int numberOfSheets = workbook.getNumberOfSheets();
XSSFSheet sheet = null;
for(int j = 0; j<numberOfSheets; j++){
sheetNum++;
System.out.println("sheet页"+sheetNum);
if ((sheet = workbook.getSheetAt(j)) != null) {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
if (sheet.getLastRowNum() >= 3) {
XSSFRow row3 = sheet.getRow(3);//获取标题行
int columnNum = 0; //获取行里的总列数
HashSet<String> titles = new HashSet<>();//标题列表
HashMap<Integer, String> columnsMap = new HashMap<>();
HashMap<Integer, String> columnsDateMap = new HashMap<>();
if (row3 != null && (columnNum = row3.getPhysicalNumberOfCells()) > 0) {//列大于0
HashMap<String, String> titlesColumnMap = new HashMap<>();//自定义标题列表对应字段
titlesColumnMap.put("Supplier", "supplier");
titlesColumnMap.put("GTK PN", "gtkpn");
titlesColumnMap.put("SKU", "sku");
titlesColumnMap.put("Description", "description");
titlesColumnMap.put("Part#", "part");
titlesColumnMap.put("Exfactory release date(cut off Sat by week)", "importdate");
titlesColumnMap.put("MRP running date", "importdates");
titlesColumnMap.put("周别", "weeknum");
//从5行，第7列获取表头信息
for (int i = 0; i < columnNum; i++) {
if(i < 8){
//从第3行，获取表头数据
XSSFCell cell = row3.getCell(i);
if(cell != null){
String title = cell.getStringCellValue();
if(StringUtils.isNotBlank(title) && !titles.contains(title)) {//判断表头字段是否存在重复
titles.add(title);
String invokeMethod = titlesColumnMap.get(title);
if (StringUtils.isNotBlank(invokeMethod) ) {
invokeMethod = "set"+invokeMethod.substring(0, 1).toUpperCase() + invokeMethod.substring(1);
columnsMap.put(i,invokeMethod);
}
}
}
}else{
XSSFCell cell = row3.getCell(i);
if(cell != null){
//0代表是数字类型
int a = cell.getCellType();
if(cell.getCellType() == 0 || cell.getCellType() == 2){
String title = dateFormat.format(cell.getDateCellValue());
columnsDateMap.put(i,title);
}else{
String stringCellValue = cell.getStringCellValue();
if(StringUtils.isNotBlank(stringCellValue)){
columnsDateMap.put(i,stringCellValue);
}
}
}
}
}
}
Class<? extends UMaterialExcel> classType = UMaterialExcel.class;
int lastRowNum = sheet.getLastRowNum();
ArrayList<UMaterialExcel> insertList = new ArrayList<>();
for(int m = 4; m <= lastRowNum; m++) {//从第5行开始读取数据
hang++;
System.out.println("行："+hang);
XSSFRow rowData = sheet.getRow(m);//获取标题行
UMaterialExcel uMaterialExcel = new UMaterialExcel();
for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
XSSFCell cell = rowData.getCell(column.getKey());
String invokeMethod = column.getValue();
if (cell != null){
//cell.setCellType(CellType.STRING);
int b = cell.getCellType();
Method declaredMethod = classType.getMethod(invokeMethod, String.class);
if (cell.getCellType() == 1) {
declaredMethod.invoke(uMaterialExcel, cell.getStringCellValue());
}
else {
declaredMethod.invoke(uMaterialExcel, dateFormat.format(cell.getDateCellValue()));
}
}
}
for (Map.Entry<Integer, String> column : columnsDateMap.entrySet()) {
XSSFCell cell = rowData.getCell(column.getKey());
String date = column.getValue();
if (cell != null) {
String contentData = null;
if(cell.getCellType() == 0 || cell.getCellType() == 2){
contentData = new BigDecimal(cell.getNumericCellValue()).toString();
}else {
contentData = cell.getStringCellValue();
}
if(contentData != null){
UMaterialExcel materialExcel = new UMaterialExcel();
BeanUtils.copyProperties(uMaterialExcel, materialExcel);
materialExcel.setContentdate(date);
materialExcel.setContentdata(contentData);
//表头日期是第几周
materialExcel.setTitleweek(getWeeksInMonthOfDate(date));
materialExcel.setProject(project);
insertList.add(materialExcel);
}
}
}
}
super.saveBatch(insertList);
}else {
return Result.error("导入失败!!!"+"第"+sheetNum+"个sheet页，第"+hang+"行数据错误");
}
}
}
return Result.OK();
}
return Result.error("导入失败!!!"+"第"+sheetNum+"个sheet页，第"+hang+"行数据错误");
}
@Override
public Result<?> pdfToExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
String fileStyle = request.getParameter("fileStyle");
String pageValue = request.getParameter("pageValue");
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
MultipartFile file = fileMap.get("file");
if (fileStyle.equals("1")){
pdf2excel(file,response);
}else if (fileStyle.equals("2")){
pdf2word(file,response);
}else if (fileStyle.equals("3")){
pdf2ppt(file,response);
}else if(fileStyle.equals("4")){
pdfChai(file,response);
}else if(fileStyle.equals("5")){
pdfChaiZ(file,pageValue,response);
}
return Result.OK("转换成功");
}
@Override
public List<String> getProject() {
List<String> projectList = new ArrayList<>();
List<Map<String,String>> project = uMaterialExcelMapper.getProject();
if (project != null && project.size()>0){
for (Map<String, String> map : project) {
projectList.add(map.get("PROJECT"));
}
// projectList = project.stream()
// .map(m ->
// m.get(“project”)
// )
// .collect(Collectors.toList());
}
return projectList;
}
//根据当前时间获取其所在周的周一至周日的时间
public static Date[] getWeekDay() {
Calendar calendar = Calendar.getInstance();
while (calendar.get(Calendar.DAY_OF_WEEK) != Calendar.MONDAY) {
calendar.add(Calendar.DAY_OF_WEEK, -1);
}
Date[] dates = new Date[7]; // new Date[7] 星期日
for (int i = 0; i < 7; i++) { // i < 7 星期日
dates[i] = calendar.getTime();
calendar.add(Calendar.DATE, 1);
}
return dates;
}
//根据时间获取是第几周
public static String getWeeksInMonthOfDate(String dateStr){
SimpleDateFormat df=new SimpleDateFormat(“yyyy-MM-dd”);
Calendar calendar = Calendar.getInstance();
try {
calendar.setTime(df.parse(dateStr));
} catch (ParseException e) {
e.printStackTrace();
}
//设置每周第一天为周一 默认每周第一天为周日
calendar.setFirstDayOfWeek(Calendar.MONDAY);
//设置一周的最小天数
calendar.setMinimalDaysInFirstWeek(4);
return String.valueOf(calendar.get(Calendar.YEAR)).substring(2).concat(“W”).
concat(String.valueOf(String.format(“%02d”,calendar.get(Calendar.WEEK_OF_YEAR))));
}
/*
* 列头单元格样式
*/
public CellStyle getColumnTopStyle(XSSFWorkbook workbook) {
CellStyle cellStyleTitle = workbook.createCellStyle();
cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyleTitle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//单元格颜色
cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyleTitle.setAlignment(HorizontalAlignment.LEFT);//水平居中
cellStyleTitle.setWrapText(true);//自动换行
Font fontTitle = workbook.createFont();
fontTitle.setFontHeightInPoints((short)15);
fontTitle.setBold(true);
cellStyleTitle.setFont(fontTitle);
return cellStyleTitle;
}
public XSSFCellStyle getCellStyle(XSSFWorkbook workbook){
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
return cellStyle;
}
public void excelExportXlsx(String projectName){
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.setForceFormulaRecalculation(true);// 执行公式
List<UMaterialExcel> uMaterialExcelPn = uMaterialExcelMapper.gtkPn(projectName);
if (uMaterialExcelPn!=null && uMaterialExcelPn.size()>0) {
for (UMaterialExcel uMaterialExcel : uMaterialExcelPn) {
//封装数据
LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
String gtkPn = uMaterialExcel.getGtkpn();
List<UMaterialExcel> uMaterialExcels = uMaterialExcelMapper.gtkPnWlList(projectName, gtkPn);
batchData(uMaterialExcels,projectName,workbook,titleMap,contentDateMap);
}
}
List<UMaterialExcel> uMaterialExcelGb = uMaterialExcelMapper.gb(projectName);
if (uMaterialExcelGb!=null && uMaterialExcelGb.size()>0){
for (UMaterialExcel uMaterialExcelg : uMaterialExcelGb) {
//封装数据
LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
String gb = uMaterialExcelg.getGtkpn();
List<UMaterialExcel> uMaterialExcelsG = uMaterialExcelMapper.listG(projectName,gb);
batchData(uMaterialExcelsG,projectName,workbook,titleMap,contentDateMap);
}
}
List<UMaterialExcel> uMaterialExcelsTotal = uMaterialExcelMapper.listTotal(projectName);
if (uMaterialExcelsTotal!=null && uMaterialExcelsTotal.size()>0){
//封装数据
LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
batchData(uMaterialExcelsTotal,projectName,workbook,titleMap,contentDateMap);
}
String projectNameEmail = projectName;
String path = "C:/infor/"+projectNameEmail+".xlsx";
File file = new File(path);
//如果已经存在则删除
if (file.exists()) {
file.delete();
}
//检查父包是否存在
File parentFile = file.getParentFile();
if (!parentFile.exists()) {
parentFile.mkdirs();
}
//创建文件
try(FileOutputStream out = new FileOutputStream(path)){
file.createNewFile();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
sendEmail.mailSendApply(projectNameEmail);
}
public void dataEncapsulation(String projectName,XSSFWorkbook workbook,HashMap<String, ArrayList<String>> titleMap,LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap){
//设置单元格样式
CellStyle cellStyleTitle = workbook.createCellStyle();
cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyleTitle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());//单元格颜色
cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyleTitle.setWrapText(true);//自动换行
Font fontTitle = workbook.createFont();
fontTitle.setFontHeightInPoints((short) 8);
fontTitle.setBold(true);
cellStyleTitle.setFont(fontTitle);
//设置单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
for (Map.Entry<String, LinkedHashMap<String, HashMap<String, String>>> mapEntry : contentDateMap.entrySet()) {
XSSFSheet sheet = workbook.createSheet(mapEntry.getKey());
//冻结前两行
sheet.createFreezePane(0,2,0,2);
//设置密级
sheet.getHeader().setRight("文档密级：Confidential");
ArrayList<String> list = titleMap.get(mapEntry.getKey());
int columnIndex = 1;
int titleIndex = 0;
//最开始的一行放一个特殊标头
//是物料号的才查，物料号的长度大于10
String message = "";
if (mapEntry.getKey().trim().length() >= 11) {
List<Map<String, String>> titleBaseMeList = uMaterialExcelMapper.getTitleBaseMe(mapEntry.getKey());
message = titleBaseMeList.get(0).get("MESSAGE");
}
CellRangeAddress regionB = new CellRangeAddress(0, 0, 0, 6);
sheet.addMergedRegion(regionB);
XSSFRow rowHeadTitle = sheet.createRow(0);
XSSFCell cellHead = rowHeadTitle.createCell(0);
cellHead.setCellValue(projectName + "-" + message);
cellHead.setCellStyle(getColumnTopStyle(workbook));
//表头对应数据查询字段
XSSFRow rowTitle = sheet.createRow(columnIndex++);
ArrayList<String> keyList = new ArrayList<>();
List<String> titleList = Arrays.asList("importdate", "importdates", "weeknum");
sheet.setColumnWidth(titleIndex, 2500);
XSSFCell cell0 = rowTitle.createCell(titleIndex++);
cell0.setCellStyle(cellStyleTitle);
cell0.setCellValue("Exfactory");
keyList.add("importdate");
sheet.setColumnWidth(titleIndex, 2500);
XSSFCell cell1 = rowTitle.createCell(titleIndex++);
cell1.setCellStyle(cellStyleTitle);
cell1.setCellValue("MRP running date");
keyList.add("importdates");
sheet.setColumnWidth(titleIndex, 4000);
XSSFCell cell2 = rowTitle.createCell(titleIndex++);
cell2.setCellStyle(cellStyleTitle);
cell2.setCellValue("周别");
keyList.add("weeknum");
for (String s : list) {
sheet.setColumnWidth(titleIndex, 4000);
XSSFCell cell = rowTitle.createCell(titleIndex++);
cell.setCellStyle(cellStyleTitle);
cell.setCellValue(s);
keyList.add(s);
}
// XSSFCell cellFinish = rowTitle.createCell(titleIndex++);
// cellFinish.setCellStyle(cellStyleTitle);
// cellFinish.setCellValue(“Total”);
for (Map.Entry<String, HashMap<String, String>> entry : mapEntry.getValue().entrySet()) {
XSSFRow row = sheet.createRow(columnIndex++);
HashMap<String, String> value = entry.getValue();
int rIndex = 0;
for (String s : keyList) {
XSSFCell cell = row.createCell(rIndex++);
if (titleList.contains(s)) {
cell.setCellValue(value.get(s));
} else {
cell.setCellValue(Double.valueOf(value.get(s) == null ? "0" : value.get(s)));
}
cell.setCellStyle(cellStyle);
}
// XSSFRow rowTotal = sheet.createRow(columnIndexTotal++);
// int rIndexTotal = 3;
// for (String s : keyList) {
// for (int i=3;i<columnIndexTotal;i++){
// XSSFRow row1 = sheet.getRow(i);
// XSSFRow row2 = sheet.getRow(i+1);
// XSSFCell cell = rowTotal.createCell(rIndexTotal++);
// Double aDouble = 0.0;
// Double bDouble = 0.0;
// if (titleList.contains(s)){
// cell.setCellValue(value.get(s));
// }else {
// if (row1.getCell(i).getCellType()==0){
// aDouble = Double.valueOf(row1.getCell(i).getNumericCellValue());
// //aDouble = Double.valueOf(row1.getCell(i).getStringCellValue()==null?“0”:row1.getCell(i).getStringCellValue());
// }
// if (row2.getCell(i+1).getCellType()==0){
// bDouble = Double.valueOf(row2.getCell(i+1).getNumericCellValue());
// //aDouble = Double.valueOf(row2.getCell(i+1).getStringCellValue()==null?“0”:row1.getCell(i+1).getStringCellValue());
// }
// cell.setCellValue(bDouble - aDouble);
// }
// cell.setCellStyle(cellStyle);
// }
// }
int totalCell = sheet.getRow(1).getLastCellNum();// 获取第2行的数量
int cellNum = sheet.getPhysicalNumberOfRows(); //当前第几行了
XSSFCell cellTotal = row.createCell(totalCell);
String colString = CellReference.convertNumToColString(totalCell - 1); //长度转成ABC列
String sumString = "SUM(" + "D" + +cellNum + ":" + colString + cellNum + ")";
cellTotal.setCellFormula(sumString);
//cellTotal.setCellStyle(cellStyle);
}
if ("total".equals(mapEntry.getKey())) {
int a = sheet.getPhysicalNumberOfRows(); //获取行
int columnIndexTotal = a + 1;
int b = sheet.getRow(1).getLastCellNum();//获取列
int A = 0;
int B = 0;
for (int j = a - 4; j >= 0; j--) {
XSSFRow rowTotal = sheet.createRow(columnIndexTotal++);
A = a - j;
B = A - 1;
int rIndexTotal = 3;
//放置周别相减的表头
XSSFCell cellsss = rowTotal.createCell(rIndexTotal - 1);
String colString1 = CellReference.convertNumToColString(2); //长度转成ABC列
String sumString1 = colString1 + A + "&" + "\"-\"" + "&" + colString1 + B;
cellsss.setCellFormula(sumString1);
//放置新日期
XSSFCell cellxx = rowTotal.createCell(rIndexTotal - 3);
String colString2 = CellReference.convertNumToColString(0); //长度转成ABC列
String sumString2 = colString2 + A;
cellxx.setCellFormula(sumString2);
for (int i = 0; i < b - 2; i++) {
XSSFCell cell = rowTotal.createCell(rIndexTotal++);
String colString = CellReference.convertNumToColString(rIndexTotal - 1); //长度转成ABC列
String sumString = colString + A + "-" + colString + B;
cell.setCellFormula(sumString);
}
}
}
//计算平均值
// int avgIndex = 0;
// int cellNumTotal = sheet.getPhysicalNumberOfRows(); //当前一共多少行
// XSSFRow rowBottomTitle = sheet.createRow(cellNumTotal);
// for (String s : keyList) {
// XSSFCell cellBottom = rowBottomTitle.createCell(avgIndex++);
// String colString = CellReference.convertNumToColString(avgIndex - 1); //长度转成ABC列
// String avgString = “AVERAGE(” + colString + “3:” + colString + cellNumTotal + “)”;
// if (!titleList.contains(s)){
// cellBottom.setCellFormula(avgString);
// }
// //cellBottom.setCellStyle(cellStyle);
// }
}
}
public void batchData(List<UMaterialExcel> uMaterialExcels,String projectName,XSSFWorkbook workbook,HashMap<String, ArrayList<String>> titleMap,LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap){
for (UMaterialExcel materialExcel : uMaterialExcels) {
LinkedHashMap<String, HashMap<String, String>> dMap = contentDateMap.get(materialExcel.getGtkpn());
if (dMap == null) {
dMap = new LinkedHashMap<>();
contentDateMap.put(materialExcel.getGtkpn(), dMap);
}
HashMap<String, String> map = dMap.get(materialExcel.getImportdate());
if (map == null) {
map = new HashMap<>();
map.put("importdate", materialExcel.getImportdate());
map.put("importdates", materialExcel.getImportdates());
map.put("weeknum", materialExcel.getWeeknum());
dMap.put(materialExcel.getImportdate(), map);
}
map.put(materialExcel.getContentdate(), materialExcel.getContentdata());
ArrayList<String> titlelist = titleMap.get(materialExcel.getGtkpn());
if (titlelist == null) {
titlelist = new ArrayList<>();
titleMap.put(materialExcel.getGtkpn(), titlelist);
}
if (!titlelist.contains(materialExcel.getContentdate())) {
titlelist.add(materialExcel.getContentdate());
}
}
dataEncapsulation(projectName, workbook, titleMap, contentDateMap);
}
/**
* PDF转Excel操作
*
*/
public static void pdf2excel(MultipartFile file,HttpServletResponse response) {
try {
// dwg2GeoJson(“D:\oldwar\CEP-BU4.dwg”);
long old = System.currentTimeMillis();
InputStream inputStream = file.getInputStream();
ServletOutputStream os = response.getOutputStream();
Document doc = new Document(inputStream);//加载源文件数据
doc.save(os, SaveFormat.Excel);//设置转换文件类型并转换
os.close();
long now = System.currentTimeMillis();
System.out.println(“共耗时：” + ((now - old) / 1000.0) + “秒”); //转化用时
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* PDF转Word操作
*
*/
public static void pdf2word(MultipartFile file,HttpServletResponse response) {
try {
long old = System.currentTimeMillis();
InputStream inputStream = file.getInputStream();
ServletOutputStream os = response.getOutputStream();
Document doc = new Document(inputStream);//加载源文件数据
doc.save(os, SaveFormat.DocX);//设置转换文件类型并转换
os.close();
long now = System.currentTimeMillis();
System.out.println(“共耗时：” + ((now - old) / 1000.0) + “秒”); //转化用时
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* PDF转ppt
*
*/
public static void pdf2ppt(MultipartFile file,HttpServletResponse response) {
try {
long old = System.currentTimeMillis();
InputStream inputStream = file.getInputStream();
ServletOutputStream os = response.getOutputStream();
Document doc = new Document(inputStream);//加载源文件数据
doc.save(os, SaveFormat.Pptx);//设置转换文件类型并转换
os.close();
long now = System.currentTimeMillis();
System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒"); //转化用时
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* PDF拆分
*
*/
public static void pdfChai(MultipartFile file,HttpServletResponse response) {
try {
long old = System.currentTimeMillis();
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String fileNamePath = "D:\\pdf\\"+format.format(new Date());
File fileDir = new File(fileNamePath);
if (!fileDir.exists()){
fileDir.mkdirs();
}
InputStream inputStream = file.getInputStream();
ServletOutputStream os = response.getOutputStream();
Document pdfDocument = new Document(inputStream);//加载源文件数据
int pageCount = 1;
for (Page pdfPage : pdfDocument.getPages()) {
Document newDocument = new Document();
newDocument.getPages().add(pdfPage);
newDocument.save(fileNamePath+"\\"+"page_" + pageCount + "_out" + ".pdf");
pageCount++;
}
toZip(fileNamePath,os,response,true);
deleteFolder(fileDir);
long now = System.currentTimeMillis();
System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒"); //转化用时
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* PDF自定义拆分
*
*/
public static void pdfChaiZ(MultipartFile file,String pageValue,HttpServletResponse response) {
try {
long old = System.currentTimeMillis();
int pageOne = Integer.parseInt(pageValue);
List pagesList1 = new ArrayList();
List pagesList2 = new ArrayList();
List pagesList3 = new ArrayList();
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String fileNamePath = "D:\\pdf\\"+format.format(new Date());
File fileDir = new File(fileNamePath);
if (!fileDir.exists()){
fileDir.mkdirs();
}
InputStream inputStream = file.getInputStream();
ServletOutputStream os = response.getOutputStream();
Document pdfDocument = new Document(inputStream);//加载源文件数据
for (Page pdfPage : pdfDocument.getPages()) {
int number = pdfPage.getNumber();
if (number<=pageOne){
pagesList1.add(pdfPage);
} else {
pagesList2.add(pdfPage);
}
}
// 拆分的第一份PDF
Document newDocument = new Document();
newDocument.getPages().add(pagesList1);
newDocument.save(fileNamePath+"\\"+"split_first.pdf");
// 拆分的第二份PDF
newDocument = new Document();
newDocument.getPages().add(pagesList2);
newDocument.save(fileNamePath+"\\"+"split_second.pdf");
toZip(fileNamePath,os,response,true);
deleteFolder(fileDir);
long now = System.currentTimeMillis();
System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒"); //转化用时
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* @param srcDir 压缩文件夹路径
* @param out 压缩文件输出
* @param response
* @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
* @throws
*/
public static void toZip(String srcDir, OutputStream out, HttpServletResponse response, boolean KeepDirStructure)
throws RuntimeException {
long start = System.currentTimeMillis();
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(out);
File sourceFile = new File(srcDir);
compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
long end = System.currentTimeMillis();
System.out.println("压缩完成，耗时：" + (end - start) + " ms");
} catch (Exception e) {
throw new RuntimeException("zip error from ZipUtils", e);
} finally {
if (zos != null) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 递归压缩方法
*
* @param sourceFile 源文件
* @param zos zip输出流
* @param name 压缩后的名称
* @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
* false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
* @throws Exception
*/
private static void compress(File sourceFile, ZipOutputStream zos, String name,
boolean KeepDirStructure) throws Exception {
byte[] buf = new byte[BUFFER_SIZE];
if (sourceFile.isFile()) {
// 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
zos.putNextEntry(new ZipEntry(name));
// copy文件到zip输出流中
int len;
FileInputStream in = new FileInputStream(sourceFile);
while ((len = in.read(buf)) != -1) {
zos.write(buf, 0, len);
}
// Complete the entry
zos.closeEntry();
in.close();
} else {
File[] listFiles = sourceFile.listFiles();
if (listFiles == null || listFiles.length == 0) {
// 需要保留原来的文件结构时,需要对空文件夹进行处理
if (KeepDirStructure) {
// 空文件夹的处理
zos.putNextEntry(new ZipEntry(name + "/"));
// 没有文件，不需要文件的copy
zos.closeEntry();
}
} else {
for (File file : listFiles) {
// 判断是否需要保留原来的文件结构
if (KeepDirStructure) {
// 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
// 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
} else {
compress(file, zos, file.getName(), KeepDirStructure);
}
}
}
}
}
private static boolean deleteFolder(File folder) {
// 检查文件夹是否存在，如果不存在，说明删除成功；如果存在，继续删除子文件夹和文件
if (!folder.exists()) {
return true;
}
// 如果文件夹是一个文件，直接删除它
if (folder.isFile()) {
return folder.delete();
}
// 如果文件夹是一个目录，遍历其下的所有子文件夹和文件，并递归地调用删除本身的方法
boolean result = true; // 定义布尔变量，用于判断删除是否成功
File[] files = folder.listFiles(); // 获取文件夹中的所有文件和子文件夹
if (files != null) {
for (File f : files) { // 遍历每个文件或子文件夹
if (!deleteFolder(f)) { // 对文件夹进行递归删除
result = false; // 如果删除失败，设置布尔变量为false
}
}
}
// 删除文件夹本身
if (!folder.delete()) {
result = false;
}
return result;
}
}
扫一扫
399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
