convertTime

convertTime

关注
发信
关注(28)粉丝(399)

Apache-POI 在 Excel 中设置值，但另一个单元格的公式无法使用该值，直到我在处理条中手动按下 Enter

本文介绍了Apache-POI 在 Excel 中设置值，但另一个单元格的公式无法使用该值，直到我在处理条中手动按下 Enter的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我写了一个程序，将值写入 xlsx 文件.乍一看，它似乎可以正常工作.但是在我的 xlsx 文件中，我在另一个单元格中有一个公式，如果我手动输入该值应该可以正常工作，但是如果我的程序设置了完全相同的值，它就不会像它应该的那样工作.如果我在我的程序写入一个值后手动打开文件并通过按处理条中的输入按钮确认写入的值，它会像它应该的那样工作.没有抛出异常.我的程序正在单元格 N4 和 O4 中写入.这是我正在谈论的单元格的 Excel 公式:

=+MAX(;MIN(O4+(N4>O4);MAX(($N$2>$O$2);$O$2))-MAX(N4;$N$2))+MAX(;MIN(O4;MAX(($N$2>$O$2);$O$2))-MAX(;$N$2))(N4>O4)+MAX(;MIN(O4+(N4>O4);MIN)(($N$2>$O$2);$O$2))-MAX(N4;))+MIN(O4;MIN(($N$2>$O$2);$O$2))(N4>O4)

N2 和 O2 包括与 N4 和 O4 值进行比较的预选值.这是我的 Java 代码:

