logo
down
shadow

Apache POI unable to detect hash formatted number


Apache POI unable to detect hash formatted number

By : Hari Krish
Date : November 17 2020, 03:01 PM
will help you The problem is multidimensional.
At first, the number format 60############ cannot be applied usig Java. It leads to java.lang.IllegalArgumentException: Malformed pattern "60############" using DecimalFormat.
code :
...
        // Now, handle the other aspects like 
        //  quoting and scientific notation
        for(int i = 0; i < sb.length(); i++) {
           char c = sb.charAt(i);
/*
            // remove quotes and back slashes
            if (c == '\\' || c == '"') {
                sb.deleteCharAt(i);
                i--;
*/
            // handle quotes and back slashes
            if (c == '\\') {
                sb.setCharAt(i, '\'');
                sb.insert(i+2, '\'');
                i+=2;
            } else if (c == '"') {
                sb.setCharAt(i, '\'');
            // for scientific/engineering notation
            } else if (c == '+' && i > 0 && sb.charAt(i - 1) == 'E') {
                sb.deleteCharAt(i);
                i--;
            }
        }

        formatStr = sb.toString();
        formatStr = formatStr.replace("''", ""); 
        return formatStr;
    }
...
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileInputStream;

import java.lang.reflect.Method;

class ExcelDataformatterExample {

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

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter df = new DataFormatter();
  MyDataFormatter mydf = new MyDataFormatter();

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    if (cell.getCellTypeEnum() == CellType.NUMERIC) {
     CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
     System.out.println("Cell " + cellRef.formatAsString());

     System.out.print("Excel's data format string: ");
     String formatStr = cell.getCellStyle().getDataFormatString();
     System.out.println(formatStr);

     System.out.print("Value using poi's data formatter: ");
     Method cleanFormatForNumber = DataFormatter.class.getDeclaredMethod("cleanFormatForNumber", String.class); 
     cleanFormatForNumber.setAccessible(true); 
     String cleanFormatStr = (String)cleanFormatForNumber.invoke(df, formatStr);
     System.out.print("using poi's cleanFormatStr: ");
     System.out.print(cleanFormatStr + " result: ");
     String value = df.formatCellValue(cell);
     System.out.println(value);

     System.out.print("Value using my data formatter: ");
     cleanFormatForNumber = MyDataFormatter.class.getDeclaredMethod("cleanFormatForNumber", String.class); 
     cleanFormatForNumber.setAccessible(true); 
     cleanFormatStr = (String)cleanFormatForNumber.invoke(mydf, formatStr);
     System.out.print("using my cleanFormatStr: ");
     System.out.print(cleanFormatStr + " result: ");
     value = mydf.formatCellValue(cell);
     System.out.println(value);

    }
   }
  }
  wb.close();

 }

}
Cell A1
Excel's data format string: \60##########
Value using poi's data formatter: using poi's cleanFormatStr: 60########## result: 199901234
Value using my data formatter: using my cleanFormatStr: '6'0########## result: 199901234
Cell A2
Excel's data format string: \60000000000
Value using poi's data formatter: using poi's cleanFormatStr: 60000000000 result: 60199901234
Value using my data formatter: using my cleanFormatStr: '6'0000000000 result: 60199901234
Cell A3
Excel's data format string: "60"#
Value using poi's data formatter: using poi's cleanFormatStr: 60# result: 199901234
Value using my data formatter: using my cleanFormatStr: '60'# result: 60199901234
Cell A4
Excel's data format string: \6\0#
Value using poi's data formatter: using poi's cleanFormatStr: 60# result: 199901234
Value using my data formatter: using my cleanFormatStr: '60'# result: 60199901234


Share : facebook icon twitter icon
How to get the formatted value of a number for a cell in Apache POI?

How to get the formatted value of a number for a cell in Apache POI?


By : user2333898
Date : March 29 2020, 07:55 AM
wish of those help You can force the value to be returned as a String using the methods below
code :
HSSFDataFormatter hdf = new HSSFDataFormatter();
System.out.println (hdf.formatCellValue(mycell));
HSSFCell cell1 = (HSSFCell) row1.getCell(2);

HSSFDataFormatter hdf = new HSSFDataFormatter();
System.out.println ("formatted "+ hdf.formatCellValue(cell1));
Regular expression to detect whether input is a formatted number

Regular expression to detect whether input is a formatted number


By : user3549089
Date : March 29 2020, 07:55 AM
will be helpful for those in need Regular Expressions have always seemed like black magic to me and I have never been able to get my head around building them. I am now in need of a Reg Exp (for validation putsposes) that checks that the user enters a number according to the following rules. , You can try the following expression
code :
^([1-9]\d{0,2}(,\d{3})+|[1-9]\d*|0)(\.\d+)?$
JavaScript URL Hash detect id number

