Sometime file will be lost something(share formula?)after modified by qxlsx

liufeijin commented 5 years ago

but can repaired by office and seems anything is ok. https://github.com/liufeijin/QXlsx/blob/master/TestExcel/C1804093.xlsx this is the file with problem.

https://app.leanboard.io/board/9a33da08-9352-4a63-942f-b99803e98324

j2doll commented 5 years ago

Thank you for reporting this issue.

Here are the test results:

The file does not open in Microsoft Excel (online).
But, It will open normally in Google Spreadsheets.

Can I see the code that generated the Excel file?

liufeijin commented 5 years ago

QXlsx::Document xlsx(excelFileName); if (!xlsx.isLoadPackage()) { QMessageBox::information(this,tr("read data"),excelFileName+tr("can't open the file！")); }else{

     if(xlsx.selectSheet("sheet1")){

           TankTotalNum=0;
           VesselNC="";
           VesselNE="";
           CertNo="";
           CertNo=xlsx.read(3,2).toString().trimmed();
           VesselNC=xlsx.read(4,2).toString().trimmed();
           VesselNE=xlsx.read(5,2).toString().trimmed();
     }
      ............

if(xlsx.selectSheet("sheet1")){
       for (int i=0;i<TankTotalNum;++i){
           xlsx.write(i+3,18,EndHeight[i]);
           xlsx.write(i+3,19,MarkHeight[i]);
         }
        xlsx.write(2,18,tr("incline ending"));
        xlsx.write(2,19,tr("incline marking"));
 }
xlsx.save();

} Seems after written some thing hidden in the excel is lose. (use office open it give tips is shared formula losing . if accept excel to repair , the file can be open and then use QXlsx write this excel file again , the excel doesn't have problem tips. Seems it is ok if the original excel doesn't include something(shared formula).

j2doll commented 5 years ago

I have found the following:

The file C1804093.xlsx is not a zip-compressed file. It is just a html format file.
If you are using Microsoft Excel, the file is opened as follows.
- Rename C1804093.xlsx to C1804093.tmp
- Open the file, using Microsoft Excel.

liufeijin commented 5 years ago

PLS download it https://github.com/liufeijin/QXlsx/blob/master/TestExcel/C1804093.rar

j2doll commented 5 years ago

I have found the following:

The file C1804093.rar is not a zip-compressed file. It is a html format file.

hxd-cap

liufeijin commented 5 years ago

hi j2doll I don't know why it is wrong the rar file you download. Anyway , i sent it to you via your gmail.

Kindly PLS check your email. best regards, jeff

j2doll commented 5 years ago

I wrote the test code in new branch 'shared-formula'. https://github.com/QtExcel/QXlsx/tree/shared-formula

Try the test, and tell me the result. (Don't use master branch.)

liufeijin commented 5 years ago

hi J2doll Many thanks for your response. I use your Shared-formula branch to test , but it is still same error. according to the error tips , the original excel shall be have some shared formula after written something is wrong format , so when open it by excel must be deleted the shared formula for continue .

liufeijin commented 5 years ago

I send you a rar via email that contains the original excel and afterwritten excel file. compare 1 same sheet maybe you can find something. Best regards, jeff

j2doll commented 5 years ago

Try the test, and tell me the result. (test branch is shared-formula)

Now, the following attributes are not implemented:

aca (Always Calculate Array)
bx (Assigns Value to Name)
del1 (Input 1 Deleted)
del2 (Input 2 Deleted)
dt2D (Data Table 2-D)
dtr (Data Table Row)
r1 (Data Table Cell 1)
r2 (Input Cell 2)

liufeijin commented 5 years ago

many thanks for your work！ but the error is same.

error053840_01.xml

在文件“C:\Users\jeff\Desktop\C1804093 建桥506\C1804093.xlsx”中检测到错误

-

已删除的记录: /xl/worksheets/sheet3.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet4.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet5.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet6.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet7.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet8.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet9.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet10.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet11.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet12.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet14.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet15.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet16.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet17.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet18.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet19.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet20.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet21.xml 部分的共享公式

liufeijin commented 5 years ago

Hi j2doll You can just open the original excel file and write any value to any cell (just 1 enough). And then open the written excel file , office will give the tips that something wrong the shared formula to be deleted for continue opening . So i think something(about shared formula) is no written back or written wrong ,so when open it by office the unreasonable (or wrong) formula to be deleted for continue opening. when the shared formula were deleted , use Qtxlsx to write any value to cell , and open by office . there will be not any error tips. So the original excel file to be contain some formula can't be discern by Qtxlsx ,or writes back wrongly format by Qtxlsx.

j2doll commented 5 years ago

Well, I think more testing and complementary work is required.

Thank you for reporting.

I will keep this issue.

j2doll commented 5 years ago

Hi. I fixed some code for testing. If possible, try to test it.

Test branch is 'dev34'. https://github.com/QtExcel/QXlsx/tree/dev34
Use 'git checkout dev34'

rickbase1 commented 5 years ago

Hi Jay Two,

Thanks for the effort. By chance is there a way to create chart legends yet?

Thanks

Rick

On Wed, Feb 13, 2019, 4:58 AM Jay Two <notifications@github.com wrote:

Hi. I fixed some code for testing. If possible, try to test it.

Test branch is 'dev34'. https://github.com/QtExcel/QXlsx/tree/dev34

Use 'git checkout dev34'

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/QtExcel/QXlsx/issues/32#issuecomment-463114583, or mute the thread https://github.com/notifications/unsubscribe-auth/AKqQs5dPzXmdKCXe4SbkVQ5qPqUp6kckks5vM9POgaJpZM4aSyDJ .

j2doll commented 5 years ago

Dear @rickbase1

I think it will take more time to analyze the function. :sob:

I'll check that issue in #31

rickbase1 commented 5 years ago

Great!

Thanks very much !!!

Rick

On Wed, Feb 13, 2019 at 8:04 AM Jay Two notifications@github.com wrote:

Dear @rickbase1 https://github.com/rickbase1

I think it will take more time to analyze the function. 😭

I'll check that issue in #31 https://github.com/QtExcel/QXlsx/issues/31

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/QtExcel/QXlsx/issues/32#issuecomment-463174759, or mute the thread https://github.com/notifications/unsubscribe-auth/AKqQs6O1RzJboSAtWfCHpX638VDXRlM7ks5vM_9SgaJpZM4aSyDJ .

liufeijin commented 5 years ago

Hi. I fixed some code for testing. If possible, try to test it.

Test branch is 'dev34'. https://github.com/QtExcel/QXlsx/tree/dev34

Use 'git checkout dev34'

Dear j2doll Many thanks for your work! But the error is still present.

已删除的记录: /xl/worksheets/sheet4.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet5.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet6.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet7.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet10.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet11.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet12.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet13.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet14.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet18.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet19.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet20.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet21.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet22.xml 部分的共享公式已删除的记录: /xl/worksheets/sheet23.xml 部分的共享公式

I think i have no time to find where is the problem in QXlsx till Sep this year(need pay attention on a hurry project). Thanks again for your hard work on this! Jeff

liufeijin commented 5 years ago

Hi. I fixed some code for testing. If possible, try to test it.

Test branch is 'dev34'. https://github.com/QtExcel/QXlsx/tree/dev34

Use 'git checkout dev34'

Hi I delete other items and found what status the error will be present. The two rows present together ,after write data to excel file , the error will present when open file by office Delete any row , after write data to excel file , no error present when open file by office

I change the file testorignal.xlsx ,testbadafterwrite.xlsx to testorignal.zip ,testabadafterwrite.zip and get the sheet1.xml ,compare it, found, some part will be lose when file write data to excel file.

i warp all file in desktop.zip Desktop.zip

for your reference.

j2doll commented 5 years ago

Dear @liufeijin

I think I know roughly what happened.

Cell 'J1' of file 'testoriginal.xlsx' is boolean type(b), it is not number type.
Only number type of cell can handle formula.

This conclusion is based on the ECMA 376 standard.

Cell type(t) can be 'Boolean', 'Date', 'Error', 'Inline String', 'Number', 'Shared String', 'String'. and it is based on W3C XML Scema string datatype.

I do not know how the file was created, but I think if I knew exactly how to do it, I would be able to do a more accurate analysis. :sunglasses:

liufeijin commented 5 years ago

The problem is if only has 1 row , it will be not error 。 I checked again the 'J1' present FALSE as when "A1"..."B1".....Cell has data and office does a data verification ![Uploading T1.png…]()

I have no English office, so i take a picture to show the button .

j2doll commented 5 years ago

I modified the processing of 'boolean type cell' again.

Try the test.

test branch is dev34

liufeijin commented 5 years ago

Oh... Great! You fix it prefect! Now it works fine! Many thanks for your great work!

Best regards, jeff

j2doll commented 5 years ago

I'll close this issue.

liufeijin commented 5 years ago

OK. Thanks again.

QtExcel / QXlsx

Sometime file will be lost something(share formula?)after modified by qxlsx #32