中国十大大型门户网站软文营销模板

一、使用到的依赖包

1、xelem-3.1.jar 下载地址：管网下载地址

2、poi-3.17.jar 下载地址：https://mvnrepository.com/artifact/org.apache.poi/poi

二、实现方法

1、Xml2003公式转XLSX公式算法

（1）Xml2003函数格式

SUM(R[-1]C+R[-7]C[-4])

R代表当前行，C代表当前列，中括号中的数字代表相对于当前行的偏移量。比如当前操作的是第10行第三列6列，那么上面公式的含义就是第6列9行与第2列3行相加。转换成XLSX的公式就是

SUM(F9-B3)

（2）代码实现

    private final static String[] ExcelColIndexes = new String[]{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M","N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z","AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM","AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ","BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM","BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"};

private static String convertFormula(String xmlFormula, int rowIndex, int colIndex) {if (xmlFormula.equals("=SUM(RC[-2]-RC[-5])")) {System.out.println();}StringBuilder formula = new StringBuilder(xmlFormula.replace("=", ""));Pattern flaPattern = Pattern.compile("R(\\[[-]?\\d+\\])?C(\\[[-]?\\d+\\])?");Pattern rowPattern = Pattern.compile("R(\\[[-]?\\d+\\])?");Pattern colPattern = Pattern.compile("C(\\[[-]?\\d+\\])?");Pattern numberPattern = Pattern.compile("[-]?\\d+");Matcher flaMatcher = flaPattern.matcher(xmlFormula);while (flaMatcher.find()) {String subFla = "";String content = flaMatcher.group();Matcher colMatcher = colPattern.matcher(content);if (colMatcher.find()) {String colFla = colMatcher.group();Matcher numberMatcher = numberPattern.matcher(colFla);if (numberMatcher.find()) {int num = Integer.parseInt(numberMatcher.group());subFla = ExcelColIndexes[colIndex + num];} else {subFla = ExcelColIndexes[colIndex];}}Matcher rowMatcher = rowPattern.matcher(content);if (rowMatcher.find()) {String rowFla = rowMatcher.group();Matcher numberMatcher = numberPattern.matcher(rowFla);if (numberMatcher.find()) {int num = Integer.parseInt(numberMatcher.group());subFla += rowIndex + 1 + num;} else {subFla += rowIndex + 1;}}int start = formula.indexOf(content);int end = start + content.length();formula.replace(start, end, subFla);}return formula.toString();}

2、XML2003十六进制颜色向POI颜色转换

private static byte[] convertColorHexToByteArray(String colorStr) {colorStr = colorStr.replace("#", "");if (colorStr.length() != 6 && colorStr.length() != 8) {throw new IllegalArgumentException("Must be of the form 112233 or FFEEDDCC");} else {byte[] rgb = new byte[colorStr.length() / 2];for (int i = 0; i < rgb.length; ++i) {String part = colorStr.substring(i * 2, (i + 1) * 2);rgb[i] = (byte) Integer.parseInt(part, 16);}return rgb;}}

3、Xml2003转换XLSX代码

 List<CellRangeAddress> mergedRanges = new ArrayList<>();Map<String, CellStyle> cellStyleMap = new HashMap<>();File xmlFile = new File("C:\\Users\\Daibz\\Desktop\\test2.xls");File outFile = new File("C:\\Users\\Daibz\\Desktop\\test.xlsx");ExcelReader reader = new ExcelReader();Workbook workbook = reader.getWorkbook(new InputSource(new FileInputStream(xmlFile)));XSSFWorkbook xssfWorkbook = new XSSFWorkbook();DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();DocumentBuilder builder = factory.newDocumentBuilder();Document doc = builder.parse(xmlFile);NodeList styleList = doc.getElementsByTagName("Style");for (int i = 0; i < styleList.getLength(); i++) {XSSFCellStyle style = xssfWorkbook.createCellStyle();Node node = styleList.item(i);NodeList childNodes = node.getChildNodes();for (int i1 = 0; i1 < childNodes.getLength(); i1++) {Node childNode = childNodes.item(i1);String name = childNode.getNodeName();switch (name) {case "Alignment": {NamedNodeMap attributes = childNode.getAttributes();for (int i2 = 0; i2 < attributes.getLength(); i2++) {Node attr = attributes.item(i2);String value = attr.getNodeValue();switch (attr.getNodeName()) {case "ss:Horizontal": {style.setAlignment(HorizontalAlignment.valueOf(value.toUpperCase()));}break;case "ss:Vertical": {style.setVerticalAlignment(VerticalAlignment.valueOf(value.toUpperCase()));}break;case "ss:WrapText": {style.setWrapText("1".equals(value));}break;}}}break;case "Borders": {NodeList borders = childNode.getChildNodes();for (int i2 = 0; i2 < borders.getLength(); i2++) {Node border = borders.item(i2);NamedNodeMap attributes = border.getAttributes();if (attributes == null)continue;for (int i3 = 0; i3 < attributes.getLength(); i3++) {Node attr = attributes.item(i3);String value = attr.getNodeValue();switch (attr.getNodeName()) {case "ss:Position": {switch (value) {case "Bottom":style.setBorderBottom(BorderStyle.THIN);break;case "Left":style.setBorderLeft(BorderStyle.THIN);break;case "Right":style.setBorderRight(BorderStyle.THIN);break;case "Top":style.setBorderTop(BorderStyle.THIN);break;}}break;case "ss:LineStyle": {}break;case "ss:Weight": {}break;}}}}break;case "Font": {Font font = xssfWorkbook.createFont();NamedNodeMap attributes = childNode.getAttributes();for (int i2 = 0; i2 < attributes.getLength(); i2++) {Node attr = attributes.item(i2);String value = attr.getNodeValue();switch (attr.getNodeName()) {case "ss:FontName": {font.setFontName(value);}break;case "ss:CharSet": {font.setCharSet(Integer.parseInt(value));}break;case "ss:Size": {font.setFontHeightInPoints(Short.parseShort(value));}break;case "ss:Bold": {font.setBold("1".equals(value));}break;}}style.setFont(font);}break;case "Interior": {NamedNodeMap attributes = childNode.getAttributes();for (int i2 = 0; i2 < attributes.getLength(); i2++) {Node attr = attributes.item(i2);String value = attr.getNodeValue();switch (attr.getNodeName()) {case "ss:Color": {XSSFColor color = new XSSFColor(convertColorHexToByteArray(value),xssfWorkbook.getStylesSource().getIndexedColors());style.setFillForegroundColor(color);}break;case "ss:Pattern": {switch (value) {case "Solid":style.setFillPattern(FillPatternType.SOLID_FOREGROUND);break;}}break;}}}break;}}String id = node.getAttributes().getNamedItem("ss:ID").getNodeValue();cellStyleMap.put(id, style);}for (String sheetName : workbook.getSheetNames()) {Worksheet sheet = workbook.getWorksheet(sheetName);XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);int rowIndex = 0;for (Row row : sheet.getRows()) {XSSFRow xssfRow = xssfSheet.createRow(row.getIndex() - 1);int colIndex = 0;int preColIndex = 0;for (Cell cell : row.getCells()) {int mergeAcross = cell.getMergeAcross();int mergeDown = cell.getMergeDown();if (cell.getIndex() - preColIndex != 1) {colIndex += cell.getIndex() - preColIndex - 1;}XSSFCell xssfCell = xssfRow.createCell(colIndex);CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + mergeDown,colIndex, colIndex + mergeAcross);CellStyle cellStyle = cellStyleMap.get(cell.getStyleID());xssfCell.setCellStyle(cellStyle);if (mergeDown != 0 || mergeAcross != 0) {boolean isMerged = false;for (CellRangeAddress mergedRange : mergedRanges) {if (colIndex >= mergedRange.getFirstColumn()&& colIndex <= mergedRange.getLastColumn()&& rowIndex >= mergedRange.getFirstRow()&& rowIndex <= mergedRange.getLastRow()) {colIndex += mergeAcross + 1;isMerged = true;break;}}if (isMerged) {continue;}mergedRanges.add(range);xssfSheet.addMergedRegion(range);}if (cell.getFormula() != null) {xssfCell.setCellFormula(convertFormula(cell.getFormula(), rowIndex, colIndex));} else {Object data = cell.getData();if (data instanceof String) {xssfCell.setCellValue((String) data);} else if (data instanceof Double) {xssfCell.setCellValue((Double) data);} else if (data instanceof Integer) {xssfCell.setCellValue((Integer) data);} else if (data instanceof Short) {xssfCell.setCellValue((Short) data);} else if (data instanceof Float) {xssfCell.setCellValue((Float) data);} else if (data instanceof Date) {xssfCell.setCellValue((Date) data);} else if (data instanceof Calendar) {xssfCell.setCellValue((Calendar) data);} else if (data instanceof RichTextString) {xssfCell.setCellValue((RichTextString) data);} else {xssfCell.setCellValue(cell.getData$());}}preColIndex = cell.getIndex();xssfSheet.autoSizeColumn(colIndex);colIndex += mergeAcross + 1;}rowIndex++;}for (CellRangeAddress range : mergedRanges) {CellStyle cellStyle = xssfSheet.getRow(range.getFirstRow()).getCell(range.getFirstColumn()).getCellStyle();RegionUtil.setBorderTop(cellStyle.getBorderBottomEnum(), range, xssfSheet);RegionUtil.setBorderBottom(cellStyle.getBorderBottomEnum(), range, xssfSheet);RegionUtil.setBorderLeft(cellStyle.getBorderBottomEnum(), range, xssfSheet);RegionUtil.setBorderRight(cellStyle.getBorderBottomEnum(), range, xssfSheet);}mergedRanges.clear();}xssfWorkbook.write(new FileOutputStream(outFile));xssfWorkbook.close();Desktop.getDesktop().open(outFile);