12 数据集的使用方法和技巧(ExcelToDataSet, XMLToDataSet)

最新推荐文章于 2024-02-29 13:56:29 发布

原创最新推荐文章于 2024-02-29 13:56:29 发布 · 585 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#dataset #null #excel #string #properties #exception

ASP.NET开发实践专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用C#实现Excel文件与DataSet之间的导入与导出操作，同时涵盖了XML文件与DataSet之间的转换方法。文章提供了详细的代码示例，帮助读者理解和实践数据格式间的转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

    /// <summary>
    /// 从Excel文件导入到DataSet
    /// </summary>
    /// <param name="strFileName"></param>
    /// <returns></returns>
    public DataSet ImportFromExcel(string strFileName)
    {
        DataSet ds = new DataSet();
        if (strFileName == "")
        {
            return null;
        }
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
        OleDbDataAdapter dap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
        try
        {
            dap.Fill(ds, "ExcelInfo");
        }
        catch (Exception err)
        {
            System.Console.WriteLine(err.ToString());
        }
        return ds;
    }

    /// <summary>
    /// 从DataSet导出到Excel
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="strExcelFileName"></param>
    public void ExportToExcle(DataSet ds, string strExcelFileName)
    {
        if (ds.Tables.Count == 0 || strExcelFileName == "")
        {
            return;
        }
        else
        {
            Excel.Application excel = new Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;
            excel.Application.Workbooks.Add(true);
            DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = false;
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();
        }
    }
    /// <summary>
    /// 从XML导入到DataSet
    /// </summary>
    /// <param name="strFileName"></param>
    /// <returns></returns>
    public DataSet ImportFromXML(string strFileName)
    {
        if (strFileName == "")
        {
            return null;
        }
        else
        {
            DataSet ds = new DataSet();
            try
            {
                ds.ReadXml(strFileName, System.Data.XmlReadMode.Auto);
            }
            catch
            {

            }
            return ds;
        }
    }

    /// <summary>
    /// DataSet导出为XML
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="strXMLFileName"></param>
    public void ExportToXML(DataSet ds, string strXMLFileName)
    {
        if (ds.Tables.Count == 0 || strXMLFileName == "")
        {
            return;
        }
        else
        {
            try
            {
                ds.WriteXml(strXMLFileName);
            }
            catch
            { }
        }
    }