CODEWITHSUNDEEP
c#asp.netexcellinqdatatable
Mr doubt
Mr doubt

Reputation: 65

Some data is missing in the Export to Excel using DataTable and Linq

I am exporting three worked sheet in single XL file, but I am missing some user data in the second DataTable (Education Details sheet) and third DataTable (Employeement Details sheet).

The Education Details sheet is some users are not there, but an Employeement Details sheet that users are showing. User Email Id's is there all three Database Tables.

    DataSe ds = new DataSet();
    DataTable dt = new DataTable("Registration Details");
    DataTable dt1 = new DataTable("Education Details");
    DataTable dt2 = new DataTable("Employeement Details");


    dt = bl.Get_Registrationdetailsbydate(bo);
    gv_Regdetails.DataSource = dt;
    gv_Regdetails.DataBind();
    dt1 = bl.Get_Registrationdetailsbydate1(bo);
    dt2 = bl.Get_Registrationdetailsbydate2(bo);
    DataTable filteredEducation = dt1.AsEnumerable()
          .Where(x => dt.AsEnumerable()
          .Any(z => z.Field<string>("Email").Trim() == x.Field<string>("Email").Trim()))
          .CopyToDataTable();
    DataTable filteredEmployee = dt2.AsEnumerable()
          .Where(x => dt.AsEnumerable()
          .Any(z => z.Field<string>("Email").Trim() == x.Field<string>("Email").Trim()))
          .CopyToDataTable();

    dt.TableName = "Registration Details";
    filteredEducation.TableName = "Education Details";
    filteredEmployee.TableName = "Employeement Details";
    ds.Tables.Add(dt);
    ds.Tables.Add(filteredEducation);
    ds.Tables.Add(filteredEmployee);
    ExcelHelper.ToExcel(ds, "DangoteUsers.xls", Page.Response);

I did result base on first DataTable users Email, then fill second DataTable detail users base on first DataTable Email id's. Same as Employment Details. The issue in first DataTable and second DataTable. I am not returning the DataTable also.

I refer this example

Upvotes: 11

Views: 2545

Answers (3)

Viru
Viru

Reputation: 2246

I think your string comparison in linq query is a problem..your email address might have different case which could have caused this issue. Try below code

DataTable filteredEducation = dt1.AsEnumerable()
          .Where(x => dt.AsEnumerable()
          .Any(z => z.Field<string>("Email").Trim().Equals(x.Field<string>("Email").Trim(),StringComparison.CurrentCultureIgnoreCase)))
          .CopyToDataTable();
    DataTable filteredEmployee = dt2.AsEnumerable()
          .Where(x => dt.AsEnumerable()
          .Any(z => z.Field<string>("Email").Trim().Equals(x.Field<string>("Email").Trim(),StringComparison.CurrentCultureIgnoreCase)))
          .CopyToDataTable();

Upvotes: 1

isxaker
isxaker

Reputation: 9446

I had done the same export problem by manual export. First, i need to prepare a responce http responce properly, than add all headers(with rowsapn and colspan attributes) of your tables and then populate data:

//this fun is called after click on export button for example
public void Export(string fileName, GridView gv)
{
    try
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", String.Format("{0}.xls", fileName)));
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "utf-8");
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");

        HttpContext.Current.Response.Charset = "utf-8";//"windows-1251";//
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid  
                Table table = new Table(); 
                table.Width = Unit.Percentage(100);
                //  include the gridline settings  
                table.GridLines = gv.GridLines;
                //header
                TableRow r = new TableRow();
                TableCell cell = new TableCell()
                {
                    ColumnSpan = 18,
                    Text = fileName,
                    BackColor = Color.LightGray,
                    HorizontalAlign = HorizontalAlign.Center
                };
                cell.Font.Size = new FontUnit(14);
                r.Cells.Add(cell);
                table.Rows.Add(r);

                GridViewRow row;
                int rowSpan = 0;

                //second row
                row = CreateSecondHeaderRow();
                table.Rows.AddAt(1, row);

                //first row
                row = CreateFirstHeaderRow(row, rowSpan);
                table.Rows.AddAt(1, row);

                //  add each of the data rows to the table  
                for (int j = 0; j < gv.Rows.Count; j++)
                {
                    //Set the default color    
                    gv.Rows[j].BackColor = System.Drawing.Color.White;
                    for (int i = 0; i < gv.Rows[j].Cells.Count; i++)
                    {
                        gv.Rows[j].Cells[i].BackColor = System.Drawing.Color.White;
                        gv.Rows[j].Cells[i].Width = gv.Columns[i].ItemStyle.Width;
                        gv.Rows[j].Cells[i].Font.Size = gv.Columns[i].ItemStyle.Font.Size;
                        gv.Rows[j].Cells[i].Font.Bold = gv.Columns[i].ItemStyle.Font.Bold;
                        gv.Rows[j].Cells[i].Font.Italic = gv.Columns[i].ItemStyle.Font.Italic;
                        //aligh
                        if (i == 0)
                        {
                            gv.Rows[j].Cells[i].Style["text-align"] = "center";
                        }
                        else
                        {
                            gv.Rows[j].Cells[i].Style["text-align"] = "right";
                        }

                        //for alternate
                        if (j % 2 != 1) gv.Rows[j].Cells[i].BackColor = Color.LightSteelBlue;
                    }

                    table.Rows.Add(gv.Rows[j]);
                }

                table.RenderControl(htw);

                //  render the htmlwriter into the response  
                HttpContext.Current.Response.Write(sw);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
            }
        }
    }
    catch (Exception ex)
    {
        this._hasError = true;
        ShowError(ex);
    }
}

