edi.rule.util.ZSPoi Maven / Gradle / Ivy
package edi.rule.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.util.*;
import java.util.Map.Entry;
import edi.rule.config.JSRuleMessage;
import jakarta.servlet.http.HttpServletResponse;
import lombok.Cleanup;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import com.deepoove.poi.XWPFTemplate;
import edi.rule.model.JSRuleAction;
import edi.rule.model.JSRuleExportExcel;
import edi.rule.model.JSRuleExportSheet;
import edi.rule.model.JSRuleExportWord;
import edi.rule.model.JSRuleImportExcel;
import edi.rule.work.constant.ZSConstant;
import edi.rule.work.custom.JSRuleException;
import edi.rule.work.processor.JSSAXSheetContent;
/**
* @author 摩拉克斯
* @date 2022年6月27日 上午10:59:29
*/
public class ZSPoi {
public static > void exportWord(JSRuleExportWord word, HttpServletResponse response) {
try{
@Cleanup OutputStream out = null;
@Cleanup XWPFTemplate template = XWPFTemplate.compile(word.getPath()).render(word.getData());
if (word.getOutFilePath() != null) {
out = Files.newOutputStream(ZSIo.createFileWithDir(word.getOutFilePath()).toPath());
}else {
response.setContentType(ZSConstant.CONTENT_TYPE_WORD_DOCX);
response.setHeader("Content-disposition","attachment:filename=\""+ZSHttp.responseEncode(word.getResponseFileName())+"\"");
out = response.getOutputStream();
}
template.write(out);
out.flush();
} catch (IOException e) {
throw new JSRuleException(JSRuleMessage.read("the.IO.throws.exception"),e.getMessage());
}
}
public static > List> getExcelContent(JSRuleImportExcel excel) {
List> contentList = new ArrayList<>();
try (OPCPackage pkg = OPCPackage.open(excel.getFile())){
@Cleanup InputStream inputStream = null;
XSSFReader xssfReader = new XSSFReader(pkg);
SharedStrings sst = xssfReader.getSharedStringsTable();
XMLReader xmlReader = XMLReaderFactory.createXMLReader();//SAXParserFactory.newInstance().newSAXParser().getXMLReader();
JSSAXSheetContent sheetContent;InputSource sheetSource;
Iterator sheets = xssfReader.getSheetsData();
int i = 0;
while(sheets.hasNext()) {
try {
sheetContent = new JSSAXSheetContent<>(excel.getSheets().get(i));
}catch (IndexOutOfBoundsException e) {
throw new JSRuleException(JSRuleMessage.read("missing.sheet"));
}
xmlReader.setContentHandler(new XSSFSheetXMLHandler(xssfReader.getStylesTable(),sst,sheetContent,new DataFormatter(),false));
inputStream = sheets.next();
sheetSource = new InputSource(inputStream);
xmlReader.parse(sheetSource);
contentList.add(sheetContent);
i++;
}
return contentList;
} catch (Exception e) {
throw new JSRuleException(e);
}
}
public static > void exportExcel(JSRuleExportExcel excel,boolean isUseTemplate,HttpServletResponse response) {
if (isUseTemplate) {
if (!excel.getPath().endsWith(ZSConstant.EXCEL_2007U_SUFFIX)) {
throw new JSRuleException(JSRuleMessage.read("the.following.versions.2007.are.not.supported"));
}
exportExcelWithTemplate(excel,response);
}else {
exportExcelWithOutTemplate(excel,response);
}
}
private static > void exportExcelWithOutTemplate(JSRuleExportExcel excel,HttpServletResponse response) {
try (SXSSFWorkbook xWorkBook = new SXSSFWorkbook(excel.getCacheRow())){
@Cleanup OutputStream output = null;
int sheetNum=0;
SXSSFSheet xSheet;String defaultSheetName;
for (JSRuleExportSheet sheet:excel.getSheets()) {
if (sheet.getDataLine() < 1) {
throw new JSRuleException(JSRuleMessage.read("the.start.line.of.data.can't.be.less.than.1"));
}
if (ZSString.isBlank(sheet.getTempSheetName())) {
defaultSheetName = "sheet"+(sheetNum+1);
}else {
defaultSheetName = sheet.getTempSheetName();
}
if (ZSString.isBlank(sheet.getNewSheetName())) {
sheet.setNewSheetName(defaultSheetName);
}
xSheet = xWorkBook.createSheet(sheet.getNewSheetName());
int dataStartRowNum = sheet.getDataLine()-1;
//表体数据行加载
Object cellValue;int dataRowNum;Map data;SXSSFRow row;
int dataSize = sheet.getData().size();int colNumber;
Map fieldMapping = sheet.getFieldMappings();
Map mergeRecord = new HashMap<>(); //记录所有需要合并的单元格信息
if (sheet.getContainsHead()) {
row = xSheet.createRow(Math.max(dataStartRowNum - 1, 0));
colNumber = 0;
for (Entry column:fieldMapping.entrySet()) {
setCellValue(row.createCell(colNumber),column.getValue());
colNumber++;
}
}
for (int i=0;i column:fieldMapping.entrySet()) {
cellValue = data==null||data.get(column.getKey())==null?sheet.getDefaultValue():data.get(column.getKey());
if (sheet.getWrapText() && ZSString.isNotBlank(sheet.getLineBreakTag())) {
setCellValue(row.createCell(colNumber),new XSSFRichTextString(cellValue.toString().replaceAll(sheet.getLineBreakTag(),"\r\n")));
}else {
setCellValue(row.createCell(colNumber),cellValue);
}
//是否合并单元格(因poi循环合并单元格后行号偏移,再次合并会出现错误,即连续3个相同行的列值,因此只能先记录合并的行列再统一进行合并处理)
if (sheet.getMergeCell() && i!=0) {
if (excel.getCacheRow()> void exportExcelWithTemplate(JSRuleExportExcel excel,HttpServletResponse response) {
try {
@Cleanup InputStream input = null;
@Cleanup OutputStream output = null;
@Cleanup XSSFWorkbook tWorkBook = null;
@Cleanup SXSSFWorkbook nWorkBook = null;
try {
input = new FileInputStream(excel.getPath());
}catch (FileNotFoundException e) {
throw new JSRuleException(JSRuleMessage.read("template.not.found"),excel.getPath());
}
int sheetNum=0;
tWorkBook = new XSSFWorkbook(input);
nWorkBook = new SXSSFWorkbook(excel.getCacheRow());
//只能通过XSSFSheet获取数据，因此需要先从SXSSFWorkbook获取XSSFWorkbook
XSSFSheet tSheet;XSSFRow tHeadRow;SXSSFSheet nSheet;SXSSFRow nHeadRow;String defaultSheetName;
for (JSRuleExportSheet jSheet:excel.getSheets()) {
if (jSheet.getDataLine() < 1) {
throw new JSRuleException(JSRuleMessage.read("the.start.line.of.data.can't.be.less.than.1"));
}
if (ZSString.isBlank(jSheet.getTempSheetName())) {
tSheet = tWorkBook.getSheetAt(sheetNum);
defaultSheetName = "sheet"+(sheetNum+1);
}else {
tSheet = tWorkBook.getSheet(jSheet.getTempSheetName());
defaultSheetName = jSheet.getTempSheetName();
}
if (tSheet==null) {
throw new JSRuleException(JSRuleMessage.read("sheet.not.found.in.template"),"name："+defaultSheetName,"number："+sheetNum);
}
if (ZSString.isBlank(jSheet.getNewSheetName())) {
jSheet.setNewSheetName(defaultSheetName);
}
nSheet = nWorkBook.createSheet(jSheet.getNewSheetName());
int dataStartRowNum = jSheet.getDataLine()-1;
//表头及标题部分
CellStyle cellStyle;String headCellValue;
//获取并替换指令行以上的表头或标题部分的变量形成真实值,如果在params里找不到对应的key的值则默认为""空
for (int i=0;i v==null?"":v),cellStyle);
}
}
for (CellRangeAddress cra:tSheet.getMergedRegions()) {
nSheet.addMergedRegion(cra);
}
//表体列项预加载
XSSFRow dataStartRow = tSheet.getRow(dataStartRowNum);
if (dataStartRow==null) {
throw new JSRuleException(JSRuleMessage.read("the.property.of.dataLine.is.error.or.missing"));
}
int colSize = dataStartRow.getLastCellNum(); //获取指定的行号上的最后一个单元格位置(中间可能有空列或没有$标识的列)
CellStyle[] columnStyles = new CellStyle[colSize]; //初始化可构造数据的列样式数组
String[] columnFields = new String[colSize]; //初始化可构造数据的列字段数组
String columnCellValue;
for (int i=0;i data;SXSSFRow row;
Map mergeRecord = new HashMap<>(); //记录所有需要合并的单元格信息
int dataSize = jSheet.getData().size();
for (int i=0;i sheet,int i,int j,int dataSize,int dataRowNum,Object cellValue,String fieldName,Map mergeRecord){
String mergeLineOfStart;
if (cellValue.equals(sheet.getData().get(i-1).get(fieldName))) {
mergeRecord.computeIfAbsent(j + "", k -> dataRowNum - 1 + "");
if (i==dataSize-1) {
mergeLineOfStart = mergeRecord.get(j+"");
mergeRecord.put(j+"-"+mergeLineOfStart,dataRowNum+"-"+cellValue);
mergeRecord.remove(j+"");
}
}else {
mergeLineOfStart = mergeRecord.get(j+"");
if (mergeLineOfStart != null) {
mergeRecord.put(j+"-"+mergeLineOfStart,(dataRowNum-1)+"-"+cellValue);
mergeRecord.remove(j+"");
}
}
}
private static void mergeRecord(SXSSFSheet xSheet,Map mergeRecord){
String[] key;
String[] value;
int mergeColumnNum;
//合并单元格,根据记录器mergeRecord来合并单元格,key[0]为列号,key[1]为起始行号,value[0]结束行号,value[1]合并后单元格的值
for (Entry entry:mergeRecord.entrySet()){
key = entry.getKey().split("-",2);
value = entry.getValue().split("-",2);
mergeColumnNum = Integer.parseInt(key[0]);
mergeCells(xSheet,Integer.parseInt(key[1]),Integer.parseInt(value[0]),mergeColumnNum,mergeColumnNum);
}
}
private static SXSSFRow initRowWithStyle(SXSSFSheet xSheet,int rowNum,CellStyle style,short height) {
SXSSFRow row = xSheet.createRow(rowNum);
row.setRowStyle(style);
row.setHeight(height);
return row;
}
/*带样式设置单元格*/
private static void setCellValueWithStyle(SXSSFCell cell,Object value,CellStyle style){
cell.setCellStyle(style);
setCellValue(cell,value);
}
private static void mergeCells(SXSSFSheet xSheet , int firstRow , int lastRow , int firstCol , int lastCol){
xSheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
private static void setCellValue(SXSSFCell xCell,Object value){
if(value instanceof String){
xCell.setCellValue((String) value);
}else if(value instanceof Double){
xCell.setCellValue((Double) value);
}else if (value instanceof Date) {
xCell.setCellValue((Date) value);
}else if (value instanceof Boolean){
xCell.setCellValue((Boolean) value);
}else if (value instanceof RichTextString) {
xCell.setCellValue((RichTextString)value);
}else {
xCell.setCellValue(value.toString());
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy