Manipulation with xlsx file contents

[Elek]

Hi all,
What i want to do is get contents of xlsx file, insert some data, create some columns and save the values to csv file.
For example, i want to get number of rows, so im writing something like this:
int numberOfRows = sourceSheet.Rows.Count();

Then i want to iterate over this number and incest values to the cells of the first row, from array of value. I didn"t find how to do it.
Is there any way to do it or i should think about another way ?
Thanks

[Betsy.jiang]

Dear Elek,

Thanks for your inquiry.
Here is sample code for your reference.

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"F:\sample document\9614.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //insert a row at first row
            sheet.InsertRow(1);
            string[] values = { "Value1", "value2", "value3" };
            //insert the values into the added row
            for (int i = 0; i < values.Length; i++)
            {
                sheet.Range[1, i+1].Value = values[i];
            }
            //save to csv
            sheet.SaveToFile("result.csv", ",", Encoding.UTF8);

If there is any question, welcome to get it back to us.

Sincerely,
Betsy
E-iceblue support team

[Elek]

Worked like a charm.
Thanks a lot !

[Betsy.jiang]

Dear Elek,

Thanks for your feedback.
Please feel free to contact us if there is any question. We will be happy to help you.

Sincerely,
Betsy
E-iceblue support team

[Elek]

Hi, i've stuck with another issue, when im importing csv file, i get the output file with empty rows.
How can i remove empty rows, or am i doing something wrong on import ?
I'm loading file with

Code: Select all

workBook.LoadFromFile(filePath, ",");

[Simon.yang]

Hello Elek,

Thanks for your inquiry.
I have used the Spire.XLS Pack Hotfix Version:7.11.49 to import a csv file through workBook.LoadFromFile(filePath, ",") and got the result without mistake. Please try with the latest version. If the issue still exist, please provide us the sample documents and full code for an investigation.
And there is the solution to remove empty rows. Please see below for your reference.

Code: Select all

            Workbook book = new Workbook();
            book.LoadFromFile("csvSample.csv", ",");
            Worksheet sheet = book.Worksheets[0];
            int rowCount = sheet.Rows.Length;

            DeleteBlankRows2(0, rowCount, sheet);
        static void DeleteBlankRows2(int begin, int count, Worksheet sheet)
        {
            for (int i = begin; i < count; i++)
            {
                if (i == sheet.Rows.Length) { break; }
                if (sheet.Rows[i].IsBlank)
                {
                    sheet.DeleteRow(i + 1);
                    DeleteBlankRows2(i, --count, sheet);
                }         
            }
        }

Sincerely,
Simon
E-iceblue support team

[Elek]

Hi, i've finished deleting empty rows, with recursion, with the code similar to yours .
Thanks for update and your help.

[Betsy.jiang]

Hi,

Thanks for your feedback.
Please feel free to contact us if there is any question.

Sincerely,
Betsy
E-iceblue support team