dhatim / fastexcel Goto Github PK

Is it possible to set width for specific column?

Great library with significant performance. However when I need to customize the String cell with right alignment, the style setting does not work. Neither with "center. Could you please advise?

or

I had same problem with emojis,

I'm trying to set cell value a string, which contains \b\n character, the file is generated without errors, but when I open it I see this

Can you fix this for all unicode characters?

I started using this library because I thought that it continuously streamed values to the OutputStream passed to the Workbook instance. This doesn't seem to be the case, rather it accumulates all data in memory and when calling ws.finish() everything is written to the OutputStream. I'm sure that this is intended behavior but when you're working with a huge set of rows you don't want to keep everything in memory and rather just pipe it to the output stream directly. Would this be out of scope for the project?

Try to read xlsx file and get IndexOutOfBoundsException after full file reading.
For example, if I run this code:

Sheet sheet = wb.getFirstSheet();
            try (Stream<Row> rows = sheet.openStream()) {
                rows.forEach(r -> {
                    System.out.println(r.getRowNum());
                });
            }

For attached file, I get strange output:

1
2
1039685
java.lang.IndexOutOfBoundsException: row-index: 1039685, index: 0, count: 0
	at org.dhatim.fastexcel.reader.Row.getCell(Row.java:41)
	at com.checker.springbootadminclient.temp.translations.Sql3.lambda$main$0(Sql3.java:36)
	at org.dhatim.fastexcel.reader.RowSpliterator.tryAdvance(RowSpliterator.java:52)
	at java.base/java.util.Spliterator.forEachRemaining(Spliterator.java:326)
	at java.base/java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:658)
	at com.checker.springbootadminclient.temp.translations.Sql3.main(Sql3.java:34)

In the same time, rows.count() return correct result.

Translations.xlsx

Add the ability to write to headers and footers on the workbook/sheets

See: https://www.javatpoint.com/apache-poi-excel-header

There are only two methods fro borders in StyleSetter class, and they apply the border styles from all sides
ex:

here is result:

Can you add ability to define border styles for only one side, for ex for bottom or left, i want to get result like this.

Thank You.

fastexcel-reader's tests are already in junit-5.

Hello,

I am building a basic diff&mege tool for excel files, and I am looking for support of read/write on Cell.Notes so that users can add notes to their "conflict resolution" on cells.

Do you have any guidelines for contribution ?
Are you open to incorporate note support ? It might seem like a good feature that should not affect performance as I assume (not a raw excel format expert..) might be easy to do lazily,

Thanks

Local links to documents are not possible with the =HYPERLINK....
Would be a great enhancement
my usecase
I need a function to make a clickable link to some cells which opens a pdf.

Hi,
We are using fast-excel more often in our project. It is working faster than other plugin.
But we can't include logo in excel cell.
Will you bring this feature in future or no idea about it?

Thanks

Hi,
When open the xls file. it throws the following error dialogue box. Kindly provide the solution as soon as possible.

Hi ,
I am trying to set custom date format using format method of style , it is changing actual date value to some other date with format required. how to fix this issue ?