private TableHeaderCell CreateHeaderCell(string text = null, int rowSpan = 0, int columnSpan = 0, Color backColor = default(Color), Color foreColor = default(Color))
{
    if (object.Equals(backColor, default(Color))) backColor = Color.LightGray;
    if (object.Equals(foreColor, default(Color))) foreColor = Color.Black;
    return new TableHeaderCell
    {
        RowSpan = rowSpan,
        ColumnSpan = columnSpan,
        Text = text,
        BackColor = backColor
    };
}

private GridViewRow CreateFirstHeaderRow(GridViewRow row, int rowSpan)
{
    row = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);

    TableHeaderCell cell = CreateHeaderCell("Surplus %");
    row.Controls.Add(cell);

    cell = CreateHeaderCell("The date", columnSpan: 2);
    row.Controls.Add(cell);

    if (this.WithQuantity)
    {
        cell = CreateHeaderCell("Total Quantity", 2 + rowSpan, backColor: Color.Yellow);
        row.Controls.Add(cell);
    }

    cell = CreateHeaderCell("Total Amount", 2 + rowSpan);
    row.Controls.Add(cell);

    cell = CreateHeaderCell("Has elapsed periods from start", columnSpan: (this.WithQuantity ? (SurplusUtil.TheColumnsNumbers * 2) : SurplusUtil.TheColumnsNumbers));
    row.Controls.Add(cell);

    if (this.WithQuantity)
    {
        cell = CreateHeaderCell("Quantity <br style='mso-data-placement:same-cell;' /> surplus", 2 + rowSpan, backColor: Color.Yellow);
        row.Controls.Add(cell);
    }

    cell = CreateHeaderCell("Principal <br style='mso-data-placement:same-cell;' /> surplus", 2 + rowSpan);
    row.Controls.Add(cell);

    return row;
}

private GridViewRow CreateSecondHeaderRow()
{
    GridViewRow row = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);

    TableHeaderCell cell = CreateHeaderCell("Period number", rowSpan: ((this.WithQuantity) ? 2 : 0));
    row.Controls.Add(cell);

    cell = CreateHeaderCell("from", rowSpan: ((this.WithQuantity) ? 2 : 0));
    row.Controls.Add(cell);

    cell = CreateHeaderCell("to", rowSpan: ((this.WithQuantity) ? 2 : 0));
    row.Controls.Add(cell);

    for (int i = 0; i < SurplusUtil.TheColumnsNumbers; i++)
    {
        cell = CreateHeaderCell(i.ToString(),
        columnSpan: ((this.WithQuantity) ? 2 : 0),
        backColor: System.Drawing.Color.FromArgb(198, 239, 206),
        foreColor: System.Drawing.Color.FromArgb(0, 97, 0));
        row.Controls.Add(cell);
    }
    return row;
}

Upvotes: 0

mybirthname
mybirthname

Reputation: 18127

The problem is coming somewhere from the solution of conversion from DataSet to Excel in the article. Using this self made conversion is not a good idea. Use Jet/ACE engine or Microsoft Office Interop. At least they guarantee, they don't have such kind of bugs, which in future can became more. Better use something which is already highly accepted by the community. Here I wrote an approach how to do it with Interop.

First what you need to do is to add the reference to Microsoft.Office.Interop.Excel. Here is how to do it, taken from msdn article

Add the Excel assembly as a reference to the project: Right-click on the project, select Add Reference.

Click the COM tab of the Add Reference dialog box, and find Microsoft Excel 11 Object Library.

Double-click on Microsoft Excel 11 Object Library, and press OK.

Obviously if you have bigger version of Excel 11 use it.

