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 Oct 10, 2021 11:32 am

Hi E-ICEBlue Spire Team,
I have stuck at one point whilre Reading CVS file I have to Group by start columns and Sum last columns for remove 0 duplication in File and then i want to Export it into DataTable C#.
Kindly Find Attachment Below for Better Understating.
Thanks For Your Good Cooperation .
Attachments
Spire.JPG
Spire.JPG (100.43 KiB) Viewed 2336 times

MSAQIBABBASI
 
Posts: 11
Joined: Mon Jul 05, 2021 8:08 am

Mon Oct 11, 2021 9:05 am

Hello,

Thank you for your inquiry.
Please refer to the following code to achieve your requirement. If there is any questions, please feel free to contact us.
Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile("handle.csv", ",",1,1);
Worksheet sheet = workbook.Worksheets[0];
int colCount = sheet.Rows[0].Count;
int amountSum;
int pieceSum;
for (int i = 2; i < sheet.Columns[0].Count - 1; i++)
{
    //Get the names value of the i-th row
    string text1 = sheet.Columns[1].Rows[i].Value + sheet.Columns[2].Rows[i].Value;
    //Get the Amount value of the i-th row
    amountSum = int.Parse(sheet.Columns[colCount - 2].Rows[i].DisplayedText);
    //Get the Piece value of the i-th row
    pieceSum = int.Parse(sheet.Columns[colCount - 1].Rows[i].DisplayedText);
    for (int j = i + 1; j < sheet.Columns[0].Count; j++)
    {
        //Get the names value of row i+1
        string text2 = sheet.Columns[1].Rows[j].Value + sheet.Columns[2].Rows[j].Value;
        //Determine whether the names value is the same
        if (text1 == text2)
        {
            amountSum += int.Parse(sheet.Columns[colCount - 2].Rows[j].DisplayedText);
            pieceSum += int.Parse(sheet.Columns[colCount - 1].Rows[j].DisplayedText);
            //Delete rows with duplicate names value
            sheet.DeleteRow(j + 1);
            j--;
        }                   
    }
    sheet.Columns[colCount - 2].Rows[i].Value = amountSum.ToString();
    sheet.Columns[colCount - 1].Rows[i].Value = pieceSum.ToString();
}
//Export to datatable
DataTable t = sheet.ExportDataTable();
//Show in data grid
this.dataGridView1.DataSource = t;

Sincerely,
Annika
E-iceblue support team
Attachments
attachment.zip
(5.38 KiB) Downloaded 210 times
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Oct 11, 2021 7:08 pm

Hi Team it's best for small file but i my file has minimum 100,000 (01 Lac) rows so its just checking all the time one row by all rows and then 2nd row by all rows so its not working for me right now but for small file its good. Do you have any fast way to do this for 1 lac plus rows data .
Thanks for Your Best Service.

MSAQIBABBASI
 
Posts: 11
Joined: Mon Jul 05, 2021 8:08 am

Tue Oct 12, 2021 9:34 am

Hello,

Thank you for your feedback.
Please refer to my modified code. Please test on your side and look forward to your test feedback.
Code: Select all
Workbook book = new Workbook();
book.LoadFromFile(inputCSV, ",", 1, 1);
Worksheet sheet = book.Worksheets[0];
Worksheet newsheet = book.Worksheets.Add("temporary");
String text1 = "";
String text2 = "";
List<CellRange> rows = new List<CellRange>();
int j = 0;

int r = 2;
for (int i = 2; i < sheet.Rows.Count() + 1; i++)
{
    rows.Clear();
    text1 = sheet.Range[i, 2].Text;
    text2 = sheet.Range[i, 3].Text;
    rows.Add(sheet.Rows[i - 1]);
    for (j = i + 1; j < sheet.Rows.Count() + 1; j++)
    {
        if ((sheet.Range[j, 2].Text == text1) && (sheet.Range[j, 3].Text == text2))
        {
            rows.Add(sheet.Rows[j - 1]);
        }
        else
        {
            break;
        }
    }
    i = j - 1;
    double Amount = 0;
    double Piece = 0;
    foreach (CellRange range in rows)
    {
        Amount += double.Parse(range.CellList[3].Text);
        Piece += double.Parse(range.CellList[4].Text);
    }
    newsheet.Range[r, 1].Text = rows[0].CellList[0].Text;
    newsheet.Range[r, 2].Text = rows[0].CellList[1].Text;
    newsheet.Range[r, 3].Text = rows[0].CellList[2].Text;
    newsheet.Range[r, 4].Value = Amount.ToString();
    newsheet.Range[r, 5].Value = Piece.ToString();
    r++;
}
DataTable t = newsheet.ExportDataTable();
this.dataGridView1.DataSource = t;

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Oct 13, 2021 9:20 am

Hi Team I have tried both and both are taking too much time, Do you have any select command with group by function ,
Just Like we use in SQL Query. just Select column then group by specific columns.

Select S.No, FirstName, LastName sum(Amount)as[Amount], sum(Piece)as[Piece] from myTable group By S.No, FirstName, LastName.

the above command perform which i want to do in sql but is tehre any option in spire xlxs or xlx or csv if it is available so its really helpfull to m.
Thanks for Your Cooperation. And one more thing i am unable to login my account now beacuse i am out of town thats why i am replying with another account.

hellolistenn
 
Posts: 1
Joined: Wed Oct 13, 2021 9:14 am

Wed Oct 13, 2021 10:03 am

Hello,

Thank you for your feedback.
Sorry, our Spire.XLS does not support grouping by function. As your data is large, we suggest that you directly import the data of the CSV file into the DataTable using our Spire.Xls(DataTable t = worksheet.ExportDataTable();), then import it into the database, and finally use the SQL statement to operate. This speed will be much faster.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS