使用Java将.xls格式Excel转换为.xlsx时丢失饼图与样式的问题咨询

你现有转换代码仅处理了单元格的基础值、注释和简单数据格式，没有迁移内嵌图片、完整单元格样式、绘图元素，因此会丢失饼图和格式。你插入的饼图本质是JPG静态图片，不是Excel原生图表，迁移难度远低于原生图表。

直接把生成xls的代码替换为XSSF实现，一步生成.xlsx文件，省掉转换步骤，完全不会丢失内容。
修改后的饼图生成代码如下：

package excelFileGenerate6;
import java.awt.Color;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.jfree.data.general.DefaultPieDataset; 
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.labels.PieSectionLabelGenerator;
import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
import org.jfree.chart.plot.PiePlot;
import org.jfree.chart.ChartUtilities; 
import java.util.Iterator;

public class CreatePieChartXLSXExample {  

        public void createPie(String Filename) throws IOException {
                
                /* 读取Excel表格数据 */
                FileInputStream chart_file_input = new FileInputStream(new File(Filename));
                /* 替换为XSSFWorkbook处理xlsx格式 */
                XSSFWorkbook my_workbook = new XSSFWorkbook(chart_file_input);
                XSSFSheet my_sheet = my_workbook.getSheetAt(0);
                DefaultPieDataset my_pie_chart_data = new DefaultPieDataset();
                Iterator<Row> rowIterator = my_sheet.iterator(); 
                String chart_label="a";
                Number chart_data=0;            
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next();  
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next(); 
                                        switch(cell.getCellType()) { 
                                        case Cell.CELL_TYPE_NUMERIC:
                                                chart_data=cell.getNumericCellValue();
                                                break;
                                        case Cell.CELL_TYPE_STRING:
                                                chart_label=cell.getStringCellValue();
                                                break;
                                        }
                                }
                my_pie_chart_data.setValue(chart_label,chart_data);
                }               
                JFreeChart myPieChart=ChartFactory.createPieChart("Test Results",my_pie_chart_data,true,true,false);
                
                PiePlot plot = (PiePlot) myPieChart.getPlot();
                PieSectionLabelGenerator gen = new StandardPieSectionLabelGenerator("{1}");
                plot.setLabelGenerator(gen);
                
                plot.setSectionPaint("Passed", Color.GREEN);
                plot.setSectionPaint("Failed", Color.RED);
                plot.setSectionPaint("Pending", Color.black);
                plot.setSectionPaint("Total Time taken (ms)", Color.pink);
                plot.setSectionPaint("Skipped", Color.blue);
                plot.setSectionPaint("How many Sub headers are Passed", Color.yellow);
                plot.setSectionPaint("Total TC's", Color.CYAN);
                
                int width=640;
                int height=480;
                float quality=1;
                ByteArrayOutputStream chart_out = new ByteArrayOutputStream();          
                ChartUtilities.writeChartAsJPEG(chart_out,quality,myPieChart,width,height);
                InputStream feed_chart_to_excel=new ByteArrayInputStream(chart_out.toByteArray());
                byte[] bytes = IOUtils.toByteArray(feed_chart_to_excel);
                int my_picture_id = my_workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                feed_chart_to_excel.close();
                chart_out.close();
                /* 替换为XSSFDrawing处理绘图 */
                XSSFDrawing drawing = my_sheet.createDrawingPatriarch();
                ClientAnchor my_anchor = new XSSFClientAnchor();
                my_anchor.setCol1(4);
                my_anchor.setRow1(5);
                XSSFPicture  my_picture = drawing.createPicture(my_anchor, my_picture_id);
                my_picture.resize();
                chart_file_input.close();               
                FileOutputStream out = new FileOutputStream(new File(Filename));
                my_workbook.write(out);
                out.close();            
        }
        
        public static void main(String args[]) throws IOException {
            CreatePieChartXLSXExample chart = new CreatePieChartXLSXExample();
            // 直接传入xlsx后缀的文件路径即可
            chart.createPie("C:\\Users\\user\\JavaProjects\\workspace\\Sample2\\src\\excelFileGenerate6\\report.xlsx");
        }
}

你之前生成基础表格的代码也对应把HSSF替换为XSSF即可，不需要修改业务逻辑。