Here is the code, there are comments/regions with the workflow of it. You should use using Excel = Microsoft.Office.Interop.Excel; as reference

    public void ExcelBtn_Click(object sender, EventArgs e)
    {
        DataSet dst = PrepareData();
        byte[] bytes = ExportDataSetToExcel(dst);

        Response.ClearContent();
        Response.ContentType = "application/msoffice";
        Response.AddHeader("Content-Disposition", @"attachment; filename=""ExportedExcel.xlsx"" ");
        Response.BinaryWrite(bytes);
        Response.End();

    }

    public static DataSet PrepareData()
    {
        DataTable badBoysDst = new DataTable("BadBoys");
        badBoysDst.Columns.Add("Nr");

        badBoysDst.Columns.Add("Name");
        badBoysDst.Rows.Add(1, "Me");
        badBoysDst.Rows.Add(2, "You");
        badBoysDst.Rows.Add(3, "Pepe");
        badBoysDst.Rows.Add(4, "Roni");

        //Create a Department Table
        DataTable goodBoysDst = new DataTable("GoodBoys");
        goodBoysDst.Columns.Add("Nr");
        goodBoysDst.Columns.Add("Name");
        goodBoysDst.Rows.Add("1", "Not me");
        goodBoysDst.Rows.Add("2", "Not you");
        goodBoysDst.Rows.Add("3", "Quattro");
        goodBoysDst.Rows.Add("4", "Stagioni");

        DataTable goodBoysDst2 = new DataTable("GoodBoys2");
        goodBoysDst2.Columns.Add("Nr");
        goodBoysDst2.Columns.Add("Name");
        goodBoysDst2.Rows.Add("1", "Not me");
        goodBoysDst2.Rows.Add("2", "Not you");
        goodBoysDst2.Rows.Add("3", "Quattro");
        goodBoysDst2.Rows.Add("4", "Stagioni");

        DataTable goodBoysDst3 = new DataTable("GoodBoys3");
        goodBoysDst3.Columns.Add("Nr");
        goodBoysDst3.Columns.Add("Name");
        goodBoysDst3.Rows.Add("1", "Not me");
        goodBoysDst3.Rows.Add("2", "Not you");
        goodBoysDst3.Rows.Add("3", "Quattro");
        goodBoysDst3.Rows.Add("4", "Stagioni");


        //Create a DataSet with the existing DataTables
        DataSet dst = new DataSet("SchoolBoys");
        dst.Tables.Add(badBoysDst);
        dst.Tables.Add(goodBoysDst);
        dst.Tables.Add(goodBoysDst2);
        dst.Tables.Add(goodBoysDst3);

        return dst;
    }

    public static byte[] ExportDataSetToExcel(DataSet dst)
    {

        #region Create The Excel

        Excel.Application excelApp = null;
        Excel.Workbook excelWorkBook = null;

        try
        {

            excelApp = new Excel.Application();

            if (excelApp == null)
                throw new Exception("You can throw custom exception here too");

            excelWorkBook = excelApp.Workbooks.Add();
            int sheetNr = 1;

            foreach (DataTable table in dst.Tables)
            {
                Excel.Worksheet excelWorkSheet = null;

                //Add a new worksheet or reuse first 3 sheets of workbook with the Datatable name
                if (sheetNr <= excelWorkBook.Sheets.Count)
                {
                    excelWorkSheet = excelWorkBook.Sheets.get_Item(sheetNr);
                }
                else
                {
                    excelWorkSheet = excelWorkBook.Sheets.Add(After: excelWorkBook.Sheets[excelWorkBook.Sheets.Count]);
                }

                excelWorkSheet.Name = table.TableName;

                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }

                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                    }
                }

                sheetNr += 1;
            }
            //make first sheet active
            excelApp.ActiveWorkbook.Sheets[1].Select();
            excelWorkBook.SaveAs(@"c:\temp\DataSetToExcel.xlsx");


        }
        finally
        {
            excelWorkBook.Close();
            excelApp.Quit();

            //you should call GC here because there is memory problem with Interop
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        #endregion


        #region Take byte[] of the excel

        byte[] result = null;
        using (FileStream fs = new FileStream(@"c:\temp\DataSetToExcel.xlsx", FileMode.Open, FileAccess.Read))
        {
            BinaryReader reader = new BinaryReader(fs);
            result = reader.ReadBytes((int)fs.Length);
        }

        #endregion

        #region Delete the excel from the server

        File.Delete(@"c:\temp\DataSetToExcel.xlsx");

        #endregion

        return result;
    }

}

So try to use something established by the community already.This is pretty much full example how to do it with Interop. Personally I prefer to use ACE/JET engines, because there is no memory leaks problems like in the Interop(because of that we are calling the GC in the code). Creation of new sheets with ACE/JET engine is a little bit harder.

Upvotes: 3

Related Questions