package com.yc.action.grid;
|
|
import com.yc.exception.ApplicationException;
|
import com.yc.factory.FactoryBean;
|
import com.yc.multiData.SpObserver;
|
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
|
import com.yc.service.BaseService;
|
import com.yc.utils.Page;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
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 java.io.ByteArrayOutputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.net.URL;
|
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
|
*/
|
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 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) {
|
throw new IllegalArgumentException("写excel流需要List参数!");
|
}
|
try {
|
SXSSFWorkbook wb = new SXSSFWorkbook(500);
|
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) or controltype in(9,19,40)) 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]), s[k], panel_title[k], panel_filed[k]));
|
}
|
int samRow = 0;
|
int col = 0;
|
Iterator<Pov> it = set.iterator();
|
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;
|
final Map<String, String> fileType = getFileType(dbid, panel, picFileds);
|
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)) sb.append(value).append("-");
|
tempNum = row;
|
col = rcol + 1;
|
samRow = row;
|
}
|
}
|
|
if (panel_title != null)
|
rowNum = tempNum + 4;
|
}
|
|
//---------格线内容
|
if (title != null) {
|
putRow(ws, rowNum, title);//压入标题
|
rowNum += 1;
|
}
|
|
Map<String, String> fileType = getFileType(dbid, datas, picFileds);
|
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) {
|
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());
|
|
for (Map<String, Object> map2 : list) {
|
putRowC(wb, ws, ++rowNum, map2, filed, 1, "", true, picFileds, dbid, imageHostUrl, page.getFormid() + "",fileType); //压一行到sheet
|
}
|
|
}
|
} catch (Exception e) {
|
throw new ApplicationException("处理第三表数据出错：" + e.getMessage());
|
}
|
|
}
|
String ex = page.getExcelTitle().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(wb, ws, rowNum, param);//常规
|
}
|
}
|
//--------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) {
|
throw new ApplicationException(e.getMessage());
|
|
}
|
}
|
|
/**
|
*
|
* @param dbid
|
* @param list 数据
|
* @param key 附件字段名称
|
* @return
|
*/
|
private Map<String, String> getFileType(String dbid, List list,String key) {
|
|
HashMap<String, String> fileType = new HashMap<>();
|
try {
|
SpObserver.setDBtoInstance("_" + dbid);
|
BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
|
StringJoiner joiner = new StringJoiner(" or ");
|
if(list!=null&&list.size()>0) {
|
list.stream().forEach(x->{
|
Map<String,Object> m =(Map<String, Object>) x;
|
if(m.get(key)!=null&&StringUtils.isNotBlank(m.get(key)+"")) {
|
joiner.add("unid='" + (m.get(key) + "").split(";")[0] + "'");
|
}
|
});
|
final List<Map<String, Object>> maps = baseService.getSimpleJdbcTemplate().queryForList("set nocount on\n "+
|
" select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,FileType from _sys_Attachment9 where " +joiner.toString()+" \n"+
|
"union\n" +
|
"select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,FileType from _sys_Attachment where "+joiner.toString()+"\n");
|
if (maps != null && maps.size() > 0) {
|
for (Map<String, Object> map : maps) {
|
fileType.put(GridUtils.prossRowSetDataType_String(map, "unid"), GridUtils.prossRowSetDataType_String(map, "FileType"));
|
}
|
}
|
}
|
return fileType;
|
} catch (Exception ex) {
|
return fileType;
|
} 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 (cellStyle.containsKey(format)) {
|
//cellStyle=wb.createCellStyle();
|
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 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 n, String con, String t, String f) {
|
this.num = n;
|
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 = 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()) {
|
|
ExcelPO num = param.get(key);
|
Object obj = map.get(key);
|
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.createCell(cellindex);
|
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 byte[] getImage(String imageHostUrl) throws IOException {
|
final InputStream inputStream = new URL(imageHostUrl).openStream();
|
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();
|
inputStream.close();
|
return byteArrayOutputStream.toByteArray();
|
}
|
|
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, String> fileType) {
|
Map map = (Map) cells;
|
boolean flg = false;
|
if (j > 0 && !isThreeData) flg = true;
|
String value = "";
|
Pattern p = Pattern.compile("uuid=.*");
|
for (String s : filed) {
|
String[] fieldTexts = s.split(";");
|
//---针对图片处理
|
if (StringUtils.isNotBlank(picFileds) && StringUtils.isNotBlank(GridUtils.prossRowSetDataType_String(map, fieldTexts[0].trim().toLowerCase()))
|
&& ("," + picFileds.toLowerCase() + ",").contains("," + fieldTexts[0].trim().toLowerCase() + ",")) {
|
|
byte[] imgByte = null;
|
|
|
String uuid = GridUtils.prossRowSetDataType_String(map, fieldTexts[0].trim().toLowerCase());
|
map.put(fieldTexts[0].trim().toLowerCase(), "");//清空附件内容
|
String[] strArray = uuid.split(";");
|
|
for (int i = 1; i < strArray.length; i++) {
|
String txt = strArray[0] + ";" + strArray[i];
|
//|/getImage.do?type=0&uuid=19D7C74C-FA55-4027-ADCB-BDDEBD3C42C5;15522;png|48|48|
|
String imgUrl = null;
|
try {
|
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";
|
String fileExt="."+fileType.get(txt);
|
txt=txt.replace(";", "@p@")+fileExt ;
|
}
|
|
imgUrl = imageHostUrl + "/uploads/attachment/" + dbid + "/" + formid + "/" + txt;
|
imgByte = getImage(imgUrl);
|
} catch (IOException e) {
|
e.printStackTrace();
|
throw new ApplicationException(e.getMessage());
|
}
|
if (imgByte != null && imgByte.length > 0) {
|
SXSSFRow row = ws.getRow(rowNum);
|
if (row == null)
|
row = ws.createRow(rowNum);
|
row.setHeight((short) (1200 * (strArray.length - 1)));
|
//列宽，单位为字符宽度的1/256, 15*256表示15个字符宽度
|
ws.setColumnWidth(j, 13 * 256);
|
SXSSFCell cell = row.createCell(j);
|
// 图片存在即输出图片
|
int addPicture = wb.addPicture(imgByte, SXSSFWorkbook.PICTURE_TYPE_JPEG);
|
Drawing<?> drawing = ws.createDrawingPatriarch();
|
CreationHelper helper = wb.getCreationHelper();
|
ClientAnchor anchor = helper.createClientAnchor();
|
anchor.setRow1(rowNum);
|
anchor.setCol1(j);
|
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
|
|
// 指定我想要的长宽
|
double standardWidth = 80;
|
double standardHeight = 100;
|
|
// 计算单元格的长宽
|
double cellWidth = ws.getColumnWidthInPixels(cell.getColumnIndex());
|
double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;
|
|
// 计算需要的长宽比例的系数
|
double a = standardWidth / cellWidth;
|
double b = standardHeight / cellHeight;
|
|
Picture picture = drawing.createPicture(anchor, addPicture);
|
if (strArray.length > 2) {
|
picture.resize(0.1);
|
} else {
|
picture.resize(a, b);
|
}
|
|
} else {
|
setCell("", ws, rowNum, j, null);
|
}
|
//if (!flg) j++;
|
// continue;
|
}
|
}
|
//----
|
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, j, 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, j, getCellStyle(format, wb));
|
else
|
setCell(st.replaceAll(" ", " "), ws, rowNum, j, getCellStyle(format, wb));
|
}
|
|
} else {
|
if (map.get(fieldTexts[0]) == null || "".equalsIgnoreCase(map.get(fieldTexts[0]).toString().trim())) {
|
setCell("", ws, rowNum, j, 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, j, null);
|
} catch (Exception e) {
|
// TODO Auto-generated catch blockk
|
setCell(map.get(fieldTexts[0]).toString(), ws, rowNum, j, 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, j, null);
|
}
|
if (!flg) j++;
|
}
|
return value;
|
}
|
|
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;
|
}
|
}
|
