System date format ,report excel ERROR

[visualczm]

hi System date format for the date of Taipei (106/03/05), the data stored in the wrong date format date (2017/03/05) is OK

can you help me.

[rabanti-github]

Hi Can you provide me a code snippet with the constructor / definition of your date object? I assume we talk about the Minguo calendar. Could you also provide an Excel sheet (a valid one) with such a date in a cell? I can then look into it.

[rabanti-github]

Hi I looked a little bit more into the topic of date formatting. It is possible to format a date in Excel according to several international calendars. But this is just formatting. You need to insert the date still in the Gregorian type (e.g. 2017-03-30 or 30.03.2017 not 106/03/30). However, I have not found a suitable Taiwanese time format in Excel. If you can provide me an Excel sheet with a properly formatted date in a cell, then I can tell you the correct format string and styling options as code

[visualczm]

Windows` system date format to modify the format of Taipei, the export of EXCEL, open the EXCEL error The code is the same,change windows Gregorian type cause

http://blog.mosme.net/index.php/archives/896

http://yes.nctu.edu.tw/YesWWW/Tool/SnapTutor.aspx?Path=Lecture%2fPC%2fWindows%2fTutor%2fCtrl%2fCalendar&Snap=snap01.gif This Gregorian type

think you.

PS, PicoXLSX is very good

Workbook wk = new Workbook(false);
        int sheet = 0;
  private void button2_Click(object sender, EventArgs e)
 {
            List<object> title = new List<object>() {
                "時間",
                "時域X",
                "時域Y",
                "時域Z",
                "頻譜X",
                "頻譜Y",
                "頻譜Z"};

            List<object> Ltransfer_X = new List<object>();//时域图X

           foreach (var item in transfer_X)
            {
                 Ltransfer_X.Add(item);
            }

           List<object> Ltransfer_Y = new List<object>();//时域图Y

           foreach (var item in transfer_Y)
           {
               Ltransfer_Y.Add(item);
           }

           List<object> Ltransfer_Z = new List<object>();//时域图Z

           foreach (var item in transfer_Z)
           {
               Ltransfer_Z.Add(item);
           }

           List<object> LabsData_X = new List<object>();//频谱图X

           foreach (var item in absData_X)
           {
               LabsData_X.Add(item);
           }

           List<object> LabsData_Y = new List<object>();//频谱图Y

           foreach (var item in absData_Y)
           {
               LabsData_Y.Add(item);
           }

           List<object> LabsData_Z = new List<object>();//频谱图Z

           foreach (var item in absData_Z)
           {
               LabsData_Z.Add(item);
           }

            wk.AddWorksheet(string.Format("Sheet{0}", sheet));
            wk.CurrentWorksheet.AddCellRange(title, "A1:G1"); //新增标题
            wk.CurrentWorksheet.AddCell(date, "A2");//当前测试时间 date = DateTime.Now.ToString();
            wk.CurrentWorksheet.AddCellRange(Ltransfer_X, string.Format("B2:B{0}", Ltransfer_X.Count+1));//添加时域图数据
            wk.CurrentWorksheet.AddCellRange(Ltransfer_Y, string.Format("C2:C{0}", Ltransfer_Y.Count + 1));//添加时域图数据
            wk.CurrentWorksheet.AddCellRange(Ltransfer_Z, string.Format("D2:D{0}", Ltransfer_Z.Count + 1));//添加时域图数据
            wk.CurrentWorksheet.AddCellRange(LabsData_X, string.Format("E2:E{0}", Ltransfer_Z.Count + 1));//添加时域图数据
            wk.CurrentWorksheet.AddCellRange(LabsData_Y, string.Format("F2:F{0}", Ltransfer_Z.Count + 1));//添加时域图数据
            wk.CurrentWorksheet.AddCellRange(LabsData_Z, string.Format("G2:G{0}", Ltransfer_Z.Count + 1));//添加时域图数据

            sheet++;
}
         /// <summary>
        /// 保存數據為EXCEL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_excel_Click(object sender, EventArgs e)
        {
            string path = System.Windows.Forms.Application.StartupPath;//獲取程式所在的相同目錄
            wk.SaveAs(path+string.Format(@"\Data{0}.xlsx",DateTime.Now.ToString("hhmmss")));//保存EXCEL

        }

[rabanti-github]

Hi

Thank you for the code. The most important line is: wk.CurrentWorksheet.AddCell(date, "A2");//当前测试时间 date = DateTime.Now.ToString();

Normally, you don't need to convert DateTime to string explicitly. Try following code:

DateTime date = DateTime.Now; // No conversion into string
 wk.CurrentWorksheet.AddCell(date, "A2");//当前测试时间

If is still does not work:

• Please send me an XLSX file which was created in Microsoft Excel, containing a cell with your desired date format.
• Please send me also a screenshot (or the text) of your error message when you open Excel (I assume you get an error in Excel)

I can also try to install a Taiwanese localization this evening to test it according to the blog you referenced.

Some background to dates and Excel: Excel (or the OOXML standard) stores dates traditionally as OAdate. This is a number beginning with 0. 0 means 00.01.1900 (Gregorian). Days from this date are full numbers, the 24 hours of a day are described as the fraction of 1.0 (0.5 means approximately 12:00:00). Excel or the system in general gives the possibility to display localized date formats and calendars. But in the background is Excel still working with this (rather odd and backward compatibility-reasoned) Gregorian OAdate. Therefore it is not really a conversion of dates but a pure formatting. And formatting is done in Excel / OOXML by styling.

[visualczm]

Thank you for your answer

I don't think it's a problem,

DateTime date = DateTime.Now; // No conversion into string
 wk.CurrentWorksheet.AddCell(date, "A2");//当前测试时间

Because I tried to change the date to text, so too

DateTime date = DateTime.Now; // No conversion into string
 wk.CurrentWorksheet.AddCell(“111”, "A2");//

EXCEL open error display an error message click OK after auto repair

excel file Provide later

[rabanti-github]

Hi OK. A repair dialog looks like an error of the library. In this case I need also maybe a little bit more code to test. Can you provide the code (the data type and sample data) of transfer_X, transfer_Y, transfer_Z, LabsData_X, LabsData_Y and LabsData_Z for completeness? And your variable date is just DateTime.Now.ToString()?

Thank you in advance.

[visualczm]

Hi

All the information is in the file, Excel A2 using DateTime.Now.ToString()

if not DateTime.Now.ToString() A2 cell show numbers exp:56454654984

Excel file Link https://pan.baidu.com/s/1hsGCdPa

Thank you in advance.

Skype:pclover268

Hope to see your message

[rabanti-github]

Hi I figured out the problem. It is the creation date of the core.xml document which is defined by DateTime.Now. The CultureInfo of this must be "en-US" but it was on system default. In your case the Taiwanese CultureInfo. Excel seems not to like this.

I will publish a patch as soon as possible. If you use the source code of PicoXLSX then you can change the following in the meantime:

In the file LowLevel.cs, change the line 687 from string time = DateTime.Now.ToString("yyyy-MM-ddThh:mm:ssZ");

to string time = DateTime.Now.ToString("yyyy-MM-ddThh:mm:ssZ", this.culture);

Can you try this out?

[rabanti-github]

Fixed with commit 91da938 (v1.5.6) Please let me know if something is not working as expected. Thank you for your report and the contribution.

[visualczm]

@rabanti-github is Working,tks.