import java.io.File;导入 java.io.FileInputStream;导入 java.io.FileNotFoundException;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.sql.Date;导入 java.text.SimpleDateFormat;导入 java.util.HashMap;导入 java.util.Iterator;导入 java.util.Map;导入 java.util.Set;导入 java.util.logging.Level;导入 java.util.logging.Logger;导入 javax.swing.JCheckBox;导入 javax.swing.JComboBox;导入 javax.swing.JTable;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.CellStyle;导入 org.apache.poi.ss.usermodel.CreationHelper；导入 org.apache.poi.ss.usermodel.IndexedColors;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.xssf.usermodel.*;公共类 XLSXEditor {公共 XLSXEditor(){}public void searchWriter(String path, JTable t1) 抛出 FileNotFoundException, IOException{文件 excel = 新文件(路径)；FileInputStream fis = new FileInputStream(excel);XSSFWorkbook book = new XSSFWorkbook(fis);XSSFSheet sheet = book.getSheetAt(1);int r = getNonBlankRowCount(path);字符串 uname = "404";SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");java.util.Date 解析 = null;java.sql.Date 日期 = null;//基准样式CellStyle csDate = book.createCellStyle();CreationHelper createHelper = book.getCreationHelper();csDate.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));csDate.setBorderBottom(XSSFCellStyle.BORDER_THIN);csDate.setBorderTop(XSSFCellStyle.BORDER_THIN);csDate.setBorderLeft(XSSFCellStyle.BORDER_THIN);csDate.setBorderRight(XSSFCellStyle.BORDER_THIN);csDate.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());csDate.setFillPattern(CellStyle.SOLID_FOREGROUND);//uname 样式CellStyle csUname = book.createCellStyle();csUname.setBorderBottom(XSSFCellStyle.BORDER_THIN);csUname.setBorderTop(XSSFCellStyle.BORDER_THIN);csUname.setBorderLeft(XSSFCellStyle.BORDER_THIN);csUname.setBorderRight(XSSFCellStyle.BORDER_THIN);csUname.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());csUname.setFillPattern(CellStyle.SOLID_FOREGROUND);//时间样式CellStyle csTime = book.createCellStyle();csTime.setBorderTop(XSSFCellStyle.BORDER_THIN);csTime.setBorderBottom(XSSFCellStyle.BORDER_THIN);csTime.setBorderLeft(XSSFCellStyle.BORDER_THIN);csTime.setBorderRight(XSSFCellStyle.BORDER_THIN);csTime.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());csTime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm"));csTime.setFillPattern(CellStyle.SOLID_FOREGROUND);csTime.setVerticalAlignment(CellStyle.ALIGN_CENTER);csTime.setAlignment(CellStyle.ALIGN_CENTER);System.out.println(getFilledRows(t1));for(int i=0; i

在图片中你可以看到列，我的程序正在使用.由于德国数据安全政策，我需要删除其他列.. 列中填充了字符串

解决方案

在你的代码部分之后:

...String timeF = tf.substring(0, 5);cellTimeF.setCellValue(timeF);...

单元格内容将始终是字符串(文本)单元格内容.函数 MIN 和 MAX 不能使用此内容.这些函数需要数字内容.在 Excel 中日期和时间也是仅格式化为日期时间的数字内容.默认设置为 1 = 1 天 = 01/01/1900 00:00:00.1 小时 = 1/24，1 分钟 = 1/24/60，1 秒 = 1/24/60/60.

如果String timeF是HH:MM:SS"格式的字符串，则DateUtil.convertTime 可用于将此字符串转换为 Excel 宝贵的时间.

完整示例，显示哪些无效，哪些有效:

import java.io.FileOutputStream;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；导入 org.apache.poi.ss.usermodel.DateUtil;公共类 ExcelCalculateTimeValues {public static void main(String[] args) 抛出异常 {工作簿工作簿 = new XSSFWorkbook();CreationHelper createHelper = workbook.getCreationHelper();CellStyle styletime = workbook.createCellStyle();styletime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm:ss"));Sheet sheet = workbook.createSheet();sheet.createRow(0).createCell(0).setCellValue("Start");sheet.getRow(0).createCell(1).setCellValue("End");sheet.getRow(0).createCell(3).setCellValue("Start");sheet.getRow(0).createCell(4).setCellValue("End");String[][] tableData = new String[][]{{"12:34:00", "22:45:00"},{"23:45:00", "01:34:00"},{"08:01:00", "13:23:00"}};整数 r = 1;for (String[] rowData : tableData) {行行 = sheet.createRow(r++);int c = 0;for (String cellData : rowData) {单元格单元格 = row.createCell(c);cell.setCellValue(cellData);//这设置字符串单元格数据cell.setCellStyle(styletime);cell = row.createCell(3 + c++);cell.setCellValue(DateUtil.convertTime(cellData));//这会设置日期时间单元格数据cell.setCellStyle(styletime);}}sheet.createRow(r).createCell(0).setCellFormula("MIN(A2:A4)");//由于 A2:A4 中的字符串值而无法工作sheet.getRow(r).createCell(1).setCellFormula("MIN(B2:B4)");//无法工作，因为 B2:B4 中的字符串值sheet.getRow(r).createCell(3).setCellFormula("MIN(D2:D4)");//将工作sheet.getRow(r).createCell(4).setCellFormula("MIN(E2:E4)");//将工作workbook.setForceFormulaRecalculation(true);workbook.write(new FileOutputStream("ExcelCalculateTimeValues.xlsx"));workbook.close();}}

所以你的代码:

...cellTimeF.setCellValue(DateUtil.convertTime(timeF));...

和

...cellTimeT.setCellValue(DateUtil.convertTime(timeT));...

应该可以.

I wrote a programm, that writes values into a xlsx-file. At the first glance it seems to work like it should. But in my xlsx-file I've got a Formula in another cell that should works correct if I type in the value manually, but if my programm sets exactly the same value it doesn't work like it should. If I open the file manually after my programm wrote a value and confirm the wrote value by pressing the enter button in the processing strip it works like it should. There are no Exceptions thrown. My programm is writing in the cells N4 and O4. Here is the Excel Formula of the cell I'm talking about:

N2 and O2 include preselected Values, that are compared with the values of N4 and O4.And here is my Java code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JTable;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;


public class XLSXEditor {

public XLSXEditor(){

}

public void searchWriter(String path, JTable t1) throws FileNotFoundException, IOException{
        File excel = new File(path);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook book = new XSSFWorkbook(fis);
        XSSFSheet sheet = book.getSheetAt(1);
        int r = getNonBlankRowCount(path);
        String uname = "404";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date parsed = null;
        java.sql.Date date = null;

        //Datum Style
        CellStyle csDate = book.createCellStyle();
        CreationHelper createHelper = book.getCreationHelper();
        csDate.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
        csDate.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csDate.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        csDate.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //uname style
        CellStyle csUname = book.createCellStyle();
        csUname.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csUname.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        csUname.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //time style
        CellStyle csTime = book.createCellStyle();
        csTime.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csTime.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csTime.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        csTime.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csTime.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        csTime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm"));
        csTime.setFillPattern(CellStyle.SOLID_FOREGROUND);
        csTime.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        csTime.setAlignment(CellStyle.ALIGN_CENTER);

        System.out.println(getFilledRows(t1));
        for(int i=0; i<getFilledRows(t1); i++){
            System.out.println(r);
            XSSFRow row = sheet.getRow(r);
            //fill username column in xlsx file
            XSSFCell celluName = row.getCell(0, Row.RETURN_BLANK_AS_NULL);

            if(celluName != null){
                r++;
            }
            if(celluName == null){
                celluName = sheet.getRow(r).createCell(0);
                celluName.setCellStyle(csUname);
                uname = t1.getValueAt(i, 0).toString().charAt(0) +""+ t1.getValueAt(i, 1);
                celluName.setCellValue(uname);
                r++;
            }
            r--;
            //fill date column in xlsx file
            XSSFCell cellDate = row.getCell(5, Row.RETURN_BLANK_AS_NULL);
            System.out.println("r = " + r);
            cellDate = sheet.getRow(r).createCell(5);
            cellDate.setCellStyle(csDate);
            try{
                parsed = sdf.parse(t1.getValueAt(i, 2).toString());
            }
            catch(Exception e){
                System.out.println(e);
            }
            date = new java.sql.Date(parsed.getTime());
            cellDate.setCellValue(date);


            //fill zeit von
            if(!(t1.getValueAt(i, 6).toString().equals("Standby not activated"))){
                XSSFCell cellTimeF = row.getCell(13, Row.RETURN_BLANK_AS_NULL);
                cellTimeF = sheet.getRow(r).createCell(13);

                String tf = t1.getValueAt(i, 3).toString();
                String timeF = tf.substring(0, 5);
                cellTimeF.setCellValue(timeF);
                cellTimeF.setCellStyle(csTime);
            }

            //fill zeit bis
            if(!(t1.getValueAt(i, 6).toString().equals("Standby not activated"))){
                XSSFCell cellTimeT = row.getCell(14, Row.RETURN_BLANK_AS_NULL);
                cellTimeT = sheet.getRow(r).createCell(14);

                String tt = t1.getValueAt(i, 4).toString();
                String timeT = tt.substring(0, 5);
                cellTimeT.setCellValue(timeT);
                cellTimeT.setCellStyle(csTime);
            }

            //set crosses
            XSSFCell cellStandbyP = row.getCell(7, Row.RETURN_BLANK_AS_NULL);
            XSSFCell cellStandbyA = row.getCell(8, Row.RETURN_BLANK_AS_NULL);
            XSSFCell cellSpecial = row.getCell(9, Row.RETURN_BLANK_AS_NULL);

            if(t1.getValueAt(i, 6).equals("Standby not activated")){
                cellStandbyP = sheet.getRow(r).createCell(7);
                cellStandbyP.setCellStyle(csUname);
                cellStandbyP.setCellValue("x");
            }
            if(t1.getValueAt(i, 6).equals("Planned work")){
                cellSpecial = sheet.getRow(r).createCell(9);
                cellSpecial.setCellStyle(csUname);
                cellSpecial.setCellValue("x");
            }
            if(t1.getValueAt(i, 6).equals("Standby Activated")){
                cellStandbyA = sheet.getRow(r).createCell(8);
                cellStandbyA.setCellStyle(csUname);
                cellStandbyA.setCellValue("x");
            }
            r++;
        }

        FileOutputStream fos = new FileOutputStream(path);
        book.setForceFormulaRecalculation(true);
        book.write(fos);
        fos.flush();
        fos.close();
}
}

in the picture u can see the columns, my programm is working with. I needed to cut out the other columns because of the german data security policy.. the columns are filled with strings

解决方案

After your code part:

...
String timeF = tf.substring(0, 5);
cellTimeF.setCellValue(timeF);
...

the cell content will always be string (text) cell content. This content the functions MINand MAX cannot work with. Those functions needs numeric content. In Excel date and time also is numeric content only formatted as date-time. With default settings 1 = 1 day = 01/01/1900 00:00:00. 1 hour = 1/24, 1 minute = 1/24/60, 1 second = 1/24/60/60.

If String timeF is string of format "HH:MM:SS", then DateUtil.convertTime can be used to convert this string into a Excel valuable time.

Complete example which shows what not works and what works:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

public class ExcelCalculateTimeValues {

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

  Workbook workbook = new XSSFWorkbook();

  CreationHelper createHelper = workbook.getCreationHelper();
  CellStyle styletime = workbook.createCellStyle();
  styletime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm:ss"));

  Sheet sheet = workbook.createSheet();
  sheet.createRow(0).createCell(0).setCellValue("Start");
  sheet.getRow(0).createCell(1).setCellValue("End");
  sheet.getRow(0).createCell(3).setCellValue("Start");
  sheet.getRow(0).createCell(4).setCellValue("End");

  String[][] tableData = new String[][]{
   {"12:34:00", "22:45:00"},
   {"23:45:00", "01:34:00"},
   {"08:01:00", "13:23:00"}
  };

  int r = 1;
  for (String[] rowData : tableData) {
   Row row = sheet.createRow(r++);
   int c = 0;
   for (String cellData : rowData) {
    Cell cell = row.createCell(c);
    cell.setCellValue(cellData); //this sets string cell data
    cell.setCellStyle(styletime);
    cell = row.createCell(3 + c++);
    cell.setCellValue(DateUtil.convertTime(cellData)); //this sets datetime cell data
    cell.setCellStyle(styletime);
   }
  }

  sheet.createRow(r).createCell(0).setCellFormula("MIN(A2:A4)"); //cannot work because of string values in A2:A4
  sheet.getRow(r).createCell(1).setCellFormula("MIN(B2:B4)"); //cannot work because of string values in B2:B4
  sheet.getRow(r).createCell(3).setCellFormula("MIN(D2:D4)"); //will work
  sheet.getRow(r).createCell(4).setCellFormula("MIN(E2:E4)"); //will work

  workbook.setForceFormulaRecalculation(true);

  workbook.write(new FileOutputStream("ExcelCalculateTimeValues.xlsx"));
  workbook.close();

 }

}

So with your code :

...
cellTimeF.setCellValue(DateUtil.convertTime(timeF));
...

and

...
cellTimeT.setCellValue(DateUtil.convertTime(timeT));
...

should work.

这篇关于Apache-POI 在 Excel 中设置值，但另一个单元格的公式无法使用该值，直到我在处理条中手动按下 Enter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-05 21:41
Powered by LMLPHP ©2024 convertTime 0.032840