如何在Excel中保存数据到1个工作表

本文关键字：1个 工作 数据 保存 Excel | 更新日期: 2023-09-27 18:10:15

我创建了一个类来创建函数排列PermutationsClass。

我想在excel中保存到1个工作表

加载并保存到excel的类，参数为:

        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
        }

我试着:

    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);
    }

在按钮btnGenerateRandomPermutations我把类ThreadProcCMND在这，但不工作

如何在Excel中保存数据到1个工作表

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);
    }
}

要注意的是，导出到excel是释放所有的资源，而不是使用双点符号，而使用互操作。如果你想以简单的方式做到这一点，那么你可以用EPPlus导出xlsx文件或ExcelLibrary导出xls文件。有关限制的快速参考，您可以查看

相关文章：