package com.yc.action.grid;
|
|
import com.yc.exception.ApplicationException;
|
import com.yc.factory.FactoryBean;
|
import com.yc.listener.SessionListener;
|
import com.yc.multiData.SpObserver;
|
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
|
import com.yc.service.BaseService;
|
import com.yc.utils.Page;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.common.usermodel.HyperlinkType;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
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.XSSFDataFormat;
|
import org.jetbrains.annotations.NotNull;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.jdbc.LobRetrievalFailureException;
|
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
|
|
import java.io.*;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.Map.Entry;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
|
/**
|
* POI 的 Excel写数据器.
|
*
|
* @author chenlb 2007-10-29 上午10:39:31
|
*/
|
@Slf4j
|
public class PoiExcelWriter {
|
|
/**
|
* @param datas 封装着Object[]的列表, 一般是String内容.
|
* @param title 每个sheet里的标题. filed是需要导出的字段
|
* @throws ParseException
|
*/
|
|
XSSFDataFormat formatd = null;
|
Map<String, CellStyle> cellStyle = new HashMap<String, CellStyle>();//由于CellStyle创建不能过多，由主要是格式输 出，所以以格式为key，CellStyle为值的map保存来调用避免多次创建相同的对象。
|
public void writeExcelBy16(SXSSFWorkbook workbook, Exprot496Entiry entiry, String hostUrl, String dbid) {
|
try {
|
SXSSFSheet ws = workbook.createSheet(entiry.sheetName);
|
formatd = (XSSFDataFormat) workbook.createDataFormat();
|
int rowNum = 0; //要写的行
|
StringBuffer sb = new StringBuffer();
|
//写excel标题,功能号名称
|
// setCell(entiry.getMainFormName(), ws, 0, 5, null);
|
ShoppingImageDataIfc shoppingImageIfc = (ShoppingImageDataIfc) FactoryBean.getBean("ShoppingImageDataImpl");
|
String imageHostUrl = shoppingImageIfc.getImageHostUrl(hostUrl);
|
final Map<String, AttendEntity> fileType = getFileType(dbid, entiry.data, entiry.picFields, true);
|
// rowNum++;
|
//面板
|
//按行排序再输出
|
//DocStatusName#0,0,1,1;EnterName#0,0,1,1;DocDate#1,1,1,1;PreSendDate#1,2,1,1;DocCode#1,4,1,1;CltCode#2,1,1,1;CltName#2,2,1,1;ShopGuideName#3,4,1,1;SellerName#4,1,1,1;sumtotalmoney2#2,1,1,1;HDMemo#4,1,4,2
|
TreeSet<Pov> set = new TreeSet<Pov>();
|
for (T9802Entity t9802 : entiry.metaData) {
|
//DocStatusName#0,0,1,1
|
String totalStr = t9802.fieldid + "#" + t9802.rowNo + "," + t9802.colNo + "," + t9802.lengthNum + "," + t9802.heightNum;
|
|
set.add(new PoiExcelWriter().new Pov(t9802.getRowNo(), t9802.getColNo(), totalStr, t9802.getFieldname(), t9802.fieldid));
|
}
|
int samRow = 0;
|
int col = 0;
|
Iterator<Pov> it = set.iterator();
|
while (it.hasNext()) {
|
Pov pov = it.next();
|
int row = rowNum + pov.getNum();//行
|
if (samRow != row) col = 0;
|
//写入标题
|
setCell(pov.getTitle() + ":", ws, row, col == -1 ? 0 : col, null);
|
int rcol = col + 1;
|
String[] value = putRowC(workbook, ws, row, entiry.data.get(0), new String[]{pov.getFild()}, rcol, entiry.titleToFileName, false, entiry.picFields, dbid, imageHostUrl, entiry.formid + "", fileType); //压一行到sheet
|
if (!"".equals(value[0])) sb.append(value[0]).append("-");
|
if(Integer.parseInt(value[2])==1) {
|
//有图片的情况才需要加1
|
col = Integer.parseInt(value[1]) + 1;
|
}else {
|
col = Integer.parseInt(value[1]);
|
}
|
samRow = row;
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
public void writeExcelBy496(SXSSFWorkbook workbook, Exprot496Entiry entiry, String hostUrl, String dbid) {
|
try {
|
SXSSFSheet ws = workbook.createSheet(entiry.sheetName);
|
formatd = (XSSFDataFormat) workbook.createDataFormat();
|
int rowNum = 0; //要写的行
|
int tempNum = 0;
|
StringBuffer sb = new StringBuffer();
|
//写excel标题,功能号名称
|
// setCell(entiry.getMainFormName(), ws, 0, 5, null);
|
ShoppingImageDataIfc shoppingImageIfc = (ShoppingImageDataIfc) FactoryBean.getBean("ShoppingImageDataImpl");
|
String imageHostUrl = shoppingImageIfc.getImageHostUrl(hostUrl);
|
final Map<String, AttendEntity> fileType = getFileType(dbid, entiry.data, entiry.picFields, true);
|
// rowNum++;
|
if (entiry.exprotType.equals(ExprotType.Panel)) {
|
//面板
|
//按行排序再输出
|
//DocStatusName#0,0,1,1;EnterName#0,0,1,1;DocDate#1,1,1,1;PreSendDate#1,2,1,1;DocCode#1,4,1,1;CltCode#2,1,1,1;CltName#2,2,1,1;ShopGuideName#3,4,1,1;SellerName#4,1,1,1;sumtotalmoney2#2,1,1,1;HDMemo#4,1,4,2
|
TreeSet<Pov> set = new TreeSet<Pov>();
|
for (T9802Entity t9802 : entiry.metaData) {
|
//DocStatusName#0,0,1,1
|
String totalStr = t9802.fieldid + "#" + t9802.rowNo + "," + t9802.colNo + "," + t9802.lengthNum + "," + t9802.heightNum;
|
String fileid=t9802.fieldid;
|
if(StringUtils.isNotBlank(t9802.displayformat)){
|
fileid+=";"+t9802.displayformat;
|
}
|
set.add(new PoiExcelWriter().new Pov(t9802.getRowNo(), t9802.getColNo(), totalStr, t9802.getFieldname(), fileid));
|
}
|
int samRow = 0;
|
int col = 0;
|
Iterator<Pov> it = set.iterator();
|
while (it.hasNext()) {
|
Pov pov = it.next();
|
int row = rowNum + pov.getNum();//行
|
if (samRow != row) col = 0;
|
//写入标题
|
setCell(pov.getTitle() + ":", ws, row, col == -1 ? 0 : col, null);
|
int rcol = col + 1;
|
String[] value = putRowC(workbook, ws, row, entiry.data.get(0), new String[]{pov.getFild()}, rcol, entiry.titleToFileName, false, entiry.picFields, dbid, imageHostUrl, entiry.formid + "", fileType); //压一行到sheet
|
if (!"".equals(value[0])) sb.append(value[0]).append("-");
|
tempNum = row;
|
if(Integer.parseInt(value[2])==1) {
|
//有图片的情况才需要加1
|
col = Integer.parseInt(value[1]) + 1;
|
}else {
|
col = Integer.parseInt(value[1]);
|
}
|
samRow = row;
|
}
|
} else {
|
//表格
|
String[] titles = getTitles(entiry);
|
String[] fields = new String[entiry.metaData.size()];
|
entiry.metaData.stream().map(x -> {
|
String id= x.fieldid.toLowerCase();
|
String format=x.displayformat;
|
if(StringUtils.isNotBlank(format)){
|
return id+";"+format;
|
}else {
|
return id;
|
}
|
}).collect(Collectors.toList()).toArray(fields);
|
putRow(ws, rowNum, titles);//压入标题
|
|
int totalIndex = 0;
|
for (Map<String, Object> map : entiry.data) {
|
rowNum++;
|
putRowC(workbook, ws, rowNum, map, fields, 0, "", false, entiry.picFields, dbid, imageHostUrl, entiry.formid + "", fileType); //输入数据内容
|
//---是否有子功能号关联，有则在后面输出
|
if (entiry.subList != null && entiry.subList.size() > 0) {
|
for (Exprot496Entiry sub : entiry.subList) {
|
String[] mainKey = sub.fk.split(";");
|
String[] mainValue = new String[mainKey.length];
|
for (int i = 0; i < mainKey.length; i++) {
|
mainValue[i] = map.get(mainKey[i]).toString();
|
}
|
String[] pkKey = sub.seekGroupID.split(";");
|
String[] subFields = new String[sub.metaData.size()];
|
sub.metaData.stream().map(x -> x.fieldid.toLowerCase()).collect(Collectors.toList()).toArray(subFields);
|
//取到关联子功能号数据
|
final Map<String, AttendEntity> subFileType = getFileType(dbid, sub.data, sub.picFields, true);
|
|
List<Map<String, Object>> subCollect = sub.data.stream().filter(y -> {
|
//通过主功能号的主键值取出对应的数据
|
boolean isSame = false;
|
for (int j = 0; j < pkKey.length; j++) {
|
if (y.get(pkKey[j].toLowerCase()).equals(mainValue[j])) {
|
isSame = true;
|
} else {
|
isSame = false;
|
}
|
}
|
return isSame;
|
}).collect(Collectors.toList());
|
//--标题
|
int startRowNum = 0;
|
if (subCollect != null && subCollect.size() > 0) {
|
rowNum++;
|
startRowNum = rowNum;
|
String[] subTitles = getTitles(sub);
|
//---处理有图片字段的情况
|
int maxCount=0;
|
if(subFileType!=null&&subFileType.size()>0){
|
for(Entry<String,AttendEntity> entry:subFileType.entrySet()){
|
maxCount=entry.getValue().getCount();
|
break;
|
}
|
}
|
String[] cols=subTitles;
|
if(maxCount>0){
|
final List<String> list=new ArrayList<String>();
|
for(String s:subTitles){
|
list.add(s);
|
for(String str:subTitles){
|
if((str+"下载").equals(s)) {//查找匹配到是字段名+下载的值，这个值是为了显示附件而增加，不是真实字段的标题
|
for(int i=0;i<maxCount;i++) {
|
list.add("");
|
}
|
break;
|
}
|
}}
|
cols=list.toArray(new String[list.size()]);
|
}
|
// sub.metaData.stream().map(x -> x.fieldname).collect(Collectors.toList()).toArray(subTitles);
|
putsubRow(ws, rowNum, cols);//压入标题
|
rowNum += 1;
|
}
|
int index = 0;
|
for (Map<String, Object> subMap : subCollect) {
|
putRowC(workbook, ws, rowNum, subMap, subFields, 1, "", true, sub.picFields, dbid, imageHostUrl, sub.formid + "", subFileType); //压一行到sheet
|
index++;
|
if (index < subCollect.size()) {
|
rowNum++;
|
}
|
}
|
|
//----子功能号汇总列暂时去掉
|
// processTbCols(sub.tbCols, sub.page, workbook, ws, rowNum);
|
if (subCollect != null && subCollect.size() > 0) {
|
ws.groupRow(startRowNum, startRowNum + subCollect.size());//折叠行
|
setCell(sub.formName, ws, startRowNum, 0, null);
|
if (subCollect.size() > 0) {
|
ws.addMergedRegion(new CellRangeAddress(startRowNum, startRowNum + subCollect.size(), 0, 0));
|
}
|
}
|
}
|
}
|
}
|
//--处理汇总列
|
processTbCols(entiry.tbCols, entiry.page, workbook, ws, rowNum);
|
rowNum++;
|
}
|
} catch (Exception e) {
|
throw new ApplicationException(e.getMessage());
|
//e.printStackTrace();
|
}
|
}
|
|
@NotNull
|
private static String[] getTitles(Exprot496Entiry entiry) {
|
//检查是否有附件字段，有则需要新增多一个对应的名称
|
List<String> picFiledsList = entiry.metaData.stream().filter(e -> {
|
if (e.gridControlType == 9 || e.gridControlType == 19 || e.gridControlType == 40
|
|| e.controlType == 9 || e.controlType == 19 || e.controlType == 40) {
|
return true;
|
}
|
return false;
|
}).map(x -> x.fieldname + "_下载").collect(Collectors.toList());
|
List<String> stringList = entiry.metaData.stream().map(x -> x.fieldname).collect(Collectors.toList());
|
List<String> orgList = new ArrayList<>();
|
if (picFiledsList != null && picFiledsList.size() > 0) {
|
orgList = new ArrayList<>();
|
for (int i = 0; i < stringList.size(); i++) {
|
orgList.add(stringList.get(i));
|
for (String str : picFiledsList) {
|
if (str.startsWith(stringList.get(i))) {
|
orgList.add(str);
|
}
|
}
|
}
|
} else {
|
orgList = stringList;
|
}
|
String[] titles = new String[orgList.size()];
|
orgList.toArray(titles);
|
return titles;
|
}
|
|
private void processTbCols(String entiry, Page page, SXSSFWorkbook workbook, SXSSFSheet ws, int rowNum) {
|
String ex = entiry.replaceAll("\n", "");
|
if (!"".equalsIgnoreCase(ex)) {
|
Map<String, ExcelPO> param = new HashMap<String, ExcelPO>();
|
String[] tem = ex.split(";");
|
if (StringUtils.isNotBlank(page.getTbColsOut())) {
|
String[] tbCols = page.getTbColsOut().split("#M#");//#M#是行号docItem,需要排除
|
String[] values = tbCols[0].split("#p#");
|
for (String key : tem) {
|
String[] ss = key.split("#");
|
ExcelPO po = new PoiExcelWriter().new ExcelPO();
|
po.setKey(ss[0]);
|
//ss[1]格式：汇总类型|自定义汇总公式(其他类型为空)|字段在excel的位置_T_字段在9802的位置|数字格式
|
String[] indexArry = ss[1].split("\\|")[2].split("_T_");
|
po.setFum(ss[1]);
|
if (indexArry.length == 1) {
|
throw new ApplicationException("汇总列格式已更新，请通过右下角的小助手[点我一键解决]重新生成页面后再操作");
|
}
|
try {
|
po.setNum(Double.parseDouble(values[Integer.parseInt(indexArry[1])]));//indexArry[1]为汇总列在9802中的真实位置
|
} catch (Exception e) {
|
po.setNum(0);
|
}
|
param.put(ss[0], po);
|
}
|
prossFormula(workbook, ws, rowNum, param);//常规
|
}
|
}
|
}
|
|
public Map writeExcel(String dbid, String filename, Page page, String[] title, String[] filed, String[] panel_title, String[] panel_filed, List panel, String titles, String local, String exporttitles, String hostUrl) throws ParseException {
|
List datas = page.getData();
|
if (datas == null || datas.size() == 0) {
|
throw new IllegalArgumentException("没有数据需要导出到excel!");
|
}
|
SXSSFWorkbook wb = new SXSSFWorkbook(500);
|
try {
|
SXSSFSheet ws = wb.createSheet("sheet1");
|
formatd = (XSSFDataFormat) wb.createDataFormat();
|
int rowNum = 0; //要写的行
|
int tempNum = 0;
|
StringBuffer sb = new StringBuffer();
|
//写标题
|
setCell(titles, ws, 0, 5, null);
|
//取需要导出图片的字段
|
BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
|
ShoppingImageDataIfc shoppingImageIfc = (ShoppingImageDataIfc) FactoryBean.getBean("ShoppingImageDataImpl");
|
String picFileds = "";
|
String imageHostUrl;
|
try {
|
SpObserver.setDBtoInstance("_" + dbid);
|
picFileds = baseService.getJdbcTemplate().queryForObject("set nocount on \n" +
|
" select stuff((SELECT ',' + CONVERT(VARCHAR, isnull(fieldid,''))+'' from gField where formid=? and isnull(isExport,0)=1 and (gridcontroltype in(9,19,40,50) or controltype in(9,19,40,50)) FOR XML PATH ('')),1,1,'')", String.class, page.getFormid());
|
imageHostUrl = shoppingImageIfc.getImageHostUrl(hostUrl);
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
//--end
|
rowNum++;
|
if (panel != null) {
|
//1,写面板
|
if (panel_title != null) {
|
|
String[] s = local.split(";");
|
//按行排序再输出
|
TreeSet<Pov> set = new TreeSet<Pov>();
|
for (int k = 0; k < s.length; k++) {
|
String[] ss = s[k].split("#")[1].split(",");//位置
|
set.add(new PoiExcelWriter().new Pov(Integer.parseInt(ss[0]), Integer.parseInt(ss[1]),s[k], panel_title[k], panel_filed[k]));
|
}
|
int samRow = 0;
|
int col = 0;
|
Iterator<Pov> it = set.iterator();
|
final Map<String, AttendEntity> fileType = getFileType(dbid, panel, picFileds, true);
|
while (it.hasNext()) {
|
Pov p = it.next();
|
int row = rowNum + p.getNum();//行
|
if (samRow != row) col = 0;
|
//写入标题
|
setCell(p.getTitle() + ":", ws, row, col == -1 ? 0 : col, null);
|
int rcol = col + 1;
|
String[] value = putRowC(wb, ws, row, panel.get(0), new String[]{p.getFild()}, rcol, exporttitles, false, picFileds, dbid, imageHostUrl, page.getFormid() + "", fileType); //压一行到sheet
|
if (!"".equals(value[0])) sb.append(value[0]).append("-");
|
tempNum = row;
|
if(Integer.parseInt(value[2])==1) {
|
//有图片的情况才需要加1
|
col = Integer.parseInt(value[1]) + 1;
|
}else {
|
col = Integer.parseInt(value[1]);
|
}
|
samRow = row;
|
}
|
}
|
|
if (panel_title != null)
|
rowNum = tempNum + 4;
|
}
|
|
//---------格线内容
|
Map<String, AttendEntity> fileType = getFileType(dbid, datas, picFileds, true);
|
if (title != null) {
|
int maxCount=0;
|
if(fileType!=null&&fileType.size()>0){
|
for(Entry<String,AttendEntity> entry:fileType.entrySet()){
|
maxCount=entry.getValue().getCount();
|
break;
|
}
|
}
|
String[] cols=title;
|
if(maxCount>0){
|
final List<String> list=new ArrayList<String>();
|
for(String s:title){
|
list.add(s);
|
for(String str:title){
|
if((str+"下载").equals(s)) {//查找匹配到是字段名+下载的值，这个值是为了显示附件而增加，不是真实字段的标题
|
for(int i=0;i<maxCount;i++) {
|
list.add("");
|
}
|
break;
|
}
|
}}
|
cols=list.toArray(new String[list.size()]);
|
}
|
putRow(ws, rowNum, cols);//压入标题
|
rowNum += 1;
|
}
|
|
for (int i = 0; i < datas.size(); i++, rowNum++) {//写sheet
|
Map map = (Map) datas.get(i);
|
|
putRowC(wb, ws, rowNum, map, filed, 0, "", false, picFileds, dbid, imageHostUrl, page.getFormid() + "", fileType); //压一行到sheet
|
//处理第三表
|
try {
|
if (page.getGrid3Data() != null && page.getGrid3Data().size() > 0) {
|
int startRowNum = rowNum;
|
String[] key = page.getKeys().split("_");//分割关联字段
|
String[] masterkey = key[0].split(";");//分割第二表的关联字段，有可能存在多个的情况
|
String[] detailkey = key[1].split(";");//分割第三表的关联字段，有可能存在多个的情况
|
|
List<Map<String, Object>> list = ((List<Map<String, Object>>) page.getGrid3Data()).stream().filter(x -> {
|
boolean flg = false;
|
for (int j = 0; j < masterkey.length; j++) {//只取符合当前第三表的数据
|
if (x.get(detailkey[j].toLowerCase()).equals(map.get(masterkey[j].toLowerCase())))
|
flg = true;
|
}
|
return flg;
|
}).collect(Collectors.toList());
|
//输出第三表的标题
|
rowNum += 1;
|
if (page.getGrid3Titles()!= null) {
|
putsubRow(ws, rowNum, page.getGrid3Titles());//压入标题
|
}
|
for (Map<String, Object> map2 : list) {
|
putRowC(wb, ws, ++rowNum, map2, page.getGrid3Fields(), 1, "", true, picFileds, dbid, imageHostUrl, page.getGrid3FormId() + "", fileType); //压一行到sheet
|
}
|
if (list != null && list.size() > 0) {
|
ws.groupRow(startRowNum + 1, startRowNum + 1 + list.size());//折叠第三表的行
|
}
|
}
|
} catch (Exception e) {
|
throw new ApplicationException("处理第三表数据出错：" + e.getMessage());
|
}
|
|
}
|
processTbCols(page.getExcelTitle(), page, wb, ws, rowNum);
|
//--------end
|
Map<String, Object> map = new HashMap<String, Object>();
|
sb.append(filename);
|
map.put("wb", wb);//excecl对象
|
map.put("title", sb.toString());//文件名称
|
return map;
|
|
|
//-----------
|
|
} catch (Exception e) {
|
try {
|
wb.close();
|
} catch (IOException ex) {
|
throw new RuntimeException(ex);
|
}
|
throw new ApplicationException(e.getMessage());
|
|
}
|
}
|
|
/**
|
* @param dbid
|
* @param list 数据
|
* @param key 附件字段名称
|
* @return
|
*/
|
public Map<String, AttendEntity> getFileType(String dbid, List list, String key, boolean isExcel) {
|
HashMap<String, AttendEntity> fileType = new HashMap<>();
|
if (StringUtils.isBlank(key)) {
|
return fileType;
|
}
|
try {
|
SpObserver.setDBtoInstance("_" + dbid);
|
BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
|
StringJoiner joiner = new StringJoiner(" , ");
|
List<Integer> countJoiner =new ArrayList<>();
|
int count=0;
|
if (list != null && list.size() > 0) {
|
final String[] keys = key.replaceAll(",",";").split(";");
|
list.stream().forEach(x -> {
|
Map<String, Object> m = (Map<String, Object>) x;
|
int maxCount=0;
|
for (String k : keys) {
|
String[] filekeys = (m.get(k)+"").split(";");//取附件值，判断长度
|
if (filekeys!=null&&filekeys.length>0&&filekeys.length>maxCount) {
|
maxCount = filekeys.length;//附件数量，用作生成格线标题时需要空出多少列标题
|
}
|
if ((m.get(k) + "").contains(";") && !(m.get(k) + "").startsWith("http")) {
|
String[] uuid = (m.get(k) + "").split(";");
|
if (m.get(k) != null && StringUtils.isNotBlank(m.get(k) + "")) {
|
if (StringUtils.isNotBlank(uuid[0])) {
|
joiner.add("'" + uuid[0] + "' ");
|
}
|
}
|
|
}
|
}
|
countJoiner.add(maxCount);
|
});
|
if (joiner.length() == 0) {
|
return fileType;
|
}
|
count=countJoiner.stream().max(Integer::compareTo).get();
|
String fileds = "";
|
if (isExcel) {
|
//如果是导出则输出附件内容
|
fileds += ",FileType,SmallPicture as pic";//SmallPicture,OriginalPicture
|
} else {
|
fileds += ",FileType, null as pic";//SmallPicture,OriginalPicture
|
}
|
List<AttendEntity> maps = new ArrayList<>();
|
baseService.getSimpleJdbcTemplate().query(" declare @table table(unid varchar(150),count int,FileType varchar(10),pic image)\n declare @maxCount int="+((count-2)*2)+
|
" insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,@maxCount as count" + fileds + " from _sys_Attachment9\n" +
|
" where unid in(" + joiner.toString() + ")\n" +
|
" if @@ROWCOUNT=0 \n" +
|
" insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,@maxCount as count" + fileds + " from _sys_Attachment\n" +
|
" where unid in(" + joiner.toString() + ")\n" +
|
" select * from @table",
|
new AbstractLobStreamingResultSetExtractor<List<AttendEntity>>() {
|
@Override
|
protected void handleNoRowFound() throws LobRetrievalFailureException {
|
//有可能返回空集合，在这里不需要处理 by danaus 2023-09-09 14:07
|
}
|
|
@Override
|
protected void streamData(ResultSet resultSet) throws SQLException, IOException, DataAccessException {
|
if (!resultSet.wasNull()) {
|
do {
|
AttendEntity attendEntity = new AttendEntity();
|
attendEntity.setFileType(resultSet.getString("FileType"));
|
attendEntity.setUnid(resultSet.getString("unid"));
|
attendEntity.setData(resultSet.getBytes("pic"));
|
attendEntity.setCount(resultSet.getInt("count"));
|
maps.add(attendEntity);
|
} while (resultSet.next());
|
}
|
}
|
});
|
|
if (maps != null && maps.size() > 0) {
|
for (AttendEntity attendEntity : maps) {
|
fileType.put(attendEntity.getUnid(), attendEntity);
|
}
|
}
|
}
|
return fileType;
|
} catch (Exception ex) {
|
throw new ApplicationException(ex.getMessage());
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
|
}
|
|
/**
|
* @param flg true表示常常规统计:汇总,计算,...,false表示自定义公式统计
|
*/
|
private void prossFormula(SXSSFWorkbook wb, SXSSFSheet ws, int rowNum, Map<String, ExcelPO> param) {//
|
for (Entry<String, ExcelPO> en : param.entrySet()) {
|
ExcelPO po = en.getValue();
|
double total = po.getNum();
|
String[] vo = po.getFum().split("\\|");//1,null,5,数字格式;
|
String[] colIndex = vo[2].split("_T_");//取字段在execl的位置
|
String format = getNumFormat(vo[3]);//数字格式
|
if (!"".equalsIgnoreCase(format)) {
|
String datetimestyle[] = format.split("-");
|
if (datetimestyle.length >= 2) {
|
setCellDouble(total, ws, rowNum + 1, Integer.parseInt(colIndex[0]), null);
|
} else {
|
setCellDouble(total, ws, rowNum + 1, Integer.parseInt(colIndex[0]), getCellStyle(format, wb));
|
}
|
} else {
|
setCellDouble(total, ws, rowNum + 1, Integer.parseInt(colIndex[0]), null);
|
}
|
}
|
}
|
|
private CellStyle getCellStyle(String format, SXSSFWorkbook wb) {
|
if(format.startsWith(",")){
|
format=format.replace(",","#,");
|
}
|
if (cellStyle.containsKey(format)) {
|
return cellStyle.get(format);
|
} else {
|
CellStyle style = wb.createCellStyle();
|
style.setDataFormat(formatd.getFormat(format.replaceAll("~", "-")));
|
cellStyle.put(format, style);
|
return cellStyle.get(format);
|
}
|
}
|
|
class Pov implements Comparable {
|
private int num;//行
|
private int col;//列
|
private String cont;
|
private String title;
|
private String fild;
|
|
public String getFild() {
|
return fild;
|
}
|
|
public void setFild(String fild) {
|
this.fild = fild;
|
}
|
|
public String getTitle() {
|
return title;
|
}
|
|
public void setTitle(String title) {
|
this.title = title;
|
}
|
|
public Pov(int row,int col, String con, String t, String f) {
|
this.num = row;//行号
|
this.col=col;
|
this.cont = con;//整个内容，用作行号相同时，再根据内容决定顺序
|
this.title = t;
|
this.fild = f;
|
}
|
|
public int getNum() {
|
return num;
|
}
|
|
public void setNum(int num) {
|
this.num = num;
|
}
|
|
public String getCont() {
|
return cont;
|
}
|
|
public void setCont(String cont) {
|
this.cont = cont;
|
}
|
|
public int compareTo(Object o) {
|
Pov ss = (Pov) o;
|
//int result = num < ss.num ? 1 : (num == ss.num ? 0 : -1);//降序
|
int result = num > ss.num ? 1 : (num == ss.num ? 0 : -1);//升序
|
if (result == 0) {
|
result = col > ss.col ? 1 : (col == ss.col ? 0 : -1);
|
}
|
if (result == 0) {
|
result =cont.compareTo(ss.cont);
|
}
|
return result;
|
}
|
}
|
|
private static double calculate(String[] RPolishArray) {
|
double result = 0;
|
double[] tempArray = new double[100];
|
int tempNum = -1;
|
for (int i = 0; i < RPolishArray.length; i++) {
|
Pattern pt = Pattern.compile("\\d");
|
java.util.regex.Matcher propsMatcher = pt.matcher(RPolishArray[i]);
|
String p = null;
|
while (propsMatcher.find()) {
|
p = propsMatcher.group();
|
}
|
if (p != null) {
|
tempNum++;
|
tempArray[tempNum] = new Double(RPolishArray[i]);
|
} else {
|
|
if (RPolishArray[i].equalsIgnoreCase("+")) {
|
|
|
result = (tempArray[tempNum - 1] * 1) + (tempArray[tempNum] * 1);
|
tempNum--;
|
tempArray[tempNum] = result;
|
} else if (RPolishArray[i].equalsIgnoreCase("-")) {
|
result = (tempArray[tempNum - 1] * 1) - (tempArray[tempNum] * 1);
|
tempNum--;
|
tempArray[tempNum] = result;
|
|
} else if (RPolishArray[i].equalsIgnoreCase("*")) {
|
result = (tempArray[tempNum - 1] * 1) * (tempArray[tempNum] * 1);
|
tempNum--;
|
tempArray[tempNum] = result;
|
|
} else if (RPolishArray[i].equalsIgnoreCase("/")) {
|
if (tempArray[tempNum] * 1 == 0) {//被除数为0的情况
|
tempNum--;
|
tempArray[tempNum] = 0;
|
} else {
|
result = (tempArray[tempNum - 1] * 1) / (tempArray[tempNum] * 1);
|
tempNum--;
|
tempArray[tempNum] = result;
|
}
|
|
|
}
|
}
|
}
|
result = tempArray[tempNum];
|
return result;
|
}
|
|
private static double prossFumal(int rowcount, ExcelPO po, int type, List list, String name) {
|
double total;
|
double max = 0;//最大值
|
double min = 0;//最小值
|
|
|
switch (type) {
|
case 1://汇总
|
total = po.getNum();
|
break;
|
case 2://计数
|
total = rowcount;
|
break;
|
case 3://平均
|
total = po.getNum() / rowcount;
|
break;
|
case 4://百分比
|
total = po.getNum() * 100;
|
break;
|
case 6://最大
|
for (int i = 0; i < list.size(); i++) {//检查所有行
|
Map<String, Object> map = (Map<String, Object>) list.get(i);
|
|
double temp = Double.parseDouble(map.get(name) == null || map.get(name).equals("") ? "0" : map.get(name) + "");//当前行的字段值
|
if (max == 0) { //最大
|
max = temp;
|
} else {
|
if (temp > max) max = temp;
|
}
|
}
|
total = max;
|
break;
|
case 7://最小
|
for (int i = 0; i < list.size(); i++) {//检查所有行
|
Map<String, Object> map = (Map<String, Object>) list.get(i);
|
double temp = Double.parseDouble(map.get(name) == null || map.get(name).equals("") ? "0" : map.get(name) + "");//当前行的字段值
|
if (min == 0) { //最小
|
min = temp;
|
} else {
|
if (min > temp) min = temp;
|
}
|
}
|
total = min;
|
break;
|
default:
|
total = 0;
|
}
|
return total;
|
}
|
|
public static void getAmount(List selectList, Map<String, ExcelPO> param) {
|
|
if (selectList != null) {
|
if (selectList.size() > 0) {
|
Iterator list = selectList.iterator();
|
while (list.hasNext()) {
|
Map map = (Map) list.next();
|
// for (String key : param.keySet()) {
|
for (Entry<String, ExcelPO> entry : param.entrySet()) {
|
ExcelPO num = entry.getValue();
|
Object obj = map.get(entry.getKey());
|
if (obj != null && !"".equalsIgnoreCase(String.valueOf(obj))) {
|
try {
|
//存在有格式化的情况，所以这里需要做转换 44,555.55
|
String temp = procHy(String.valueOf(obj));
|
num.setNum(num.getNum() + new Double(temp.replaceAll(",", "")));
|
} catch (Exception e) {
|
num.setNum(num.getNum() + 0);
|
}
|
}
|
|
}
|
}
|
}
|
}
|
}
|
|
class ExcelPO {
|
private double num;//总数
|
private int col;//所在列
|
private String key;//键
|
private String fum;//统计公式类型
|
|
public String getFum() {
|
return fum;
|
}
|
|
public void setFum(String fum) {
|
this.fum = fum;
|
}
|
|
public double getNum() {
|
return num;
|
}
|
|
public void setNum(double num) {
|
this.num = num;
|
}
|
|
public int getCol() {
|
return col;
|
}
|
|
public void setCol(int col) {
|
this.col = col;
|
}
|
|
public String getKey() {
|
return key;
|
}
|
|
public void setKey(String key) {
|
this.key = key;
|
}
|
|
|
}
|
|
private static void setCell(String titles, SXSSFSheet ws, int rowindex, int cellindex, CellStyle cellStyle) {
|
SXSSFRow rows = ws.getRow(rowindex);
|
if (rows == null)
|
rows = ws.createRow(rowindex);
|
SXSSFCell cell = rows.getCell(cellindex);
|
if (cell == null)
|
cell = rows.createCell(cellindex);
|
if (StringUtils.isNotBlank(titles)) {
|
cell.setCellValue(titles);
|
}
|
if (cellStyle != null)
|
cell.setCellStyle(cellStyle);
|
|
}
|
|
private static void setCellDouble(double d, SXSSFSheet ws, int rowindex, int cellindex, CellStyle cellStyle) {
|
SXSSFRow rows = ws.getRow(rowindex);
|
if (rows == null)
|
rows = ws.createRow(rowindex);
|
SXSSFCell cell = rows.createCell(cellindex);
|
if (cellStyle != null)
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(d);
|
|
}
|
|
private static void putRow(SXSSFSheet ws, int rowNum, Object[] cells) {
|
for (int j = 0; j < cells.length; j++) {//写一行
|
setCell("" + cells[j], ws, rowNum, j, null);
|
}
|
}
|
|
private static void putsubRow(SXSSFSheet ws, int rowNum, Object[] cells) {
|
for (int j = 0; j < cells.length; j++) {//写一行
|
setCell("" + cells[j], ws, rowNum, j + 1, null);
|
}
|
}
|
|
private byte[] getImage(String filePath) throws IOException {
|
try (InputStream inputStream = new FileInputStream(new File(filePath))) {
|
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
|
byte[] bytes = new byte[1024];
|
int len = -1;
|
while ((len = inputStream.read(bytes)) != -1) {
|
byteArrayOutputStream.write(bytes, 0, len);
|
}
|
byteArrayOutputStream.close();
|
return byteArrayOutputStream.toByteArray();
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
private String[] putRowC(SXSSFWorkbook wb, SXSSFSheet ws, int rowNum, Object cells, String[] filed, int j, String titles, boolean isThreeData, String picFileds, String dbid, String imageHostUrl, String formid, Map<String, AttendEntity> fileType) {
|
Map map = (Map) cells;
|
boolean flg = false;
|
if (j > 0 && !isThreeData) flg = true;
|
String value = "";
|
Pattern p = Pattern.compile("uuid=.*");
|
int colNum=j;//列位置
|
int maxCount=0;
|
int picNum=0;//表示当前行有图片字段输出，需要控制列数要加1，而在没有图片字段的情况不不需要进行加1处理
|
if(fileType!=null){
|
if(fileType!=null&&fileType.size()>0){
|
for(Entry<String,AttendEntity> entry:fileType.entrySet()){
|
maxCount=entry.getValue().getCount();
|
break;
|
}
|
}
|
}
|
String tempFiled="";
|
for (String s : filed) {
|
String[] fieldTexts = s.split(";");
|
//---针对图片处理
|
if (StringUtils.isNotBlank(picFileds) && ("," + picFileds.toLowerCase() + ",").contains("," + fieldTexts[0].trim().toLowerCase() + ",")) {
|
picNum=1;
|
String uuid = GridUtils.prossRowSetDataType_String(map, fieldTexts[0].trim().toLowerCase());
|
if(tempFiled.equals(s)){ continue;}
|
tempFiled=s;
|
map.put(fieldTexts[0].trim().toLowerCase(), "");//清空附件内容
|
String[] strArray = uuid.split(";");
|
int picCount=0;
|
for (int i = 1; i < strArray.length; i++) {
|
|
String txt = strArray[0] + ";" + strArray[i];
|
byte[] imgByte = null;
|
try {
|
imgByte= fileType.get(txt).getData();
|
}catch (Exception e){
|
//不处理,因为没附件
|
//throw new ApplicationException(uuid+"-在附件表不存在");
|
}
|
//|/getImage.do?type=0&uuid=19D7C74C-FA55-4027-ADCB-BDDEBD3C42C5;15522;png|48|48|
|
String imgUrl = null;
|
String fileExt =".jpg";
|
String OrgfileExt = "jpg";
|
try {
|
fileExt = "." + fileType.get(txt).getFileType();
|
OrgfileExt = "" + fileType.get(txt).getFileType();
|
}catch (Exception e){
|
//不处理,因为没附件
|
}
|
String smallFileType = "jpg";
|
if (txt.contains("|/getImage.do")) {
|
String[] uuids = txt.split("\\|");
|
java.util.regex.Matcher propsMatcher = p.matcher(uuids[1]);
|
while (propsMatcher.find()) {
|
txt = propsMatcher.group().replace("uuid=", "");
|
}
|
txt = txt.replace(";", "@p@").replace(";", ".");
|
} else {
|
//txt = txt.replace(";", "@p@") + ".jpg";
|
|
txt = txt.replace(";", "@p@") + fileExt;
|
}
|
|
imgUrl = imageHostUrl + "/uploads/attachment/" + dbid + "/" + formid + "/" + txt;
|
if (!isPicType(OrgfileExt)) {//不是图片则取固定对应的图标作为缩略图
|
smallFileType = "png";
|
if (OrgfileExt.equalsIgnoreCase("mp4")
|
|| OrgfileExt.equalsIgnoreCase("wmv")
|
|| OrgfileExt.equalsIgnoreCase("mpeg")
|
|| OrgfileExt.equalsIgnoreCase("m4v")
|
|| OrgfileExt.equalsIgnoreCase("asf")
|
|| OrgfileExt.equalsIgnoreCase("flv")
|
|| OrgfileExt.equalsIgnoreCase("f4v")
|
|| OrgfileExt.equalsIgnoreCase("rmvb")
|
|| OrgfileExt.equalsIgnoreCase("rm")
|
|| OrgfileExt.equalsIgnoreCase("3gp")
|
|| OrgfileExt.equalsIgnoreCase("vob")
|
) OrgfileExt = "avi";
|
String file = SessionListener.servletContextPath + "/smallpic/specialpic/" + OrgfileExt + ".png";
|
try {
|
imgByte = getImage(file);
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
if (imgByte != null && imgByte.length > 0) {
|
picCount++;
|
SXSSFRow row = ws.getRow(rowNum);
|
if (row == null)
|
row = ws.createRow(rowNum);
|
|
short rowHeight= (short) (1200);
|
row.setHeight(rowHeight);
|
//列宽，单位为字符宽度的1/256, 15*256表示15个字符宽度
|
if(i==1) {
|
colNum = colNum + i - 1;
|
}else{
|
colNum=colNum+1;
|
}
|
SXSSFCell cell = row.createCell(colNum);
|
ws.setColumnWidth(colNum, 13 * 556);
|
// 图片存在即输出图片
|
int addPicture = 0;
|
if (smallFileType.equals("jpg")) {
|
addPicture = wb.addPicture(imgByte, SXSSFWorkbook.PICTURE_TYPE_JPEG);
|
} else {
|
addPicture = wb.addPicture(imgByte, SXSSFWorkbook.PICTURE_TYPE_PNG);
|
}
|
Drawing<?> drawing = ws.createDrawingPatriarch();
|
CreationHelper helper = wb.getCreationHelper();
|
ClientAnchor anchor = helper.createClientAnchor();
|
anchor.setRow1(rowNum);
|
anchor.setCol1(colNum);
|
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
|
|
Picture picture = drawing.createPicture(anchor, addPicture);
|
// 获取图片的原始宽度和高度
|
int originalWidth = picture.getImageDimension().width;
|
int originalHeight = picture.getImageDimension().height;
|
|
// 计算等比缩放比例
|
double maxWidth = 80; // 设置图片的最大宽度
|
double maxHeight = 80; // 设置图片的最大高度
|
double widthScale = maxWidth / originalWidth;
|
double heightScale = maxHeight / originalHeight;
|
|
// 选择较小的缩放比例，以保持图片比例不变
|
double scale = Math.min(widthScale, heightScale);
|
// 设置图片的缩放比例
|
/* if (strArray.length > 2) {
|
picture.resize(1.0/(rowHeight/1200));
|
} else {
|
}*/
|
picture.resize(scale);
|
// 在单元格中设置超链接
|
colNum=colNum+1;
|
Cell hyperLinkCell = row.createCell(colNum );
|
hyperLinkCell.setCellValue("打开");
|
Hyperlink hyperlink = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
|
hyperlink.setAddress(imgUrl); // 指定的URL链接
|
//增加超链接打开原图或其他不是图片的附件
|
// 创建超链接并设置起始位置和 URL
|
hyperLinkCell.setHyperlink(hyperlink);
|
// 创建一个样式来指示超链接
|
CellStyle hlinkStyle = wb.createCellStyle();
|
Font hlinkFont = wb.createFont();
|
hlinkFont.setUnderline(Font.U_SINGLE);
|
hlinkFont.setColor(IndexedColors.BLUE.getIndex());
|
hlinkStyle.setFont(hlinkFont);
|
hyperLinkCell.setCellStyle(hlinkStyle);
|
} else {
|
|
setCell("", ws, rowNum, j, null);
|
|
}
|
// if (!flg) j++;
|
// continue;
|
|
}
|
if(strArray.length==1){
|
//---附件全为空的情况
|
colNum+=maxCount+2;
|
flg=true;
|
}else {
|
if(picCount>0&&maxCount==0){
|
//只有一个附件且附件不为空，不需填充
|
}else{
|
if(picCount<maxCount){
|
colNum+=(maxCount+2-picCount*2);
|
}
|
// colNum +=maxCount==picCount?0:;
|
}
|
}//如果附件数没达到最大，则需要留空输出
|
// flg=true;
|
} else {
|
//----普通值
|
boolean tflg = (";" + titles.trim().toLowerCase() + ";").contains(";" + fieldTexts[0].trim().toLowerCase() + ";");
|
|
if (map.containsKey(fieldTexts[0].toLowerCase() + "_expr") && "0".equalsIgnoreCase(map.get(fieldTexts[0].toLowerCase() + "_expr") + "")) {
|
map.put(fieldTexts[0], "*");
|
}
|
|
if (map.get(fieldTexts[0]) != null && !"".equals(map.get(fieldTexts[0])) && fieldTexts.length > 1) {//表示有格式
|
|
if (fieldTexts[1].startsWith("0") || fieldTexts[1].startsWith(",0") || fieldTexts[1].startsWith(",0.") || fieldTexts[1].startsWith("0.")) {
|
|
//格式化数字
|
String format = getNumFormat(fieldTexts[1]);
|
|
if ((String.valueOf(map.get(fieldTexts[0]))).matches("^[+-]?[\\d]+([\\.][\\d]+)?([Ee][+-]?[\\d]+)?$")) {
|
Double d = Double.parseDouble(String.valueOf(map.get(fieldTexts[0])));
|
|
if (tflg) value = d + "";
|
setCellDouble(d, ws, rowNum, colNum, getCellStyle(format, wb));
|
} else {
|
String sd = "" + ((map.get(fieldTexts[0]) == null || "null".equals(map.get(fieldTexts[0]))) ? "" : map.get(fieldTexts[0]));
|
String st = procFont(procHy(sd));
|
if (tflg) value = st;
|
if (!"".equalsIgnoreCase(st) && st.matches("^^[+-]?[\\d]+([\\.][\\d]+)?([Ee][+-]?[\\d]+)?$$"))
|
setCellDouble(Double.parseDouble(st), ws, rowNum, colNum, getCellStyle(format, wb));
|
else
|
setCell(st.replaceAll(" ", " "), ws, rowNum, colNum, getCellStyle(format, wb));
|
}
|
|
} else {
|
if (map.get(fieldTexts[0]) == null || "".equalsIgnoreCase(map.get(fieldTexts[0]).toString().trim())) {
|
setCell("", ws, rowNum, colNum, null);
|
} else {
|
//格式化日期
|
SimpleDateFormat formatDate = new SimpleDateFormat(fieldTexts[1].replaceAll("~", "-"));
|
try {
|
String st = formatDate.format(formatDate.parse(map.get(fieldTexts[0]).toString()));//by danaus 17-11-29 还原过来
|
if (tflg) value = st;
|
setCell(st, ws, rowNum, colNum, null);
|
} catch (Exception e) {
|
// TODO Auto-generated catch blockk
|
setCell(map.get(fieldTexts[0]).toString(), ws, rowNum, colNum, null);
|
//throw new ApplicationException("【"+temp[0]+"】-"+e.getMessage());
|
}
|
}
|
}
|
} else {
|
//普通字符
|
// String format = getNumFormat(temp[1]);
|
String sd = "" + ((map.get(fieldTexts[0].toLowerCase()) == null || "null".equals(map.get(fieldTexts[0].toLowerCase()))) ? "" : map.get(fieldTexts[0].toLowerCase()));
|
String st = procFont(procHy(sd));
|
if (tflg) value = st;
|
setCell(st.replaceAll(" ", " "), ws, rowNum, colNum, null);
|
}
|
flg=false;
|
}
|
if (!flg) colNum++;//j>0表示是面板调用
|
}
|
return new String[]{value,colNum+"",picNum+""};
|
}
|
|
private boolean isPicType(String fileExt) {
|
return (fileExt.toLowerCase().equalsIgnoreCase("jpg")
|
|| fileExt.toLowerCase().equalsIgnoreCase("png")
|
|| fileExt.toLowerCase().equalsIgnoreCase("gif")
|
|| fileExt.toLowerCase().equalsIgnoreCase("bmp")
|
) ? true : false;
|
}
|
|
final int COL_WIDTH = 13000;
|
final int ROW_HEIGHT = 5000;
|
|
private static int getAnchorX(int px, int colWidth) {
|
return (int) Math.round(((double) 701 * 16000.0 / 301) * ((double) 1 / colWidth) * px);
|
}
|
|
private static int getAnchorY(int px, int rowHeight) {
|
return (int) Math.round(((double) 144 * 8000 / 301) * ((double) 1 / rowHeight) * px);
|
}
|
|
private static int getRowHeight(int px) {
|
return (int) Math.round(((double) 4480 / 300) * px);
|
}
|
|
private static int getColWidth(int px) {
|
return (int) Math.round(((double) 10971 / 300) * px);
|
}
|
|
private static String procHy(String str) { //去掉有超链接内容只返回内容
|
|
String regex = "<(\\S*?) [^>]*>.*?</\\1>|<.*? />";
|
//String regex = "<a.*>(.*)</a>";
|
Pattern pt = Pattern.compile(regex);
|
String temp = str.replaceAll("<A", "<a").replaceAll("A>", "a>").replaceAll("<hr>|<HR>|<hr/>|<HR/>", "");
|
//保存超链接外的内容
|
String otherStr = null;
|
|
Matcher mt = pt.matcher(temp.replaceAll("<A", "<a").replaceAll("A>", "a>"));//.replaceAll("<A", "<a").replaceAll("A>", "a>")
|
StringBuilder sb = new StringBuilder();
|
while (mt.find()) {
|
|
String s2 = ">.*?</(a|font)>";//标题部分
|
Pattern pt2 = Pattern.compile(s2);
|
Matcher mt2 = pt2.matcher(mt.group());
|
if (otherStr == null) {
|
otherStr = temp.replace(mt.group(), " ");
|
} else {
|
otherStr = otherStr.replace(mt.group(), " ");
|
}
|
while (mt2.find()) {
|
String tm = mt2.group().replaceAll(">|</a>|</font>", "");
|
sb.append(tm).append(" ");
|
}
|
|
}
|
return "".equals(sb.toString()) ? str : (otherStr + sb.toString());
|
|
}
|
|
private static String procFont(String str) { //去掉有字体设置内容只返回内容
|
|
String regex = "<font.*?/font>";
|
//String regex = "<a.*>(.*)</a>";
|
Pattern pt = Pattern.compile(regex);
|
Matcher mt = pt.matcher(str);
|
while (mt.find()) {
|
|
String s2 = ">.*?</font>";//标题部分
|
Pattern pt2 = Pattern.compile(s2);
|
Matcher mt2 = pt2.matcher(mt.group());
|
while (mt2.find()) {
|
return mt2.group().replaceAll(">|</font>", "");
|
}
|
|
}
|
return str;
|
|
}
|
|
private static String getNumFormat(String temp) {
|
if (temp == null || "".equalsIgnoreCase(temp)) return null;
|
int indx = temp.indexOf(".");//,0.00
|
String format = null;
|
if (indx > 0) {//有小数位
|
//有千位
|
if (temp.startsWith(","))
|
format = temp.replace(",0.", "#,###0.");
|
else
|
format = temp.replace("0.", "###0.");
|
} else {//整数
|
if (temp.startsWith(","))
|
format = temp.replace(",0", "#,###0");
|
else
|
format = temp.replace("0", "#");
|
}
|
return format;
|
}
|
}
|