public static void main(String[] args) {

	try( OutputStream os = new FileOutputStream("C:\\TESTOUTS\\testxls_"+(new Date().getTime())+".xlsx"))
	{
		Workbook   wb = new Workbook(os, "my workbook", "1.0"); 
	    Worksheet ws = wb.newWorksheet("test");
	    ws.style(2, 3).format("MMMM dd, yyyy").set();
	    ws.value(2, 3, new  SimpleDateFormat("dd/MMM/YYYY").parse("18/Apr/2020"));
	    wb.finish();
	     // Excepted output is : April 18, 2020
                // Output return is : December 29 2020

		
		
	} catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	catch (Exception e) {
		e.printStackTrace();
	}

Currently new ReadableWorksheet(InputStream) will read the whole uncompressed xml data into memory. This is how OPCPackage.open(InputStream) works.

It would be great to make fastexcel-reader be able to stream rows as it reads the input stream.

Usually the order of xml files in the xlsx archive is as follows:

-rw----     2.0 fat      571 bl defN 17-Aug-16 12:30 _rels/.rels
-rw----     2.0 fat      271 bl defN 17-Aug-16 12:30 docProps/app.xml
-rw----     2.0 fat      588 bl defN 17-Aug-16 12:30 docProps/core.xml
-rw----     2.0 fat      549 bl defN 17-Aug-16 12:30 xl/_rels/workbook.xml.rels
-rw----     2.0 fat     2725 bl defN 17-Aug-16 12:30 xl/sharedStrings.xml
-rw----     2.0 fat    43085 bl defN 17-Aug-16 12:30 xl/worksheets/sheet1.xml
-rw----     2.0 fat     5637 bl defN 17-Aug-16 12:30 xl/styles.xml
-rw----     2.0 fat      716 bl defN 17-Aug-16 12:30 xl/workbook.xml
-rw----     2.0 fat     1111 bl defN 17-Aug-16 12:30 [Content_Types].xml

This is great and would allow processing on the fly. The zip could be read using ZipInputStream. Shared string table would be created from sharedStrings.xml when it would be encountered. Then rows would be emitted to the user are they are read from sheet1.xml. In this mode accessing sheets would only be allowed in order in which they appear in the archive.

There is one problematic case though. I have already came across an xlsx (saved from MS Excel) where xl/sharedStrings.xml appeared after xl/worksheets/sheet1.xml, like this:

-rw----     2.0 fat      571 bl defN 17-Aug-16 12:30 _rels/.rels
-rw----     2.0 fat      271 bl defN 17-Aug-16 12:30 docProps/app.xml
-rw----     2.0 fat      588 bl defN 17-Aug-16 12:30 docProps/core.xml
-rw----     2.0 fat      549 bl defN 17-Aug-16 12:30 xl/_rels/workbook.xml.rels
-rw----     2.0 fat    43085 bl defN 17-Aug-16 12:30 xl/worksheets/sheet1.xml
-rw----     2.0 fat     2725 bl defN 17-Aug-16 12:30 xl/sharedStrings.xml
-rw----     2.0 fat     5637 bl defN 17-Aug-16 12:30 xl/styles.xml
-rw----     2.0 fat      716 bl defN 17-Aug-16 12:30 xl/workbook.xml
-rw----     2.0 fat     1111 bl defN 17-Aug-16 12:30 [Content_Types].xml

I do hope xl/_rels/workbook.xml.rels always appear before sheet and sharedStrings. This would at least allow for detection of this case: If sharedString.xml is specified in rels and sheet.xml is encountered before sharedString in the zip.
The only one solution that comes to my mind. Put aside the raw compressed sheet1.xml part of the input stream to temporary file. Then when sharedString.xml is read from input stream, resume uncompressing sheet1.xml and processing in on the fly then.

Further possible optimizations:

• Skip xlsx entries that we're not parsing anyway without uncompressing them (docProps/app.xml, xl/styles.xml)
• Instead of saving compressed sheet.xml to temp file, start with putting is aside in memory. Then save to file only after some threshold is reached.
• Fully skip sheets.xml that the user is not interested in. For example: Using InputStream source; The user asks for sheet3.xml (that is after name to id resolution). sheet1 and sheet2 are skipped, uncompressed when reading the InputStream. Only when sheet3.xml is encounted, it is processed and rows streamed to the users. Accessing sheet1 or sheet2 after that would be not possible.

Simpler alternative:

Load the whole InputStream (compressed xlsx) into memory.
Then specific parts like sharedString.xml or sheet3.xml could be
accessed using the zip's central directory that is located at the end of the archive.
(see "Zip file structure" in https://rzymek.github.io/post/excel-zip64/).
Maybe OPCPackage has a mode that works this way already.
OPCPackage.open(ZipEntrySource)?
If not, a contribution to OPCPackage might be a better place for improvement.

What do you think?

Dear Team,
I am unable to open the generated Excel file ( xlsx file), where some of the cell has null values . PLease see the code snippet below.

if(value != null) {
sheet.value((i+1), j, ObjectUtils.toString(value));
}else {
sheet.value((i+1), j, null);
}

		//Writing the excel to a file.
		FileOutputStream outputStream = new FileOutputStream(fileName);
		try {
			os.writeTo(outputStream);
			 
		}catch (Exception e) {
			logger.error("error in writing excel file",e);
		}finally {
			outputStream.close();
                    if(workbook != null) {
			workbook.finish();
		}
}

dear sir/madam:
i'm using fastexcel 0.8.1 to generate xlsx file,i found excel 2007 can't open the result file collect,
the low version excel can't recognition the attribute <cols autoFit=\"true\">.
set the width of the column, the excel 2007 normal xml format is as follows:
<cols> <col min="1" max="1" width="21.375" customWidth="1"/> <col min="4" max="4" width="30.875" customWidth="1"/> </cols>

Cannot change font size and family.

Will you provide jar file for 0.9.8?

It would be great if there was support for Data Validations.

How to load image in cell?

Hi,
When generated big file with fastexcel writer ( ~20K rows ), sorting A-Z or Z-A for some column on generated file excel crash and sometimes office closes.
It seams something makes heavy of generated file.

Hi.

When I am generating the Excel. Mentioned text shows in single line within a cell. Below image for your reference.

But I want text like below image within a cell.

Thanks.

When try to open stream getting null pointer exception in XSSFReader of PackageRelationship rel = workbookPart.getRelationship(relId); line number 174

I am Trying to read excel file but I am stuck at the

ReadableWorkbook wb = new ReadableWorkbook(Objects.requireNonNull(inputStream)

Following is Simple app to read data from excel file.

          public void getit(View view) throws IOException {
    File inputFile = new File("/storage/emulated/0/police/test.xlsx");
    inputFile.setReadable(true);
    if(inputFile.canRead())
        Toast.makeText(this,"it can read",Toast.LENGTH_SHORT).show();
    InputStream inputStream = null;
    try {
        inputStream = new FileInputStream(inputFile);
        Log.d(String.valueOf(this), "happen");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
   
    try ( ReadableWorkbook wb = new ReadableWorkbook(Objects.requireNonNull(inputStream))) {

        Sheet sheet = wb.getFirstSheet();
        try(Stream<Row> rows = sheet.openStream()){
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.N) {
                rows.forEach(r->{
                    BigDecimal num = r.getCellAsNumber(0).orElse(null);
                    String str = r.getCellAsString(1).orElse(null);
                    Log.i(String.valueOf(this),"cell 1 = "+num+"cell 2 = "+str+"\n");
                } );
            }

        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}`

And my build.gradle file is
apply plugin: 'com.android.application'

                  android {
compileSdkVersion 29
buildToolsVersion "29.0.0"
defaultConfig {
    applicationId "android.example.com.myapplication"
    minSdkVersion 20
    targetSdkVersion 29
    versionCode 1
    versionName "1.0"
    testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
}
buildTypes {
    release {
        minifyEnabled false
        proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
    }
}
compileOptions {
    sourceCompatibility = '1.8'
    targetCompatibility = '1.8'
}

}

            dependencies {
implementation fileTree(dir: 'libs', include: ['*.jar'])
implementation 'androidx.appcompat:appcompat:1.0.2'
implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
testImplementation 'junit:junit:4.12'
androidTestImplementation 'androidx.test:runner:1.2.0'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'
implementation 'org.dhatim:fastexcel-reader:0.10.2'

}

I am getting the following error

    Process: android.example.com.myapplication, PID: 30869
java.lang.IllegalStateException: Could not execute method for android:onClick
    at androidx.appcompat.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:390)
    at android.view.View.performClick(View.java:5619)
    at android.view.View$PerformClick.run(View.java:22298)
    at android.os.Handler.handleCallback(Handler.java:754)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:165)
    at android.app.ActivityThread.main(ActivityThread.java:6375)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:912)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:802)
 Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at androidx.appcompat.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:385)
    at android.view.View.performClick(View.java:5619) 
    at android.view.View$PerformClick.run(View.java:22298) 
    at android.os.Handler.handleCallback(Handler.java:754) 
    at android.os.Handler.dispatchMessage(Handler.java:95) 
    at android.os.Looper.loop(Looper.java:165) 
    at android.app.ActivityThread.main(ActivityThread.java:6375) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:912) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:802) 
 Caused by: java.lang.NoSuchMethodError: No virtual method toPath()Ljava/nio/file/Path; in class Ljava/io/File; or its super classes (declaration of 'java.io.File' appears in /system/framework/core-oj.jar)
    at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:218)
    at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:201)
    at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:162)
    at org.apache.poi.openxml4j.util.ZipSecureFile.<init>(ZipSecureFile.java:122)
    at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:205)
    at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:140)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:271)
    at org.dhatim.fastexcel.reader.ReadableWorkbook.open(ReadableWorkbook.java:192)
    at org.dhatim.fastexcel.reader.ReadableWorkbook.<init>(ReadableWorkbook.java:52)
    at android.example.com.myapplication.MainActivity.getit(MainActivity.java:46)
    at java.lang.reflect.Method.invoke(Native Method) 
    at androidx.appcompat.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:385) 
    at android.view.View.performClick(View.java:5619) 
    at android.view.View$PerformClick.run(View.java:22298) 
    at android.os.Handler.handleCallback(Handler.java:754) 
    at android.os.Handler.dispatchMessage(Handler.java:95) 
    at android.os.Looper.loop(Looper.java:165) 
    at android.app.ActivityThread.main(ActivityThread.java:6375) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:912) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:802) 

I was trying to use the Range, StyleSetter etc., Couldn't work because the constructors were default scope.

I'm trying to set cell value a string, which contains emoji, the file is generated without errors, but when I open it I see this

If I set the cell style fontSize to say (14), the autoSizing that happens will not take into account the font size, and the cell will be partially hidden.

Cannot set Zoom in sheet like as:
Apache Poi:
sheet.setZoom(120);
Set Zoom Poi

There is no documentation for module fastexcel-reader. We need at least a few examples in the readme.

Hi,
I want to add logo in Excel. Is there any possibility?

Are you thinking about adding functionality to read the metadata properties of an Excel file?

Like these here: https://support.office.com/en-us/article/view-or-change-the-properties-for-an-office-file-21d604c2-481e-4379-8e54-1dd4622c6b75

Automatically append suffix (e.g. _<number>) to duplicate name when creating a new worksheet.

Longer worksheet names should be truncated to avoid warning when opening workbook in Excel.

Hello.
Currently I'm getting error on creating cell with Boolean value: "No supported cell type for class java.lang.Boolean".

Database: MySQL
Column Type: TINYINT(1)
Library: mysql-connector-java
Library Version: 8.0.13

Thank you for your time.

Hi ,
Please let me know , can we generate fast excel with workbook encrypted / protected with password .
I can see only for sheet protection method.

Cannot hide grid lines on sheet.

Hi,

Border color not working using this method. Please help me.
ws.style(i, j).bold().fontSize(9).borderColor(BorderSide.BOTTOM,"FF0000").set();

First of all, I really appreciate the work that went into this!

I'm parsing a xlsx with 36 columns or so, and the last few columns represent optional data. As such, them being empty/null is a valid state.

However, when I read a cell that is null, and all following columns of that row are also null, "OutOfBoundsException" is thrown.

As per the Excel specs, the maximum column number is 16,384.

If feasible, I propose throwing "OutOfBoundsException", when the column index greater than 16,384 is requested, and otherwise return null on absent values.

Cheers

项目中纯在eureka jar 获取不到Sheet id 为 null

Some other libraries support adding Hyperlinks. As I am migrating an older project from Apache Poi to fastexcel, I would be gracious for the addition of such a functionality.

I'm having trouble reading dates from excel file. FastExcel doesn't recognize DATE type fields so it parses them as NUMBER instead and gives me something like 40333.
I don't have this problem with Apache POI.

I think there is an issue with the getCellAsBoolean method of the Row.java class.

If a boolean value is set in the cell (TRUE or FALSE), the value retrieved by the library is always true.

I'm unable to open output in LibreOffice Calc from a simple test case from fastexcel-writer 0.10.7:

public class FastExcelTest {

    @Test
    public void simpleWorkbook() throws IOException {
        OutputStream os = new FileOutputStream("/tmp/test2.xlsx");
        Workbook wb = new Workbook(os, "MyApplication", "1.0");
        Worksheet ws = wb.newWorksheet("Sheet 1");
        ws.value(0, 0, "This is a string in A1");
        ws.finish();
        wb.finish();
        os.close();
    }
}

If I try to open it in LibreOffice Calc, I get the message:

The file 'test2.xlsx' is corrupt and therefore cannot be opened. LibreOffice can try to repair the file.

If I choose "Yes", LibreOffice is unsuccessfully with the repair.

Am I missing something?

The version of LibreOffice is:

Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial7

Facing problem while reading percentage (ex: 68% in excel file converted to 0.68), currency sign not supported also