JavaScript URL Hash detect id number


By : Danilo Lima
Date : March 29 2020, 07:55 AM
I wish this help you I need to write regex in this code to detect id on url like this: http://example.com/#1 , how about:
code :
/#(\d+)/
var url = "http://example.com/#1";
var id = /#(\d+)/;
if (url.match(id)) {
     alert(url.match(id)[1]);
}
Convert a hash string to a formatted number?

Convert a hash string to a formatted number?


By : Magi Null
Date : March 29 2020, 07:55 AM
this one helps. I'm trying to output prettier numbers from my FreeMarker template in GeoServer: , If you always want 4 decimals:
code :
${feature[attribute.name].value?string("0.0000")}
C# How to convert text formatted number with exclamation mark icon note to number formatted number?

C# How to convert text formatted number with exclamation mark icon note to number formatted number?


By : Ashish
Date : March 29 2020, 07:55 AM
I hope this helps you . Changing the number format of the cells won't do anything, since it only affects the visual presentation of a numeric value and the values are strings, not numbers.
You will need to convert the numbers from strings to a compatible numeric type - double, or int depending on the content, maybe even Decimal - and replace the string values with the converted numeric.
Related Posts Related Posts :
  • Interrupt CompletableFuture with default value
  • Eclipse - Choosing servers in "Run on server" vs setting "Target Runtime"
  • Spring JPA doesn't validate bean on update
  • How is takeWhile different from filter?
  • Spring Scheduled Cron jobs firing at wrong time
  • JFrame window resizing on every compile. Different results every time
  • Kotlin unresolved reference :add after converting from Java code
  • How to use Neo4j APOC method " apoc.export.csv.query(query,file,config)" in Java program
  • Regex to include all spanish characters and number
  • Parsing and Mapping a JSON Object with Dynamic Properties
  • Which approach to building domain objects in Spring MVC is more correct?
  • Java Long Min and Max not exact as mentioned in docs
  • Selenium opens new tab after SendKeys
  • How to sort a large array of ints?
  • FitNessse Test can not find Fixture
  • Including Signed Libraries in Executable JAR with Maven
  • If and Else statement run on second iteration of while loop
  • Change class of disabled tab on change of dropdown value
  • Junit5 @ParameterizedTest How to pass array as one of parameter
  • Text message not being sent to a list of numbers but is sent when just one number is specified
  • Spring mapped servlet not called in jetty-maven-plugin
  • How to pace the consumption of a sqs queue using spring integration
  • Autowiring Not getting disabled using xml configuration
  • Maze solve and shortest path with Java BFS
  • Spinner updating only on user actions
  • Why are Java rules for protected access such as they are?
  • Modify parameter of method by value in annotation aspectJ
  • How to send sensitive data to a Liferay portlet from outside the Liferay portal
  • Does a server connection stay open if created inside a method?
  • JAR manifest Class-Path, use folder instead of jar
  • How to do a bulk update in Firestore
  • ItemArray.checklist.add() not appending string to ArrayList
  • How to move something on screen without other picture staying there
  • SpringBoot : No matching bean found exception
  • Implementing the Clonable interface, but don't have to override the clone() method
  • how can i get this code to choose a random word once from each array to print a sentence?
  • Static class to get connections from connection pool
  • Unable to start Chrome browser with user profile in Selenium
  • How to pass a object to be created as a parameter for a method in Java?
  • How to retry with hystrix
  • Loading key to KeyStore fails on Android Oreo
  • Spring+Velocity unsuccessful attempts to save object
  • Private constructor in Kotlin
  • Jetty:run fails with NoSuchMethodError with Spring 5
  • Cannot upload my Web Applicartion in Jboss7 EAP7 EAP
  • How to load a certificate from "Credential storage"?
  • Call Genexus procedure stub in Java environment
  • JavaFX clipping produces a 'lottery scratch ticket'-Effect
  • Using DateTimeFormatter on january first cause an invalid year value
  • Get all the output from Watson conversation using java
  • Java unable to open main class and jar file
  • How to override @override method from activity into another class
  • Adding Runtime VM parameters to intellij for Java 9
  • Java IBM MQ Client connected but not getting messages from queue
  • Questions of Tomcat SSL configuration
  • Stale JNLP files for <extension> (since Java 9?)
  • Properly set (system) properties in JUnit 5
  • Spring MVC Model within POST Method is empty
  • VSTS buildagent: Java 9
  • java.lang.NumberFormatException: For input string: "id" for Hibernate
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org