| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180 |
- package com.goafanti.common.utils.excel;
- import com.goafanti.common.bo.Result;
- import com.goafanti.common.constant.AFTConstants;
- import com.goafanti.common.error.BusinessException;
- import com.goafanti.common.utils.DateUtils;
- import com.goafanti.common.utils.StringUtils;
- import com.goafanti.common.utils.excel.Excel.ColumnType;
- import com.goafanti.common.utils.excel.Excel.Type;
- import com.goafanti.techproject.bo.OutTaskDetailsBo;
- import com.goafanti.techproject.bo.TaskDetailsBo;
- import org.apache.poi.POIXMLDocumentPart;
- import org.apache.poi.hssf.usermodel.*;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.ss.util.CellRangeAddressList;
- import org.apache.poi.util.IOUtils;
- import org.apache.poi.xssf.usermodel.*;
- import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import javax.servlet.http.HttpServletResponse;
- import java.io.*;
- import java.lang.reflect.Field;
- import java.lang.reflect.Method;
- import java.math.BigDecimal;
- import java.net.URL;
- import java.net.URLConnection;
- import java.nio.file.Files;
- import java.nio.file.Paths;
- import java.text.DecimalFormat;
- import java.time.LocalDate;
- import java.time.LocalDateTime;
- import java.util.*;
- import java.util.stream.Collectors;
- /**
- * Excel相关处理
- *
- *
- */
- public class NewExcelUtil<T> {
- private static final Logger log = LoggerFactory.getLogger(NewExcelUtil.class);
- /**
- * Excel sheet最大行数,默认65536
- */
- public static final int sheetSize = 65536;
- public String downloadPath = "";
- public String screen = "";
- /**
- * 工作表名称
- */
- private String sheetName;
- /**
- * 工作薄对象
- */
- private Workbook wb;
- /**
- * 导出类型(EXPORT:导出数据;IMPORT:导入模板)
- */
- private Type type;
- /**
- * 工作表对象
- */
- private Sheet sheet;
- /**
- * 样式列表
- */
- private Map<String, CellStyle> styles;
- /**
- * 导入导出数据列表
- */
- private List<T> list;
- /**
- * 注解列表
- */
- private List<Object[]> fields;
- /**
- * 标题
- */
- private String title;
- /**
- * 最大高度
- */
- private short maxHeight;
- /**
- * 统计列表
- */
- private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
- /**
- * 数字格式
- */
- private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
- /**
- * 实体对象
- */
- public Class<T> clazz;
- public NewExcelUtil(Class<T> clazz) {
- this.clazz = clazz;
- }
- public void init(List<T> list, String sheetName) {
- if (list == null) {
- list = new ArrayList<T>();
- }
- this.list = list;
- this.sheetName = sheetName;
- createExcelField();
- createWorkbook();
- }
- public void init(List<T> list, String sheetName, String title, Type type)
- {
- if (list == null)
- {
- list = new ArrayList<T>();
- }
- this.list = list;
- this.sheetName = sheetName;
- this.type = type;
- this.title = title;
- createExcelField();
- createWorkbook();
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @return 结果
- */
- public Result exportExcel(List<T> list, String sheetName) {
- this.init(list, sheetName);
- return exportExcel();
- }
- /**
- * 直接导出CXCEL表
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param response
- */
- public Result exportExcel(List<T> list, String sheetName, HttpServletResponse response) {
- this.init(list, sheetName);
- exportExcel(response);
- return new Result().data(1);
- }
- /**
- * 导出CXCEL表到指定目录
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param uploadPath 导出本地地址
- */
- public Result exportExcel(List<T> list, String sheetName, String uploadPath) {
- this.init(list, sheetName);
- this.downloadPath=uploadPath;
- return exportExcel();
- }
- /**
- * 将数据合并按订单合并并导出CXCEL表到指定目录
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param uploadPath 导出本地地址
- */
- public Result OrderMergeAndExportExcel(List<T> list, String sheetName, String uploadPath) {
- this.init(list, sheetName);
- this.downloadPath=uploadPath;
- return OrderMergeAndExportExcel();
- }
- private Result OrderMergeAndExportExcel() {
- OutputStream out = null;
- String filename="";
- try {
- // 取出一共有多少个sheet.
- int sheetNo = (list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++) {
- createSheet(sheetNo, index);
- int line=0;
- // 产生一行
- Row title = sheet.createRow(line);
- Cell cell = title.createCell(0);
- cell.setCellValue(sheetName);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
- sheet.addMergedRegion(region);
- cell.setCellStyle(styles.get("total"));
- if (StringUtils.isNotEmpty(screen)){
- log.debug(screen);
- Row r = sheet.createRow(++line);
- Cell c = r.createCell(0);
- c.setCellValue(screen);
- CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
- sheet.addMergedRegion(rg);
- c.setCellStyle(styles.get("header"));
- }
- Row row = sheet.createRow(++line);
- // 产生一行
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields) {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- filename = encodingFilename(sheetName);
- out = Files.newOutputStream(Paths.get(getAbsoluteFile(filename)));
- fillMergeExcelData(index,++line);
- //新增合并
- // mergeOrderNo();
- addStatisticsRow();
- }
- wb.write(out);
- return new Result().data(filename);
- } catch (Exception e) {
- e.printStackTrace();
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- if (out != null) {
- try {
- out.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
- private void fillMergeExcelData(int index, Integer line) throws Exception {
- int startNo = index * sheetSize;
- int endNo = Math.min(startNo + sheetSize, list.size());
- if(line==null)line=2;
- for (int i = startNo; i < endNo; i++) {
- int start=i + line - startNo;
- Row row = sheet.createRow(start);
- // 得到导出对象.
- T vo = (T) list.get(i);
- String orderNo = (String) getValue(vo, "orderNo");
- int x = i+1;
- int count =0;
- while (x<endNo){
- T t1 = list.get(x);
- String orderNo1 = (String) getValue(t1, "orderNo");
- if (orderNo.equals(orderNo1)){
- count++;
- x++;
- }else{
- break;
- }
- }
- if(count>0){
- int column = 0;
- for (Object[] os : fields) {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- if (column<35){
- this.addOrderMegerCell(excel, row, vo, field, column);
- CellRangeAddress region = new CellRangeAddress(start, start+count, column, column);
- sheet.addMergedRegion(region);
- Row row1 = sheet.getRow(start);
- Cell cell1 = row1.getCell(column);
- CellStyle style = styles.get("data4");
- cell1.setCellStyle(style);
- column++;
- }else {
- this.addOrderMegerCell(excel, row, vo, field, column++);
- }
- }
- for (int j = 1; j <= count; j++) {
- T vo3 = (T) list.get(i+j);
- Row row3 = sheet.createRow(start+j);
- int column3 = 0;
- for (Object[] os : fields) {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- if (column3>34){
- this.addOrderMegerCell(excel, row3, vo3, field, column3++);
- }else {
- column3++;
- }
- }
- }
- i=i+count;
- }else {
- int column = 0;
- for (Object[] os : fields) {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- Cell cell = this.addCell(excel, row, vo, field, column++);
- cell.setCellStyle(styles.get("data4"));
- }
- }
- }
- }
- private void mergeOrderNo() throws Exception {
- int max = list.size();
- for (int i = 0; i < max; i++) {
- T t = list.get(i);
- String orderNo = (String) getValue(t, "orderNo");
- int count =0;
- if (i!=max-1){
- int x = i+1;
- while (x<max){
- T t1 = list.get(x);
- String orderNo1 = (String) getValue(t1, "orderNo");
- if (orderNo.equals(orderNo1)){
- count++;
- x++;
- }else{
- break;
- }
- }
- if (count>0&&orderNo.equals("1015315879283802112")){
- int rowNum = i+2;
- int rowNum1 = x+1;
- Row row = sheet.getRow(rowNum);
- Row row1 = sheet.getRow(rowNum1);
- System.out.println("orderNo="+orderNo+",count="+count+",rowNum="+rowNum+",rowNum1="+rowNum1);
- int index=0;
- for (Object[] e : fields) {
- Field field = (Field) e[0];
- Excel attr = (Excel) e[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- Object value = getTargetValue(t, field, attr);
- Cell cell = row.createCell(index);
- String dateFormat = attr.dateFormat();
- String readConverterExp = attr.readConverterExp();
- String separator = attr.separator();
- String enumsName = attr.enumsName();
- String methodName="getValueByCode";
- if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
- Class<?> clazz=Class.forName(enumsName);
- Method method=clazz.getDeclaredMethod(methodName,Integer.class);
- Object s=method.invoke(clazz,value);
- setCellVo(s.toString(), attr, cell);
- }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
- value=DateUtils.parseDateToStr(dateFormat, (Date) value);
- } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
- value=convertByExp(toStr(value), readConverterExp, separator);
- } else if (value instanceof BigDecimal && -1 != attr.scale()) {
- value=(((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString();
- } else{
- // 设置列类型
- setCellVo(value, attr, cell);
- }
- if (value==null)value="";
- cell.setCellValue(value.toString());
- CellRangeAddress region = new CellRangeAddress(rowNum, rowNum1, index, index);
- sheet.addMergedRegion(region);
- index++;
- }
- }
- }
- }
- // CellRangeAddress region = new CellRangeAddress(4, 5, 0, fields.size()-1);
- // sheet.addMergedRegion(region);
- }
- /**
- * 直接导出CXCEL表
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param uploadPath 导出本地地址
- * @param screen 搜索条件
- */
- public Result exportExcel(List<T> list, String sheetName, String uploadPath,String screen) {
- this.init(list, sheetName);
- this.downloadPath=uploadPath;
- this.screen=screen;
- return exportExcel();
- }
- private void exportExcel(HttpServletResponse response) {
- try {
- OutputStream out = response.getOutputStream();
- // 取出一共有多少个sheet.
- int sheetNo = (int) Math.ceil(list.size() / sheetSize);
- String dateFilename =DateFilename(sheetName);
- for (int index = 0; index <= sheetNo; index++) {
- Integer line=0;
- createSheet(sheetNo, index);
- // 产生一行
- Row title = sheet.createRow(line);
- Cell cell = title.createCell(0);
- cell.setCellValue(sheetName);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
- sheet.addMergedRegion(region);
- cell.setCellStyle(styles.get("total"));
- if (StringUtils.isNotEmpty(screen)){
- log.debug(screen);
- Row r = sheet.createRow(++line);
- Cell c = r.createCell(0);
- c.setCellValue(screen);
- CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
- sheet.addMergedRegion(rg);
- c.setCellStyle(styles.get("header"));
- }
- Row row = sheet.createRow(++line);
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields) {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- fillExcelData(index, row,++line);
- addStatisticsRow();
- }
- response.addHeader("Content-Disposition", "attachment;filename=" + new String(dateFilename.getBytes(),"iso-8859-1"));
- response.setContentType("application/octet-stream;charset=utf-8");
- wb.write(out);
- out.flush();
- out.close();
- } catch (Exception e) {
- e.printStackTrace();
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
- private String DateFilename(String sheetName) {
- sheetName = sheetName + "_" + DateUtils.formatDate(new Date(), AFTConstants.YYYYMMDDHHMMSS)+".xlsx";
- return sheetName;
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param sheetName 工作表的名称
- * @return 结果
- */
- public Result importTemplateExcel(String sheetName) {
- this.init(null, sheetName);
- return exportExcel();
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @return 结果
- */
- public Result exportExcel() {
- OutputStream out = null;
- String filename="";
- try {
- // 取出一共有多少个sheet.
- int sheetNo = (int) Math.ceil(list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++) {
- createSheet(sheetNo, index);
- int line=0;
- // 产生一行
- Row title = sheet.createRow(line);
- Cell cell = title.createCell(0);
- cell.setCellValue(sheetName);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
- sheet.addMergedRegion(region);
- cell.setCellStyle(styles.get("total"));
- if (StringUtils.isNotEmpty(screen)){
- log.debug(screen);
- Row r = sheet.createRow(++line);
- Cell c = r.createCell(0);
- c.setCellValue(screen);
- CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
- sheet.addMergedRegion(rg);
- c.setCellStyle(styles.get("header"));
- }
- Row row = sheet.createRow(++line);
- // 产生一行
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields) {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- filename = encodingFilename(sheetName);
- out = new FileOutputStream(getAbsoluteFile(filename));
- fillExcelData(index, row,++line);
- addStatisticsRow();
- }
- wb.write(out);
- return new Result().data(filename);
- } catch (Exception e) {
- e.printStackTrace();
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- if (out != null) {
- try {
- out.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
- /**
- * 填充excel数据
- *
- * @param index 序号
- * @param row 单元格行
- */
- public void fillExcelData(int index, Row row,Integer line) {
- int startNo = index * sheetSize;
- int endNo = Math.min(startNo + sheetSize, list.size());
- if(line==null)line=2;
- for (int i = startNo; i < endNo; i++) {
- row = sheet.createRow(i + line - startNo);
- // 得到导出对象.
- T vo = (T) list.get(i);
- int column = 0;
- for (Object[] os : fields) {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- this.addCell(excel, row, vo, field, column++);
- }
- }
- }
- /**
- * 创建表格样式
- *
- * @param wb 工作薄对象
- * @return 样式列表
- */
- private Map<String, CellStyle> createStyles(Workbook wb) {
- // 写入各条记录,每条记录对应excel表中的一行
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
- //
- CellStyle style = wb.createCellStyle();
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setBorderRight(BorderStyle.THIN);
- style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderLeft(BorderStyle.THIN);
- style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderTop(BorderStyle.THIN);
- style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderBottom(BorderStyle.THIN);
- style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- Font dataFont = wb.createFont();
- dataFont.setFontName("Arial");
- dataFont.setFontHeightInPoints((short) 10);
- style.setFont(dataFont);
- styles.put("data", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font headerFont = wb.createFont();
- headerFont.setFontName("Arial");
- headerFont.setFontHeightInPoints((short) 10);
- headerFont.setBold(true);
- headerFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(headerFont);
- styles.put("header", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font totalFont = wb.createFont();
- totalFont.setFontName("Arial");
- totalFont.setFontHeightInPoints((short) 10);
- totalFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(totalFont);
- styles.put("total", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.LEFT);
- styles.put("data1", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- styles.put("data2", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.RIGHT);
- styles.put("data3", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setBorderRight(BorderStyle.THIN);
- style.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setBorderLeft(BorderStyle.THIN);
- style.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setBorderTop(BorderStyle.THIN);
- style.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setBorderBottom(BorderStyle.THIN);
- style.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
- styles.put("data4", style);
- return styles;
- }
- /**
- * 创建单元格
- */
- public Cell createCell(Excel attr, Row row, int column) {
- // 创建列
- Cell cell = row.createCell(column);
- // 写入列信息
- cell.setCellValue(attr.name());
- setDataValidation(attr, row, column);
- cell.setCellStyle(styles.get("header"));
- return cell;
- }
- /**
- * 创建单元格
- */
- public Cell createTotal(String str, Row row, int column) {
- // 创建列
- Cell cell = row.createCell(column);
- // 写入列信息
- cell.setCellValue(str);
- sheet.setColumnWidth(column, (int) ((14 + 0.72) * 256));
- cell.setCellStyle(styles.get("header"));
- return cell;
- }
- /**
- * 设置单元格信息
- *
- * @param value 单元格值
- * @param attr 注解相关
- * @param cell 单元格信息
- */
- public void setCellVo(Object value, Excel attr, Cell cell) {
- if (ColumnType.STRING == attr.cellType()) {
- if (attr.percentage()==1){
- if (value!=null){
- BigDecimal v=new BigDecimal(value.toString());
- v=v.multiply(new BigDecimal(100));
- if (attr.scale()>0){
- v=v.setScale(attr.scale(),BigDecimal.ROUND_HALF_EVEN);
- }
- value=v.stripTrailingZeros().toPlainString()+"%";
- }else {
- value="0%";
- }
- }
- cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
- } else if (ColumnType.NUMERIC == attr.cellType()) {
- cell.setCellValue(StringUtils.contains(toStr(value), ".") ? toDouble(value) : toInt(value));
- } else if (ColumnType.IMAGE == attr.cellType()) {
- ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(),
- cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
- String imagePath = toStr(value);
- if (StringUtils.isNotEmpty(imagePath)) {
- byte[] data = getImage(imagePath);
- getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
- cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
- }
- }
- }
- /**
- * 获取画布
- */
- public static Drawing<?> getDrawingPatriarch(Sheet sheet) {
- if (sheet.getDrawingPatriarch() == null) {
- sheet.createDrawingPatriarch();
- }
- return sheet.getDrawingPatriarch();
- }
- /**
- * 获取图片类型,设置图片插入类型
- */
- public int getImageType(byte[] value) {
- String type = getFileExtendName(value);
- if ("JPG".equalsIgnoreCase(type)) {
- return Workbook.PICTURE_TYPE_JPEG;
- } else if ("PNG".equalsIgnoreCase(type)) {
- return Workbook.PICTURE_TYPE_PNG;
- }
- return Workbook.PICTURE_TYPE_JPEG;
- }
- /**
- * 创建表格样式
- */
- public void setDataValidation(Excel attr, Row row, int column) {
- if (attr.name().indexOf("注:") >= 0) {
- sheet.setColumnWidth(column, 6000);
- } else {
- // 设置列宽
- sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
- }
- // 如果设置了提示信息则鼠标放上去提示.
- if (StringUtils.isNotEmpty(attr.prompt())) {
- // 这里默认设了2-101列提示.
- setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
- }
- // 如果设置了combo属性则本列只能选择不能输入
- if (attr.combo().length > 0) {
- // 这里默认设了2-101列只能选择不能输入.
- setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
- }
- }
- /**
- * 添加单元格
- */
- @SuppressWarnings("deprecation")
- public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
- Cell cell = null;
- try {
- // 设置行高
- row.setHeight(maxHeight);
- // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
- if (attr.isExport()) {
- // 创建cell
- cell = row.createCell(column);
- int align = attr.align().value();
- cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
- // 用于读取对象中的属性
- Object value = getTargetValue(vo, field, attr);
- String dateFormat = attr.dateFormat();
- String readConverterExp = attr.readConverterExp();
- String separator = attr.separator();
- String enumsName = attr.enumsName();
- String methodName="getValueByCode";
- if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
- Class<?> clazz=Class.forName(enumsName);
- Object[] os=clazz.getEnumConstants();
- Method method=clazz.getDeclaredMethod(methodName,Integer.class);
- Object s=method.invoke(clazz,value);
- setCellVo(s.toString(), attr, cell);
- }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
- cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
- } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
- cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
- } else if (value instanceof BigDecimal && -1 != attr.scale()) {
- cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
- } else{
- // 设置列类型
- setCellVo(value, attr, cell);
- }
- addStatisticsData(column, toStr(value), attr);
- }
- } catch (Exception e) {
- log.error("导出Excel失败{}", e);
- }
- return cell;
- }
- public Cell addOrderMegerCell(Excel attr, Row row, T vo, Field field, int column) {
- Cell cell = null;
- try {
- // 设置行高
- row.setHeight(maxHeight);
- // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
- if (attr.isExport()) {
- // 创建cell
- cell = row.createCell(column);
- int align = attr.align().value();
- cell.setCellStyle(styles.get("data4"));
- // 用于读取对象中的属性
- Object value = getTargetValue(vo, field, attr);
- String dateFormat = attr.dateFormat();
- String readConverterExp = attr.readConverterExp();
- String separator = attr.separator();
- String enumsName = attr.enumsName();
- String methodName="getValueByCode";
- if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
- Class<?> clazz=Class.forName(enumsName);
- Object[] os=clazz.getEnumConstants();
- Method method=clazz.getDeclaredMethod(methodName,Integer.class);
- Object s=method.invoke(clazz,value);
- setCellVo(s.toString(), attr, cell);
- }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
- cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
- } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
- cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
- } else if (value instanceof BigDecimal && -1 != attr.scale()) {
- cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
- } else{
- // 设置列类型
- setCellVo(value, attr, cell);
- }
- addStatisticsData(column, toStr(value), attr);
- }
- } catch (Exception e) {
- log.error("导出Excel失败{}", e);
- }
- return cell;
- }
- /**
- * 设置 POI XSSFSheet 单元格提示
- *
- * @param sheet 表单
- * @param promptTitle 提示标题
- * @param promptContent 提示内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- */
- public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
- int firstCol, int endCol) {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- dataValidation.createPromptBox(promptTitle, promptContent);
- dataValidation.setShowPromptBox(true);
- sheet.addValidationData(dataValidation);
- }
- /**
- * 设置某些列的值只能输入预制的数据,显示下拉框.
- *
- * @param sheet 要设置的sheet.
- * @param textlist 下拉框显示的内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- * @return 设置好的sheet.
- */
- public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- // 加载下拉列表内容
- DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
- // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- // 数据有效性对象
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- // 处理Excel兼容性问题
- if (dataValidation instanceof XSSFDataValidation) {
- dataValidation.setSuppressDropDownArrow(true);
- dataValidation.setShowErrorBox(true);
- } else {
- dataValidation.setSuppressDropDownArrow(false);
- }
- sheet.addValidationData(dataValidation);
- }
- /**
- * 解析导出值 0=男,1=女,2=未知
- *
- * @param propertyValue 参数值
- * @param converterExp 翻译注解
- * @param separator 分隔符
- * @return 解析后值
- */
- public static String convertByExp(String propertyValue, String converterExp, String separator) {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource) {
- String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue)) {
- for (String value : propertyValue.split(separator)) {
- if (itemArray[0].equals(value)) {
- propertyString.append(itemArray[1] + separator);
- break;
- }
- }
- } else {
- if (itemArray[0].equals(propertyValue)) {
- return itemArray[1];
- }
- }
- }
- return StringUtils.stripEnd(propertyString.toString(), separator);
- }
- /**
- * 反向解析值 男=0,女=1,未知=2
- *
- * @param propertyValue 参数值
- * @param converterExp 翻译注解
- * @param separator 分隔符
- * @return 解析后值
- */
- public static String reverseByExp(String propertyValue, String converterExp, String separator) {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource) {
- String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue)) {
- for (String value : propertyValue.split(separator)) {
- if (itemArray[1].equals(value)) {
- propertyString.append(itemArray[0] + separator);
- break;
- }
- }
- } else {
- if (itemArray[1].equals(propertyValue)) {
- return itemArray[0];
- }
- }
- }
- return StringUtils.stripEnd(propertyString.toString(), separator);
- }
- /**
- * 合计统计信息
- */
- private void addStatisticsData(Integer index, String text, Excel entity) {
- if (entity != null && entity.isStatistics()) {
- Double temp = 0D;
- if (!statistics.containsKey(index)) {
- statistics.put(index, temp);
- }
- try {
- temp = Double.valueOf(text);
- } catch (NumberFormatException e) {
- }
- statistics.put(index, statistics.get(index) + temp);
- }
- }
- /**
- * 创建统计行
- */
- public void addStatisticsRow() {
- if (statistics.size() > 0) {
- Cell cell = null;
- Row row = sheet.createRow(sheet.getLastRowNum() + 1);
- Set<Integer> keys = statistics.keySet();
- cell = row.createCell(0);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue("合计");
- for (Integer key : keys) {
- cell = row.createCell(key);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
- }
- statistics.clear();
- }
- }
- /**
- * 编码文件名
- */
- public String encodingFilename(String filename) {
- filename = filename + "_" +UUID.randomUUID().toString() + ".xlsx";
- return filename;
- }
- /**
- * 获取下载路径
- *
- * @param filename 文件名称
- */
- public String getAbsoluteFile(String filename) {
- String downloadPath = this.downloadPath+"/" + filename;
- File desc = new File(downloadPath);
- if (!desc.getParentFile().exists()) {
- desc.getParentFile().mkdirs();
- }
- log.debug(downloadPath);
- return downloadPath;
- }
- /**
- * 获取bean中的属性值
- *
- * @param vo 实体对象
- * @param field 字段
- * @param excel 注解
- * @return 最终的属性值
- * @throws Exception
- */
- private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
- Object o = field.get(vo);
- if (StringUtils.isNotEmpty(excel.targetAttr())) {
- String target = excel.targetAttr();
- if (target.indexOf(".") > -1) {
- String[] targets = target.split("[.]");
- for (String name : targets) {
- o = getValue(o, name);
- }
- } else {
- o = getValue(o, target);
- }
- }
- return o;
- }
- /**
- * 以类的属性的get方法方法形式获取值
- *
- * @param o
- * @param name
- * @return value
- * @throws Exception
- */
- private Object getValue(Object o, String name) throws Exception {
- if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
- Class<?> clazz = o.getClass();
- Field field = clazz.getDeclaredField(name);
- field.setAccessible(true);
- o = field.get(o);
- }
- return o;
- }
- /**
- * 得到所有定义字段
- */
- private void createExcelField() {
- this.fields = new ArrayList<Object[]>();
- List<Field> tempFields = new ArrayList<>();
- tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
- tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
- for (Field field : tempFields) {
- // 单注解
- if (field.isAnnotationPresent(Excel.class)) {
- putToField(field, field.getAnnotation(Excel.class));
- }
- // 多注解
- if (field.isAnnotationPresent(Excels.class)) {
- Excels attrs = field.getAnnotation(Excels.class);
- Excel[] excels = attrs.value();
- for (Excel excel : excels) {
- putToField(field, excel);
- }
- }
- }
- this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort()))
- .collect(Collectors.toList());
- this.maxHeight = getRowHeight();
- }
- /**
- * 根据注解获取最大行高
- */
- public short getRowHeight() {
- double maxHeight = 0;
- for (Object[] os : this.fields) {
- Excel excel = (Excel) os[1];
- maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
- }
- return (short) (maxHeight * 20);
- }
- /**
- * 放到字段集合中
- */
- private void putToField(Field field, Excel attr) {
- if (attr != null) {
- this.fields.add(new Object[] { field, attr });
- }
- }
- /**
- * 创建一个工作簿
- */
- public void createWorkbook() {
- this.wb = new XSSFWorkbook();
- }
- /**
- * 创建工作表
- *
- * @param sheetNo sheet数量
- * @param index 序号
- */
- public void createSheet(int sheetNo, int index) {
- this.sheet = wb.createSheet();
- this.styles = createStyles(wb);
- // 设置工作表的名称.
- if (sheetNo == 0) {
- wb.setSheetName(index, sheetName);
- } else {
- wb.setSheetName(index, sheetName + index);
- }
- }
- /**
- * 获取单元格值
- *
- * @param row 获取的行
- * @param column 获取单元格列号
- * @return 单元格值
- */
- public Object getCellValue(Row row, int column) {
- if (row == null) {
- return row;
- }
- Object val = "";
- try {
- Cell cell = row.getCell(column);
- if (StringUtils.isNotNull(cell)) {
- if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
- val = cell.getNumericCellValue();
- if (DateUtil.isCellDateFormatted(cell)) {
- val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
- } else {
- if ((Double) val % 1 != 0) {
- val = new BigDecimal(val.toString());
- } else {
- val = new DecimalFormat("0").format(val);
- }
- }
- } else if (cell.getCellTypeEnum() == CellType.STRING) {
- val = cell.getStringCellValue();
- } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
- val = cell.getBooleanCellValue();
- } else if (cell.getCellTypeEnum() == CellType.ERROR) {
- val = cell.getErrorCellValue();
- }
- }
- } catch (Exception e) {
- return val;
- }
- return val;
- }
- /**
- * 转换为字符串<br>
- * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static String toStr(Object value) {
- return toStr(value, null);
- }
- /**
- * 转换为字符串<br>
- * 如果给定的值为null,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static String toStr(Object value, String defaultValue) {
- if (null == value) {
- return defaultValue;
- }
- if (value instanceof String) {
- return (String) value;
- }
- return value.toString();
- }
- /**
- * 转换为double<br>
- * 如果给定的值为空,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static Double toDouble(Object value) {
- return toDouble(value, null);
- }
- /**
- * 转换为double<br>
- * 如果给定的值为空,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static Double toDouble(Object value, Double defaultValue) {
- if (value == null) {
- return defaultValue;
- }
- if (value instanceof Double) {
- return (Double) value;
- }
- if (value instanceof Number) {
- return ((Number) value).doubleValue();
- }
- final String valueStr = toStr(value, null);
- if (StringUtils.isEmpty(valueStr)) {
- return defaultValue;
- }
- try {
- // 支持科学计数法
- return new BigDecimal(valueStr.trim()).doubleValue();
- } catch (Exception e) {
- return defaultValue;
- }
- }
- /**
- * 转换为int<br>
- * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static Integer toInt(Object value) {
- return toInt(value, null);
- }
- /**
- * 转换为int<br>
- * 如果给定的值为空,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static Integer toInt(Object value, Integer defaultValue) {
- if (value == null) {
- return defaultValue;
- }
- if (value instanceof Integer) {
- return (Integer) value;
- }
- if (value instanceof Number) {
- return ((Number) value).intValue();
- }
- final String valueStr = toStr(value, null);
- if (StringUtils.isEmpty(valueStr)) {
- return defaultValue;
- }
- try {
- return Integer.parseInt(valueStr.trim());
- } catch (Exception e) {
- return defaultValue;
- }
- }
- /**
- * 获取文件类型
- *
- * @param photoByte 文件字节码
- * @return 后缀(不含".")
- */
- public static String getFileExtendName(byte[] photoByte) {
- String strFileExtendName = "JPG";
- if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
- && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97)) {
- strFileExtendName = "GIF";
- } else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70)) {
- strFileExtendName = "JPG";
- } else if ((photoByte[0] == 66) && (photoByte[1] == 77)) {
- strFileExtendName = "BMP";
- } else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71)) {
- strFileExtendName = "PNG";
- }
- return strFileExtendName;
- }
- public static byte[] getImage(String imagePath) {
- InputStream is = getFile(imagePath);
- try {
- return IOUtils.toByteArray(is);
- } catch (Exception e) {
- log.error("图片加载异常 {}", e);
- return null;
- } finally {
- IOUtils.closeQuietly(is);
- }
- }
- public static InputStream getFile(String imagePath) {
- try {
- byte[] result = readFile(imagePath);
- result = Arrays.copyOf(result, result.length);
- return new ByteArrayInputStream(result);
- } catch (Exception e) {
- log.error("获取图片异常 {}", e);
- }
- return null;
- }
- /**
- * 读取文件为字节数据
- *
- * @return 字节数据
- */
- public static byte[] readFile(String url) {
- InputStream in = null;
- ByteArrayOutputStream baos = null;
- try {
- if (url.startsWith("http")) {
- // 网络地址
- URL urlObj = new URL(url);
- URLConnection urlConnection = urlObj.openConnection();
- urlConnection.setConnectTimeout(30 * 1000);
- urlConnection.setReadTimeout(60 * 1000);
- urlConnection.setDoInput(true);
- in = urlConnection.getInputStream();
- } else {
- // 本机地址(需修改)
- String localPath = "127.0.0.1";
- String downloadPath = localPath + StringUtils.substringAfter(url, "/profile");
- in = new FileInputStream(downloadPath);
- }
- return IOUtils.toByteArray(in);
- } catch (Exception e) {
- log.error("获取文件路径异常 {}", e);
- return null;
- } finally {
- IOUtils.closeQuietly(baos);
- }
- }
- /**
- * 对excel表单默认第一个索引名转换成list
- *
- * @param is 输入流
- * @return 转换后集合
- */
- public List<T> importExcel(InputStream is) throws Exception
- {
- return importExcel(is, 0);
- }
- /**
- * 对excel表单默认第一个索引名转换成list
- *
- * @param is 输入流
- * @param titleNum 标题占用行数
- * @return 转换后集合
- */
- public List<T> importExcel(InputStream is, int titleNum) throws Exception
- {
- return importExcel(StringUtils.EMPTY, is, titleNum);
- }
- //
- public List<T> importExcelSet( InputStream is, int titleNum) throws Exception {
- this.type = Excel.Type.IMPORT;
- this.wb = WorkbookFactory.create(is);
- List<T> list = new ArrayList<T>();
- Iterator<Sheet> sheetIterator = wb.sheetIterator();
- while (sheetIterator.hasNext()) {
- Sheet sheet = sheetIterator.next();
- if (sheet == null) {
- throw new IOException("文件sheet不存在");
- }
- boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
- Map<String, PictureData> pictures;
- if (isXSSFWorkbook) {
- pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
- } else {
- pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
- }
- // 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
- int rows = sheet.getLastRowNum();
- if (rows > 0) {
- // 定义一个map用于存放excel列的序号和field.
- Map<String, Integer> cellMap = new HashMap<String, Integer>();
- // 获取表头
- Row heard = sheet.getRow(titleNum);
- for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
- Cell cell = heard.getCell(i);
- if (StringUtils.isNotNull(cell)) {
- String value = this.getCellValue(heard, i).toString();
- cellMap.put(value, i);
- } else {
- cellMap.put(null, i);
- }
- }
- // 有数据时才处理 得到类的所有field.
- List<Object[]> fields = this.getFields();
- Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
- for (Object[] objects : fields) {
- Excel attr = (Excel) objects[1];
- Integer column = cellMap.get(attr.name());
- if (column != null) {
- fieldsMap.put(column, objects);
- }
- }
- int errorCount = 0;
- try {
- for (int i = titleNum + 1; i <= rows; i++) {
- errorCount = i + 1;
- // 从第2行开始取数据,默认第一行是表头.
- Row row = sheet.getRow(i);
- // 判断当前行是否是空行
- if (isRowEmpty(row)) {
- continue;
- }
- T entity = null;
- for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {
- Object val = this.getCellValue(row, entry.getKey());
- // 如果不存在实例则新建.
- entity = (entity == null ? clazz.newInstance() : entity);
- // 从map中得到对应列的field.
- Field field = (Field) entry.getValue()[0];
- Excel attr = (Excel) entry.getValue()[1];
- // 取得类型,并根据对象类型设置值.
- Class<?> fieldType = field.getType();
- if (String.class == fieldType) {
- String s = Convert.toStr(val);
- if (StringUtils.endsWith(s, ".0")) {
- val = StringUtils.substringBefore(s, ".0");
- } else {
- String dateFormat = field.getAnnotation(Excel.class).dateFormat();
- if (StringUtils.isNotEmpty(dateFormat)) {
- val = parseDateToStr(dateFormat, val);
- } else {
- val = Convert.toStr(val);
- }
- }
- } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
- val = Convert.toInt(val);
- } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
- val = Convert.toLong(val);
- } else if (Double.TYPE == fieldType || Double.class == fieldType) {
- val = Convert.toDouble(val);
- } else if (Float.TYPE == fieldType || Float.class == fieldType) {
- val = Convert.toFloat(val);
- } else if (BigDecimal.class == fieldType) {
- val = Convert.toBigDecimal(val);
- } else if (Date.class == fieldType) {
- if (val instanceof String) {
- val = DateUtils.parseDate((String) val);
- } else if (val instanceof Double) {
- val = DateUtil.getJavaDate((Double) val);
- }
- } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
- val = Convert.toBool(val, false);
- }
- if (StringUtils.isNotNull(fieldType)) {
- String propertyName = field.getName();
- if (StringUtils.isNotEmpty(attr.targetAttr())) {
- propertyName = field.getName() + "." + attr.targetAttr();
- } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
- val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
- } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
- val = dataFormatHandlerAdapter(val, attr);
- } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty((CharSequence) pictures)) {
- PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
- if (image == null) {
- val = "";
- } else {
- byte[] data = image.getData();
- val = FileUtils.writeImportBytes(data);
- }
- }
- ReflectUtils.invokeSetter(entity, propertyName, val);
- }
- }
- list.add(entity);
- }
- } catch (Exception e) {
- throw new BusinessException(String.format("表格第%s行内容错误,请检查修改后再上传。", errorCount));
- }
- }
- }
- return list;
- }
- /**
- * 对excel表单指定表格索引名转换成list
- *
- * @param sheetName 表格索引名
- * @param titleNum 标题占用行数
- * @param is 输入流
- * @return 转换后集合
- */
- public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
- {
- this.type = Excel.Type.IMPORT;
- this.wb = WorkbookFactory.create(is);
- List<T> list = new ArrayList<T>();
- // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
- Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
- if (sheet == null)
- {
- throw new IOException("文件sheet不存在");
- }
- boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
- Map<String, PictureData> pictures;
- if (isXSSFWorkbook)
- {
- pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
- }
- else
- {
- pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
- }
- // 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
- int rows = sheet.getLastRowNum();
- if (rows > 0){
- // 定义一个map用于存放excel列的序号和field.
- Map<String, Integer> cellMap = new HashMap<String, Integer>();
- // 获取表头
- Row heard = sheet.getRow(titleNum);
- for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
- {
- Cell cell = heard.getCell(i);
- if (StringUtils.isNotNull(cell))
- {
- String value = this.getCellValue(heard, i).toString();
- cellMap.put(value, i);
- }
- else
- {
- cellMap.put(null, i);
- }
- }
- // 有数据时才处理 得到类的所有field.
- List<Object[]> fields = this.getFields();
- Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
- for (Object[] objects : fields)
- {
- Excel attr = (Excel) objects[1];
- Integer column = cellMap.get(attr.name());
- if (column != null)
- {
- fieldsMap.put(column, objects);
- }
- }
- int errorCount=0;
- try {
- for (int i = titleNum + 1; i <= rows; i++){
- errorCount=i+1;
- // 从第2行开始取数据,默认第一行是表头.
- Row row = sheet.getRow(i);
- // 判断当前行是否是空行
- if (isRowEmpty(row))
- {
- continue;
- }
- T entity = null;
- for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())
- {
- Object val = this.getCellValue(row, entry.getKey());
- // 如果不存在实例则新建.
- entity = (entity == null ? clazz.newInstance() : entity);
- // 从map中得到对应列的field.
- Field field = (Field) entry.getValue()[0];
- Excel attr = (Excel) entry.getValue()[1];
- // 取得类型,并根据对象类型设置值.
- Class<?> fieldType = field.getType();
- if (String.class == fieldType)
- {
- String s = Convert.toStr(val);
- if (StringUtils.endsWith(s, ".0"))
- {
- val = StringUtils.substringBefore(s, ".0");
- }
- else
- {
- String dateFormat = field.getAnnotation(Excel.class).dateFormat();
- if (StringUtils.isNotEmpty(dateFormat))
- {
- val = parseDateToStr(dateFormat, val);
- }
- else
- {
- val = Convert.toStr(val);
- }
- }
- }
- else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
- {
- val = Convert.toInt(val);
- }
- else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
- {
- val = Convert.toLong(val);
- }
- else if (Double.TYPE == fieldType || Double.class == fieldType)
- {
- val = Convert.toDouble(val);
- }
- else if (Float.TYPE == fieldType || Float.class == fieldType)
- {
- val = Convert.toFloat(val);
- }
- else if (BigDecimal.class == fieldType)
- {
- val = Convert.toBigDecimal(val);
- }
- else if (Date.class == fieldType)
- {
- if (val instanceof String)
- {
- val = DateUtils.parseDate((String) val);
- }
- else if (val instanceof Double)
- {
- val = DateUtil.getJavaDate((Double) val);
- }
- }
- else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
- {
- val = Convert.toBool(val, false);
- }
- if (StringUtils.isNotNull(fieldType))
- {
- String propertyName = field.getName();
- if (StringUtils.isNotEmpty(attr.targetAttr()))
- {
- propertyName = field.getName() + "." + attr.targetAttr();
- }
- else if (StringUtils.isNotEmpty(attr.readConverterExp()))
- {
- val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
- }
- else if (!attr.handler().equals(ExcelHandlerAdapter.class))
- {
- val = dataFormatHandlerAdapter(val, attr);
- }
- else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty((CharSequence) pictures))
- {
- PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
- if (image == null)
- {
- val = "";
- }
- else
- {
- byte[] data = image.getData();
- val = FileUtils.writeImportBytes(data);
- }
- }
- ReflectUtils.invokeSetter(entity, propertyName, val);
- }
- }
- list.add(entity);
- }
- }catch (Exception e){
- throw new BusinessException(String.format("表格第%s行内容错误,请检查修改后再上传。",errorCount));
- }
- }
- return list;
- }
- /**
- * 判断是否是空行
- *
- * @param row 判断的行
- * @return
- */
- private boolean isRowEmpty(Row row)
- {
- if (row == null)
- {
- return true;
- }
- for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
- {
- Cell cell = row.getCell(i);
- if (cell != null && cell.getCellTypeEnum() != CellType.BLANK)
- {
- return false;
- }
- }
- return true;
- }
- /**
- * 格式化不同类型的日期对象
- *
- * @param dateFormat 日期格式
- * @param val 被格式化的日期对象
- * @return 格式化后的日期字符
- */
- public String parseDateToStr(String dateFormat, Object val)
- {
- if (val == null)
- {
- return "";
- }
- String str;
- if (val instanceof Date)
- {
- str = DateUtils.parseDateToStr(dateFormat, (Date) val);
- }
- else if (val instanceof LocalDateTime)
- {
- str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate(LocalDate.from((LocalDateTime) val)));
- }
- else if (val instanceof LocalDate)
- {
- str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));
- }
- else
- {
- str = val.toString();
- }
- return str;
- }
- /**
- * 获取Excel2003图片
- *
- * @param sheet 当前sheet对象
- * @param workbook 工作簿对象
- * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
- */
- public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
- {
- Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
- List<HSSFPictureData> pictures = workbook.getAllPictures();
- if (!pictures.isEmpty())
- {
- for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
- {
- HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
- if (shape instanceof HSSFPicture)
- {
- HSSFPicture pic = (HSSFPicture) shape;
- int pictureIndex = pic.getPictureIndex() - 1;
- HSSFPictureData picData = pictures.get(pictureIndex);
- String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
- sheetIndexPicMap.put(picIndex, picData);
- }
- }
- return sheetIndexPicMap;
- }
- else
- {
- return sheetIndexPicMap;
- }
- }
- /**
- * 获取Excel2007图片
- *
- * @param sheet 当前sheet对象
- * @param workbook 工作簿对象
- * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
- */
- public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
- {
- Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
- for (POIXMLDocumentPart dr : sheet.getRelations())
- {
- if (dr instanceof XSSFDrawing)
- {
- XSSFDrawing drawing = (XSSFDrawing) dr;
- List<XSSFShape> shapes = drawing.getShapes();
- for (XSSFShape shape : shapes)
- {
- if (shape instanceof XSSFPicture)
- {
- XSSFPicture pic = (XSSFPicture) shape;
- XSSFClientAnchor anchor = pic.getPreferredSize();
- CTMarker ctMarker = anchor.getFrom();
- String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
- sheetIndexPicMap.put(picIndex, pic.getPictureData());
- }
- }
- }
- }
- return sheetIndexPicMap;
- }
- /**
- * 获取字段注解信息
- */
- public List<Object[]> getFields()
- {
- List<Object[]> fields = new ArrayList<Object[]>();
- List<Field> tempFields = new ArrayList<>();
- tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
- tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
- for (Field field : tempFields)
- {
- // 单注解
- if (field.isAnnotationPresent(Excel.class))
- {
- Excel attr = field.getAnnotation(Excel.class);
- if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
- {
- field.setAccessible(true);
- fields.add(new Object[] { field, attr });
- }
- }
- // 多注解
- if (field.isAnnotationPresent(Excels.class))
- {
- Excels attrs = field.getAnnotation(Excels.class);
- Excel[] excels = attrs.value();
- for (Excel attr : excels)
- {
- if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
- {
- field.setAccessible(true);
- fields.add(new Object[] { field, attr });
- }
- }
- }
- }
- return fields;
- }
- /**
- * 数据处理器
- *
- * @param value 数据值
- * @param excel 数据注解
- * @return
- */
- public String dataFormatHandlerAdapter(Object value, Excel excel)
- {
- try
- {
- Object instance = excel.handler().newInstance();
- Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
- value = formatMethod.invoke(instance, value, excel.args());
- }
- catch (Exception e)
- {
- log.error("不能格式化数据 " + excel.handler(), e.getMessage());
- }
- return Convert.toStr(value);
- }
- public Result exportTaskDetailsExcel(TaskDetailsBo byTid, String sheetName, String uploadPath) {
- OutputStream out = null;
- String filename="";
- downloadPath=uploadPath;
- uploadPath=uploadPath+"/"+"tmp/member_progress.xls";
- try {
- FileInputStream fileInputStream = new FileInputStream(uploadPath);
- wb = new HSSFWorkbook(fileInputStream);
- styles=createStyles(wb);
- sheet = wb.getSheetAt(0);
- Row row = sheet.createRow(6);
- filename = encodingFilename(sheetName);
- out = Files.newOutputStream(Paths.get(getAbsoluteFile(filename)));
- addCell( row,byTid);
- wb.write(out);
- return new Result().data(filename);
- } catch (Exception e) {
- e.printStackTrace();
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- if (out != null) {
- try {
- out.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
- private void addObjectCell(Row row, OutTaskDetailsBo byTid) {
- Class<?> clazz = byTid.getClass();
- int i = 0;
- for (Field field :clazz.getDeclaredFields()){
- field.setAccessible(true);
- Cell cell = row.createCell(i);
- cell.setCellStyle(styles.get("data4"));
- try {
- // System.out.println(String.format("name=%s,value=%s",field.getName(),field.get(byTid)));
- Object o = field.get(byTid);
- } catch (IllegalAccessException e) {
- throw new RuntimeException(e);
- }
- }
- }
- private void addCell(Row row,TaskDetailsBo o) {
- for (int i = 0; i < 70; i++) {
- Cell cell = row.createCell(i);
- cell.setCellStyle(styles.get("data4"));
- if (i==0)cell.setCellValue(1);//序号
- else if(i==1)cell.setCellValue(o.getUserName());//客户名称
- else if(i==2)cell.setCellValue(o.getContractNo());//合同编号
- else if(i==3)cell.setCellValue(o.getServiceProject());//服务内容
- else if(i==4){//服务年限
- cell.setCellValue(o.getServiceYear());
- }
- else if(i==5)cell.setCellValue(o.getMemberLvl());//会员级别
- else if(i==6)cell.setCellValue(o.getTaskName()); //业务负责人
- else if(i==7)cell.setCellValue(o.getProjectName()); //项目负责人
- else if(i==8)cell.setCellValue(o.getTechnologyName()); //技术咨询师
- else if(i==9)cell.setCellValue(o.getFinanceTaxationName()); //财税咨询师
- else if(i==10)cell.setCellValue(getBigDecimal(o.getBeforeLastRdSalesAmount())); //上上年度销售收入
- else if(i==11)cell.setCellValue(getBigDecimal(o.getBeforeLastRdBudget())); //上上年度研发费用
- else if(i==12)cell.setCellValue(getBigDecimal(o.getLastRdSalesAmount())); //上年度销售收入
- else if(i==13)cell.setCellValue(getBigDecimal(o.getRdSalesAmount())); //上年度研发费用
- else if(i==14)cell.setCellValue(o.getWorkersNumber());//职工人数
- else if(i==15)cell.setCellValue(o.getTechnologyNumber());//科技人员
- else if(i==16)cell.setCellValue(o.getWorkersRate()+"%");//人员占比
- else if(i==17)cell.setCellValue(o.getProjectApprovalNumber());//研发立项数
- else if(i==18)cell.setCellValue(getBigDecimal(o.getRdBudget()));//年度研发预算总额
- else if(i==19)cell.setCellValue(getBigDecimal(o.getRdSalesAmount()));//年度销售预计收入
- else if(i==20)cell.setCellValue(o.getRdBudgetRate()+"%");//年度研发费用预算占比
- else if(i==21)cell.setCellValue(o.getThreeYearsActualRdRate()+"%");//三年度研发费用占比
- else if(i==22){//数据周期
- if (o.getDataCycle()==null){
- cell.setCellValue("");
- }else {
- switch (o.getDataCycle())
- {
- case 0:
- cell.setCellValue("第一季度");
- break;
- case 1: cell.setCellValue("上半年"); break;
- case 2: cell.setCellValue("前三季度"); break;
- case 3: cell.setCellValue("全年"); break;
- }
- }
- }else if(i==23){//已立项RD数
- cell.setCellValue(o.getActualRdNumber());
- }else if(i==24){//委外项目数
- cell.setCellValue(o.getActualOutsourcingNumber());
- }
- else if(i==25){//合作项目数
- cell.setCellValue(o.getActualCooperateNumber());
- }
- else if(i==26){//已归集研发费用总额
- cell.setCellValue(o.getActualRdAmount().stripTrailingZeros().toPlainString());
- }
- else if(i==27){//研发预算完成度
- cell.setCellValue(o.getRdCompletionProgress()+"%");
- }
- else if(i==28){//年度实际销售额
- cell.setCellValue(getBigDecimal(o.getActualSalesAmount()));
- }
- else if(i==29){//年度研发费用占比
- cell.setCellValue(o.getActualRdRate()+"%");
- }
- else if(i==30){//三年研发费用占比
- cell.setCellValue(o.getThreeYearsActualRdRate()+"%");
- }
- else if(i==31){//火炬统计
- addParam(cell,o.getTorchStatus(),o.getTorchOther());
- }
- else if(i==32){//统计年报
- addParam(cell,o.getStatisticsStatus(),o.getStatisticsOther());
- }
- else if(i==33){//高企年报
- addParam(cell,o.getChStatus(),o.getChOther());
- }
- else if(i==34){//汇算清缴-是否完成
- addParam(cell,o.getFinalSettlementStatus(),o.getFinalSettlementOther());
- }
- else if(i==35){//汇算清缴-加扣RD数
- cell.setCellValue(o.getReportFormsRdNumber());
- }
- else if(i==36){//汇算清缴-知识产权加扣费用
- cell.setCellValue(getBigDecimal(o.getIntellectualPropertyAmount()));
- }
- else if(i==37){//汇算清缴-加扣研发费用金额
- cell.setCellValue(getBigDecimal(o.getReportRdAmount()));
- }
- else if(i==38){//研发准备金备案
- addParam(cell,o.getRdReservesStatus(),o.getRdReservesOther());
- }
- else if(i==39){//研发奖补申请-是否完成
- addParam(cell,o.getRdAwardCompensationStatus(),o.getRdAwardCompensationOther());
- }
- else if(i==40){//研发奖补申请-申请金额
- cell.setCellValue(getBigDecimal(o.getRdApplicationFee()));
- }
- else if(i==41){//研发奖补申请-公示金额
- cell.setCellValue(getBigDecimal(o.getRdAnnouncementAmount()));
- }
- else if(i==42){//研发奖补申请-其他
- cell.setCellValue(o.getRdAnnouncementOther());
- }
- else if(i==43){//加扣备查交付
- addParam(cell,o.getAdDeliverStatus(),o.getAdDeliverOther());
- }
- else if(i==44){//加扣是否归档
- if(o.getAdDeliverFinish()==null){
- cell.setCellValue("");
- }else if(o.getAdDeliverFinish()==1){
- cell.setCellValue("是");
- }else if(o.getAdDeliverFinish()==0){
- cell.setCellValue("否");
- }
- }
- else if(i==45){//高新备查交付
- addParam(cell,o.getHtDeliverStatus(),o.getHtDeliverOther());
- }
- else if(i==46){//高新是否归档
- if (o.getHtDeliverFinish()==null){
- cell.setCellValue("");
- }else if(o.getHtDeliverFinish()==1){
- cell.setCellValue("是");
- }else if(o.getHtDeliverFinish()==0){
- cell.setCellValue("否");
- }
- }
- else if(i==47){//召开上年度会议于本年度工作计划
- addParam(cell,o.getCloseMeetingStatus(),o.getCloseMeetingOther());
- }
- else if(i==48){//阶段性汇报
- StringBuilder res= new StringBuilder();
- if (o.getPhasedType()==1){
- res.append("月度- ");
- }else if (o.getPhasedType()==2){
- res.append("季度- ");
- }
- String[] split = o.getPhasedContent().split(",");
- for (String s : split) {
- res.append(s).append("月 ,");
- }
- if(res.toString().contains(",")){
- cell.setCellValue(res.substring(0,res.length()-1));
- }else {
- cell.setCellValue("");
- }
- }
- else if(i==49){//立项资料编制-完成数
- cell.setCellValue(o.getProjectApprovalWriteNumber());
- }else if(i==50){//立项资料编制-完成率
- cell.setCellValue(o.getProjectApprovalWriteRate()+"%");
- }else if(i==51){//加扣预计申报7月
- addParam(cell,o.getAdPrepareJulyStatus(),o.getAdPrepareJulyOther());
- }else if(i==52){//加扣预计申报10月
- addParam(cell,o.getAdPrepareOctoberStatus(),o.getAdPrepareOctoberOther());
- }else if(i==53){//知识产权-发明专利-申报数
- cell.setCellValue(o.getIpInventionPatentDeclare());
- }else if(i==54){//知识产权-发明专利-授权数
- cell.setCellValue(o.getIpInventionPatentAuthorize());
- }else if(i==55){///知识产权-发明专利-累计授权数
- cell.setCellValue(o.getIpInventionPatentAuthorizeCount());
- }else if(i==56){//实用新型-申报数
- cell.setCellValue(o.getIpUtilityModelDeclare());
- }else if(i==57){//实用新型-授权数
- cell.setCellValue(o.getIpUtilityModelAuthorize());
- }else if(i==58){//实用新型-累计授权数
- cell.setCellValue(o.getIpUtilityModelAuthorizeCount());
- }else if(i==59){//软著-申报数
- cell.setCellValue(o.getIpSoftwareWorksDeclare());
- }else if(i==60){//软著-授权数
- cell.setCellValue(o.getIpSoftwareWorksAuthorize());
- }else if(i==61){//软著-累计授权数
- cell.setCellValue(o.getIpSoftwareWorksAuthorizeCount());
- }else if(i==62){//软著-截止本年末可用于下次高新认定数量
- cell.setCellValue(o.getIpHt());
- }else if(i==63){//软著-I类数量
- cell.setCellValue(o.getIpHtI());
- }else if(i==64){//软著-II类数量
- cell.setCellValue(o.getIpHtIi());
- }else if(i==65){//公出指标-合同约定次数
- cell.setCellValue(o.getPublicReleaseStipulation());
- }else if(i==66){//公出指标-实际次数
- cell.setCellValue(o.getPublicReleaseActual());
- }else if(i==67){//培训指标(本年度)-合同约定次数
- cell.setCellValue(o.getTrainingStipulation());
- }else if(i==68){//培训指标(本年度)-实际次数
- cell.setCellValue(o.getTrainingActual());
- }else if(i==69){//培训指标(本年度)-其他
- cell.setCellValue(o.getTrainingOther());
- }else {
- cell.setCellValue("");
- }
- }
- }
- private String getBigDecimal(BigDecimal bigDecimal) {
- if (bigDecimal==null){
- return "";
- }else {
- return bigDecimal.stripTrailingZeros().toPlainString();
- }
- }
- private void addParam(Cell cell, Integer code, String desc) {
- switch (code)
- {
- case 0:
- String res="其他:"+desc;
- cell.setCellValue(res);
- break;
- case 1: cell.setCellValue("待完成"); break;
- case 2: cell.setCellValue("已完成"); break;
- case 3: cell.setCellValue("合同无此项"); break;
- }
- }
- }
|