How to save data to 1 sheet in Excel

Question

I've created 1 class to create function permutations PermutationsClass.

I want save it to 1 sheet in excel.

Class to load and save to excel with parameter is:

        public void ExcelSave(string e_path, string e_table, DataTable dt)
        {
            // some code here
        }

        public DataTable ExcelLoad(string e_path,string e_table)
        {
            some code here
        }

I tried:

    string noicap = "";
    int length= 0;
    string id= "";
    private void ThreadProcCMND()
    {
        dtCheck = ExcelLoad(fileExcel, "Sheet1");

        fileExcel = Application.StartupPath.ToString() + @"\Data_INFO.xls";
        foreach (DataRow ro in dtCheck .Rows)
        {
            if (ro[0].ToString().Trim() != "")
            {
                if (r["id"].ToString().Trim().Substring(0, ro[0].ToString().Trim().Length) == ro[0].ToString().Trim())
                {
                    length = ro[0].ToString().Trim().Length;
                    noicap = ro[1].ToString().Trim();
                    break;
                }
            }
        }
        int sodao = int.Parse(m_LoginInfos.GetValueByKey("vina_d").ToString());
        permutation = PermutationsClass(r["id"].ToString().Trim(), lenght, sodao);

        ExcelSave(fileExcel, "CMND Tỉnh", dtMaCMND);
    }

In button btnGenerateRandomPermutations I was put class ThreadProcCMND in this but not work.

Search for Microsoft.Office.Interop.Excel, this will lead you to info on how to interact with Excel to read/write excel sheets — Gusman, Aug 29 '15 at 05:51
I assume that your code is working fine and you need help with exporting your data to excel? — Kryptonian, Aug 29 '15 at 07:52
This correct. Class `PermutationsClass` and `ThreadProcCMND` is generate data. I want save it into Excel with sheet name is: `CMND Tỉnh` in `ExcelSave(url_of_file_Excel, "sheet_name", datatable_I_want_export_to_sheet_name)`. — , Aug 29 '15 at 08:06

score 0 · Answer 1 · edited May 23 '17 at 11:43

public void exportDtToExcel(string excelPath, string StrSheetName,DataTable dt)
{
    try
    {
        int ColumnCount;
        if (dt == null || (ColumnCount = dt.Columns.Count) == 0)
        {
            throw new Exception("Null or empty input table!\n");
        }

        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbooks.Add();

        Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

        object[] Header = new object[ColumnCount];                           
        for (int i = 0; i < ColumnCount; i++)
        {
            Header[i] = dt.Columns[i].ColumnName;
        }
        Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnCount]));
        HeaderRange.Value = Header;
        DataTable tempdtsheet;
        Worksheet = Excel.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);                
        {
            Worksheet.Name = StrSheetName;
            tempdtsheet = dt;
            Worksheet.Activate();
        }              
        Excel.Range cells = Worksheet.Cells;
        try
        {              
            for (int i1 = 0; i1 < ColumnCount; i1++)
                Header[i1] = tempdtsheet.Columns[i1].ColumnName;
            Microsoft.Office.Interop.Excel.Range HeaderRange1 = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnCount]));
            HeaderRange1.Value = Header;
            int RowsCount1 = tempdtsheet.Rows.Count;
            object[,] Cells1 = new object[RowsCount1, ColumnCount];

            for (int j = 0; j < RowsCount1; j++)
                for (int i1 = 0; i1 < ColumnCount; i1++)
                {
                    Cells1[j, i1] = tempdtsheet.Rows[j][i1];
                }
            Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount1 + 1, ColumnCount])).Value = Cells1;
            Worksheet.Columns.AutoFit();
            ////deleting other sheets as excel is created with sheet 1,2 and 3 
            ((Microsoft.Office.Interop.Excel.Worksheet)Excel.Worksheets["Sheet3"]).Delete();
            ((Microsoft.Office.Interop.Excel.Worksheet)Excel.Worksheets["Sheet2"]).Delete();
            ((Microsoft.Office.Interop.Excel.Worksheet)Excel.Worksheets["Sheet1"]).Delete();

        }
        catch (Exception e1)
        {
            MessageBox.Show("Error" + e1.Message, "Error!!!");
        }

        if (excelPath != null && excelPath != "")
        {
            try
            {
                Worksheet.SaveAs(excelPath);
                Excel.Quit();
                MessageBox.Show("Output file is saved");
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
            catch (Exception ex)
            {
                throw new Exception("Problem with File path." + ex.Message);
            }
            finally
            {
                Marshal.ReleaseComObject(Worksheet);
                Marshal.ReleaseComObject(Excel);
                Worksheet = null;
            }
        }
        else
        {
            Excel.Visible = true;
        }
    }
    catch (Exception exc)
    {
        throw new Exception("Error in Exporting : " + exc.Message);
    }
}

Point to note while exporting to excel is to release all the resources and not using double dot notation while using interop. If you want to do it in simple way then you can EPPlus to export xlsx files or ExcelLibrary for xls files. For quick reference about limitations you can see this

How to save data to 1 sheet in Excel

1 Answers1