+ Ответить в теме
Показано с 1 по 7 из 7

Тема: Apache POI:Как обновить Excel-файл со многими формулами

  1. #1
    dummy Hehabr is on a distinguished road
    Регистрация
    09.07.2017
    Сообщений
    8
    Вес репутации
    0

    По умолчанию Apache POI:Как обновить Excel-файл со многими формулами

    Что я делаю не так?
    Почему в новом файле значение ячейки D5: #DIV/0!

    -- Program output:
    Formula is: C34
    1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886
    Formula is: C34

    -- Cell values:
    Value of the cell D5 in file excelFileOrig.xlsm is: 407,25
    Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0!
    Value of the cell C8 in file excelFileOrig.xlsm is: 5,0
    Value of the cell D5 in file excelFileNew.xlsm is: 15,0

    -- Code:

    Код java:
    1.  
    2.  
    3. import org.apache.poi.ss.usermodel.Cell;
    4. import org.apache.poi.ss.usermodel.DateUtil;
    5. import org.apache.poi.ss.util.CellReference;
    6. import org.apache.poi.xssf.usermodel.XSSFSheet;
    7. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    8.  
    9. import java.io.*;
    10.  
    11. public class POITestRB {
    12.  
    13.     static String excelFileOrig = "C:/Test/excelFileOrig.xlsm";
    14.     static String excelFileNew = "C:/Test/excelFileNew.xlsm";
    15.     static FileInputStream fis;
    16.     static XSSFWorkbook workbook;
    17.  
    18.     public static void main(String[] args) throws IOException {
    19.         fis = new FileInputStream(excelFileOrig);
    20.         workbook = new XSSFWorkbook(fis);
    21.  
    22.         gettingCellContents(workbook, "D5");
    23.         updateCell(workbook, 15.0);
    24.         fis.close();
    25.         workbook.close();
    26.  
    27.         fis = new FileInputStream(excelFileNew);
    28.         workbook = new XSSFWorkbook(fis);
    29.         gettingCellContents(workbook, "D5");
    30.     }
    31.  
    32.     private static void gettingCellContents(XSSFWorkbook workbook, String cellId) {
    33.  
    34.         XSSFSheet sheet = workbook.getSheetAt(1);
    35.         CellReference ref = new CellReference(cellId);
    36.         int row = ref.getRow();
    37.         int col = ref.getCol();
    38.         Cell cell = sheet.getRow(row).getCell(col);
    39.  
    40.         switch (cell.getCellTypeEnum()) {
    41.             case STRING:
    42.                 System.out.println(cell.getRichStringCellValue().getString());
    43.                 break;
    44.             case NUMERIC:
    45.                 if (DateUtil.isCellDateFormatted(cell)) {
    46.                     System.out.println(cell.getDateCellValue());
    47.                 } else {
    48.                     System.out.println(cell.getNumericCellValue());
    49.                 }
    50.                 break;
    51.             case BOOLEAN:
    52.                 System.out.println(cell.getBooleanCellValue());
    53.                 break;
    54.             case FORMULA:
    55.                 System.out.println("Formula is: " + cell.getCellFormula());
    56.                 switch(cell.getCachedFormulaResultType()) {
    57.                     case Cell.CELL_TYPE_NUMERIC:
    58.                         System.out.println("1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue());
    59.                         break;
    60.                     case Cell.CELL_TYPE_STRING:
    61.                         System.out.println("2. case Cell.CELL_TYPE_STRING --> Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
    62.                         break;
    63.                 }
    64.                 break;
    65.             case BLANK:
    66.                 System.out.println();
    67.                 break;
    68.             default:
    69.                 System.out.println();
    70.         }
    71.     }
    72.  
    73.     public static void updateCell(XSSFWorkbook workbook, Double newData) {
    74.         try {
    75.             XSSFSheet sheet = workbook.getSheetAt(1);
    76.             CellReference ref = new CellReference("C8");
    77.             int row = ref.getRow();
    78.             int col = ref.getCol();
    79.             Cell cell = sheet.getRow(row).getCell(col);
    80.             if (cell != null) {
    81.                 cell.setCellValue(newData);
    82.             }
    83.  
    84.             workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
    85.             OutputStream os = new FileOutputStream(excelFileNew);
    86.             workbook.write(os);
    87.             os.flush();
    88.             os.close();
    89.         }
    90.         catch (Exception e) {
    91.             e.printStackTrace();
    92.         }
    93.     }
    94.  
    95. }
    Последний раз редактировалось AiK; 04.08.2017 в 10:58. Причина: [code=java][/code]

  2. По умолчанию

     
    Хотите избавиться от рекламы? Зарегистрируйтесь
  3. #2
    dummy Hehabr is on a distinguished road
    Регистрация
    09.07.2017
    Сообщений
    8
    Вес репутации
    0

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    Может быть можно как-то удалить кэшированные результаты из файла?
    Чтобы в новом файле все результаты пересчитались по новому, с учётом внесённых изменений...

  4. #3
    Administrator Админ
    system architect
    AiK is on a distinguished road Аватар для AiK
    Регистрация
    13.02.2004
    Адрес
    СПб
    Сообщений
    2,298
    Вес репутации
    80

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    На сколько я понимаю, нужно запустить пересчёт либо конкретной, либо всех ячеек.

    https://poi.apache.org/apidocs/org/a...Evaluator.html
    Код java:
    1. workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
    Даже самый дурацкий замысел можно воплотить мастерски

  5. #4
    dummy Hehabr is on a distinguished road
    Регистрация
    09.07.2017
    Сообщений
    8
    Вес репутации
    0

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    Пересчёт всех ячеек используется в коде.
    В середине метода updateCell()

  6. #5
    Administrator Админ
    system architect
    AiK is on a distinguished road Аватар для AiK
    Регистрация
    13.02.2004
    Адрес
    СПб
    Сообщений
    2,298
    Вес репутации
    80

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    evaluateAllFormulaCells пробовал?
    Даже самый дурацкий замысел можно воплотить мастерски

  7. #6
    Administrator Админ
    system architect
    AiK is on a distinguished road Аватар для AiK
    Регистрация
    13.02.2004
    Адрес
    СПб
    Сообщений
    2,298
    Вес репутации
    80

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    И ещё ексель можно заставить всё пересчитать:
    Код java:
    1. Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
    2.  
    3.   Sheet sh = wb.getSheetAt(0);
    4.   sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2
    5.  
    6.   wb.setForceFormulaRecalculation(true);
    Даже самый дурацкий замысел можно воплотить мастерски

  8. #7
    dummy Hehabr is on a distinguished road
    Регистрация
    09.07.2017
    Сообщений
    8
    Вес репутации
    0

    По умолчанию Re: Apache POI:Как обновить Excel-файл со многими формулами

    С помощью workbook.setForceFormulaRecalculati on(true); новый файл сохраняется с новыми значениями всех ячеек.

    Проблема теперь с выводом значения ячейки в новом файле:
    -------------------------------------------------------------------------------------------------
    Formula is: C34
    cell.getCachedFormulaResultType(): 0
    0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886
    Formula is: C34
    cell.getCachedFormulaResultType(): 5
    5. case Cell.CELL_TYPE_ERROR -->
    -------------------------------------------------------------------------------------------------

    Почему выводит CELL_TYPE_ERROR ?
    Новый файл хранит новые значения всех ячеек.

    Что надо ещё добавить в коде?

    Код HTML:
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.apache.poi.ss.util.CellReference;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.*;
    
    public class POITestRB {
    
        static String excelFileOrig = "C:/Test/1.xlsm";
        static String excelFileNew = "C:/Test/excelFileNew.xlsm";
        static FileInputStream fis;
        static XSSFWorkbook workbook;
    
        public static void main(String[] args) throws IOException {
    
            fis = new FileInputStream(excelFileOrig);
            workbook = new XSSFWorkbook(fis);
            gettingCellContents(workbook, "D5");
            updateCell(workbook, 10.0);
            fis.close();
            workbook.close();
    
            fis = new FileInputStream(excelFileNew);
            workbook = new XSSFWorkbook(fis);
            gettingCellContents(workbook, "D5");
            fis.close();
            workbook.close();
    
        }
    
    
        private static void gettingCellContents(XSSFWorkbook workbook, String cellId) {
    
            workbook.setForceFormulaRecalculation(true);
    
            XSSFSheet sheet = workbook.getSheetAt(1);
            CellReference ref = new CellReference(cellId);
            int row = ref.getRow();
            int col = ref.getCol();
            Cell cell = sheet.getRow(row).getCell(col);
    
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    System.out.println("Formula is: " + cell.getCellFormula());
                    System.out.println("cell.getCachedFormulaResultType(): " + cell.getCachedFormulaResultType());
    
                    switch(cell.getCachedFormulaResultType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.println("0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.println("4. case Cell.CELL_TYPE_STRING --> Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            System.out.println("5. case Cell.CELL_TYPE_ERROR --> ");
                            break;
                    }
                    break;
                case BLANK:
                    System.out.println();
                    break;
                default:
                    System.out.println("default");
            }
        }
    
        public static void updateCell(XSSFWorkbook workbook, Double newData) {
            try {
                XSSFSheet sheet = workbook.getSheetAt(1);
                CellReference ref = new CellReference("C8");
                int row = ref.getRow();
                int col = ref.getCol();
                Cell cell = sheet.getRow(row).getCell(col);
                if (cell != null) {
                    cell.setCellValue(newData);
                }
    
                workbook.getCreationHelper().createFormulaEvaluator().clearAllCachedResultValues();
                workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
    
                OutputStream os = new FileOutputStream(excelFileNew);
                workbook.write(os);
                os.flush();
                os.close();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    }

+ Ответить в теме

Похожие темы

  1. как обновить combobox в Access
    с формой связана таблица Т1( ID, Name, comment) с combobox связна другая Т. в которой есть два поля Т2( ID, Name) как только ползователь вводит...
    от Bengan в разделе MS Office и VB(A).
  2. Первая пустая ячейка диапазона стандартными формулами Excel (без VBA)
    Про нахождение первой пустой (незаполненной) ячейки на листе (в диапазоне, области) тут обсуждалось не раз. Но всё сводилось к средствам VBA для...
    от tolikt в разделе MS Office и VB(A).
  3. Экспорт из Excel в текстовый файл
    Люди помогоите! срочно требуеца написать макрос для экспорта данных в текстовый файл с разделением данных каждой ячейки точкой с запятой.
    от Шурик в разделе MS Office и VB(A).
  4. Как обновить меню написанное на JavaScripte при нажатии на п
    Например все подпункты светло голубые, нажал на один - все остались светло-голубыми, а выбранный стал зеленым например. Короче надо после щелчка мыши...
    от Corite в разделе JScript, VBScript, DHTML...
  5. Закрыть дочернее, обновить родительское... подскажите
    Народ подскажите: я открываю дочернее окно w1=window.open() по ссылке, в нем находится форма по submit которой я изменяю данные базы как...
    от sergey в разделе JScript, VBScript, DHTML...

Ваши права

  • Вы не можете создавать новые темы
  • Вы не можете отвечать в темах
  • Вы не можете прикреплять вложения
  • Вы не можете редактировать свои сообщения