Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Sep 05, 2021 1:03 am

Hello
I want to save each excel table as csv file.
Sql data server will read the csv files.

foreach (Worksheet i in dstBook.Worksheets)
{
foreach (IListObject j in i.ListObjects)
{
// j to csv file
j.Save();
}
}

kurikabocya
 
Posts: 13
Joined: Mon Aug 09, 2021 2:21 am

Mon Sep 06, 2021 7:00 am

Hello,

Thanks for your inquiry.
Please refer to the following code.
Code: Select all
            Workbook dstBook = new Workbook();
            dstBook.LoadFromFile("FormatTable.xlsx");

            int num = 0;
            CellRange range = dstBook.Worksheets[0].Range;
            foreach (Worksheet i in dstBook.Worksheets)
            {
                foreach (IListObject j in i.ListObjects)
                {
                    CellRange location = j.Location as CellRange;
                    ExportTableOptions exportTableOptions = new ExportTableOptions();
                    exportTableOptions.ExportColumnNames = true;
                    System.Data.DataTable dataTable = location.ExportDataTable(exportTableOptions);

                    Workbook resultBook = new Workbook();
                    resultBook.Worksheets.Clear();
                    Worksheet worksheet = resultBook.Worksheets.Add(i.Name);
                    worksheet.InsertDataTable(dataTable,true,1,1);
                    worksheet.SaveToFile("ToCSV"+ num + ".csv", ",", Encoding.UTF8);
                    num++;
                }
            }

If this cannot help you, please provide your input file and your desired output for further investigation. You can send them to us (support@e-iceblue.com) via email. Thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Sep 06, 2021 7:59 am

Perfect !!!!
Thank you very much !!!!

kurikabocya
 
Posts: 13
Joined: Mon Aug 09, 2021 2:21 am

Mon Sep 06, 2021 9:41 am

You are welcome.
If you encounter any issues related to our products in the future, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Sat Sep 11, 2021 8:32 pm

Below source is before getting your reply.
The source substitues formula by its value, because SQL requires value not formula.
My question is that your
System.Data.DataTable dataTable = location.ExportDataTable(exportTableOptions);
or
worksheet.InsertDataTable(dataTable, true, 1, 1);
can do the same thing.

My old source
// open output file
using (StreamWriter writer = new StreamWriter(outputFile, false, new UTF8Encoding(false)))
{
// read one line and write it with CR+LF
for (int row = i.Location.Row; row <= i.Location.LastRow; row++)
{
string oneLine = null;
for (int column = i.Location.Column; column <= i.Location.LastColumn; column++)
{
var tmpCell = MyCell(dstCommonSheet, row, column);
string tmpString = null;

if (tmpCell.HasFormula)
{
tmpString = tmpCell.FormulaValue.ToString();
}
else
{
tmpString = tmpCell.Value;
}

oneLine += tmpString + ",";
}
writer.WriteLine(oneLine);
}

kurikabocya
 
Posts: 13
Joined: Mon Aug 09, 2021 2:21 am

Mon Sep 13, 2021 7:27 am

Hello,

Thanks for your feedback.
Please add this line to the code I provided your above.
Code: Select all
                    exportTableOptions.ComputedFormulaValue = true;

If this is not what you want, please provide your input file and your desired output for further investigation. You can send them to us (support@e-iceblue.com) via email. Thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Sep 13, 2021 7:49 am

Thank you !!
The property name made me confident.
Perhaps it will work !!

kurikabocya
 
Posts: 13
Joined: Mon Aug 09, 2021 2:21 am

Thu Sep 23, 2021 10:27 am

Hello,

Greetings from E-iceblue!
Has your issue been resolved? Any feedback will be greatly appreciated.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS

cron