如果你一定要保留现有xls生成逻辑，再转成xlsx，修改转换代码，补充样式和图片迁移逻辑即可，修改后代码如下：

package excelFileGenerate6;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class conversion_to_xlsx {
    
    public static void main(String[] args) throws IOException {

        String inpFn = "C:\\Users\\user\\JavaProjects\\workspace\\Sample2\\src\\excelFileGenerate6\\report.xls"; 
        String outFn = "C:\\Users\\user\\JavaProjects\\workspace\\Sample2\\src\\excelFileGenerate6\\converted_report.xlsx"; 

        FileInputStream in = new FileInputStream(inpFn);
        try {
            Workbook wbIn = new HSSFWorkbook(in);
            File outF = new File(outFn);
            if (outF.exists())
                outF.delete();

            Workbook wbOut = new XSSFWorkbook();
            
            // 提前迁移所有样式，避免样式冲突
            Map<CellStyle, CellStyle> styleMap = new HashMap<>();
            for (short i = 0; i < wbIn.getNumCellStyles(); i++) {
                CellStyle oldStyle = wbIn.getCellStyleAt(i);
                CellStyle newStyle = wbOut.createCellStyle();
                newStyle.cloneStyleFrom(oldStyle);
                styleMap.put(oldStyle, newStyle);
            }
            
            // 迁移所有图片资源
            List<? extends PictureData> allPics = wbIn.getAllPictures();
            int[] picIdMap = new int[allPics.size()];
            for (int i = 0; i < allPics.size(); i++) {
                PictureData picData = allPics.get(i);
                picIdMap[i] = wbOut.addPicture(picData.getData(), picData.getPictureType());
            }

            int sheetCnt = wbIn.getNumberOfSheets();
            for (int i = 0; i < sheetCnt; i++) {
                Sheet sIn = wbIn.getSheetAt(i);
                Sheet sOut = wbOut.createSheet(sIn.getSheetName());
                // 迁移列宽配置
                for (int c = 0; c < sIn.getRow(0).getLastCellNum(); c++) {
                    sOut.setColumnWidth(c, sIn.getColumnWidth(c));
                }
                
                Iterator<Row> rowIt = sIn.rowIterator();
                while (rowIt.hasNext()) {
                    Row rowIn = rowIt.next();
                    Row rowOut = sOut.createRow(rowIn.getRowNum());
                    // 迁移行高配置
                    rowOut.setHeight(rowIn.getHeight());

                    Iterator<Cell> cellIt = rowIn.cellIterator();
                    while (cellIt.hasNext()) {
                        Cell cellIn = cellIt.next();
                        Cell cellOut = rowOut.createCell(
                                cellIn.getColumnIndex(), cellIn.getCellType());

                        switch (cellIn.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellOut.setCellValue(cellIn.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cellOut.setCellValue(cellIn.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cellOut.setCellFormula(cellIn.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cellOut.setCellValue(cellIn.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cellOut.setCellValue(cellIn.getStringCellValue());
                            break;
                        }
                        // 迁移单元格完整样式
                        cellOut.setCellStyle(styleMap.get(cellIn.getCellStyle()));
                        cellOut.setCellComment(cellIn.getCellComment());
                    }
                }
                
                // 迁移绘图元素（含插入的饼图图片）
                Drawing<?> drawingIn = sIn.getDrawingPatriarch();
                if (drawingIn != null) {
                    Drawing<?> drawingOut = sOut.createDrawingPatriarch();
                    for (Object shape : drawingIn.getShapes()) {
                        if (shape instanceof Picture) {
                            Picture picIn = (Picture) shape;
                            ClientAnchor anchorIn = picIn.getClientAnchor();
                            int picIndex = picIn.getPictureIndex();
                            drawingOut.createPicture(anchorIn, picIdMap[picIndex-1]);
                        }
                    }
                }
            }
            FileOutputStream out = new FileOutputStream(outF);
            try {
                wbOut.write(out);
            } finally {
                out.close();
                wbOut.close();
            }
        } finally {
            in.close();
        }
    }
}

你之前碰到的sun/image/codec错误是因为高版本JDK移除了sun相关的私有图像接口，升级JFreeChart到1.5.3以上版本即可解决。

内容的提问来源于stack exchange，提问作者automaion_test_monk