excel 如何使用apache poi在单元格内创建进度条?

ne5o7dgx  于 2023-03-04  发布在  Apache
关注(0)|答案(2)|浏览(224)

我想在Excel表格单元格中创建一个进度条。我必须使用Apache Poi库,但我甚至不知道如何启动。(类似于此,但使用Java库)http://www.tech-recipes.com/rx/35064/excel-2013-create-progress-bars/
我想我必须把一个条件格式化,但我知道它是如何工作的,我不能找到一个解决方案的任何地方...有人可以帮助我吗?
先谢了。

relj7zay

relj7zay1#

正如你所建议的,我已经使用你的链接创建了一个示例xlsx,并简单地重新创建了必要的xml结构,也就是说,将xlsx文件作为zip存档打开,并查看xl/worksheets/sheet1.xml。除了poi-ooxml.jar之外,你还需要ooxml-schemas-1.1.jar。
(使用Libre Office 4.0、Excel查看器2010、兴趣点3.10-beta1进行测试)

import java.io.FileOutputStream;
import java.lang.reflect.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

public class Databar {
    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        for (int i=0; i<4; i++) {
            sheet.createRow(i).createCell(0).setCellValue(new int[]{12,38,93,42}[i]);
        }

        SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
        XSSFConditionalFormattingRule xcfrule =
            (XSSFConditionalFormattingRule)cf.createConditionalFormattingRule("");

        Method m = XSSFConditionalFormattingRule.class.getDeclaredMethod("getCTCfRule");
        m.setAccessible(true);
        CTCfRule cfRule = (CTCfRule)m.invoke(xcfrule);
        cfRule.removeFormula(0); // cleanup

        cfRule.setType(STCfType.DATA_BAR);
        CTDataBar databar = cfRule.addNewDataBar();
        CTCfvo vfoMin = databar.addNewCfvo();
        vfoMin.setType(STCfvoType.NUM);
        vfoMin.setVal("0");
        CTCfvo vfoMax = databar.addNewCfvo();
        vfoMax.setType(STCfvoType.NUM);
        vfoMax.setVal("100");
        CTColor color = databar.addNewColor();
        color.setRgb(new byte[]{(byte)0xFF, 0x00, 0x00, (byte)0xFF});

        CellRangeAddress cra[] = {new CellRangeAddress(0, 3, 0, 0)};
        cf.addConditionalFormatting(cra, xcfrule);

        FileOutputStream fos = new FileOutputStream("databar-out.xlsx");
        wb.write(fos);
        fos.close();
    }
}
hgc7kmma

hgc7kmma2#

这是一个老案例,但为了防止对任何人有帮助,这里有一个更简单(更更新)的可接受的答案代码版本:

import java.awt.Desktop;
    import java.io.File;
    import java.io.FileOutputStream;
    
    import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class Databar {

        public static void main(String[] args) throws Exception {

            try (Workbook wb = new XSSFWorkbook()) {
                Sheet sheet = wb.createSheet();

                //  populate with dummy data
                for (int i = 0; i < 4; i++) {
                    sheet.createRow(i).createCell(0).setCellValue(new int[] { 12, 38, 93, 42 }[i]);
                }

                // The cell you want bars on
                CellRangeAddress cra[] = { new CellRangeAddress(0, 3, 0, 0) };

                // The color of the bars    
                XSSFColor color = new XSSFColor(new byte[] { (byte) 0xFF, 0x00, 0x00, (byte) 0xFF });

                // Here's the interesting part
                SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
                ConditionalFormattingRule cfrule = cf.createConditionalFormattingRule(color);
                cf.addConditionalFormatting(cra, cfrule);
    
                // save the file and open it
                final File file = new File("databar-out3.xlsx");
                FileOutputStream fos = new FileOutputStream(file);
                wb.write(fos);
                fos.close();
    
                Desktop.getDesktop().edit(file);
            }
        }
    }

此外,您可能希望自定义条形图的行为。为此,只需控制相应的DataBarFormatting即可:

DataBarFormatting format = cfrule.getDataBarFormatting();

例如,如果您希望最短和最长的可能条形为值0和100,而不是计算为工作表中现有的最低和最高值,只需执行以下操作:

format.getMinThreshold().setRangeType(RangeType.NUMBER);
    format.getMinThreshold().setValue(0);
    format.getMaxThreshold().setRangeType(RangeType.NUMBER);
    format.getMaxThreshold().setValue(100);

完整代码:

import java.awt.Desktop;
    import java.io.File;
    import java.io.FileOutputStream;
    
    import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
    import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
    import org.apache.poi.ss.usermodel.DataBarFormatting;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class Databar {
    
        public static void main(String[] args) throws Exception {
    
            try (Workbook wb = new XSSFWorkbook()) {
                Sheet sheet = wb.createSheet();
    
                //  populate with dummy data
                for (int i = 0; i < 4; i++) {
                    sheet.createRow(i).createCell(0).setCellValue(new int[] { 12, 38, 93, 42 }[i]);
                }
    
                // The cell you want bars on
                CellRangeAddress cra[] = { new CellRangeAddress(0, 3, 0, 0) };
    
                // The color of the bars    
                XSSFColor color = new XSSFColor(new byte[] { (byte) 0xFF, 0x00, 0x00, (byte) 0xFF });
    
                // Here's the interesting part
                SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
                ConditionalFormattingRule cfrule = cf.createConditionalFormattingRule(color);
                cf.addConditionalFormatting(cra, cfrule);
    
                // Format the bars
                DataBarFormatting format = cfrule.getDataBarFormatting();
                format.getMinThreshold().setRangeType(RangeType.NUMBER);
                format.getMinThreshold().setValue(0d);
                format.getMaxThreshold().setRangeType(RangeType.NUMBER);
                format.getMaxThreshold().setValue(100d);
                
                // save the file and open it
                final File file = new File("databar-out3.xlsx");
                FileOutputStream fos = new FileOutputStream(file);
                wb.write(fos);
                fos.close();
    
                Desktop.getDesktop().edit(file);
            }
        }
    }

希望能有帮助!

相关问题