在Excel和gridview之间导入/导出数据 [英] Import/Export data between excel and gridview

问题描述

大家好



请告诉我如何使用c＃将数据从excel导入/导出到gridview，从gridview导出到excel。

Hi all

Please tell me how to import/export data from excel to gridview and from gridview to excel using c#.

推荐答案

您好Codestar，



我有一个解决方案，试试这个;



for Import调用以下函数，它将返回一个数据集，然后你可以使用这个数据集绑定gridview。将您的Excel文件路径传递给导入

Hi Codestar,

I have a solution, try this;

for Import call the below function and it will return a Dataset then you can bind gridview using this Dataset. Pass your Excel file path to import

// you can call like this

DataSet ds=ImportExceltoDataset(ExcelFilePath);



 public static DataSet ImportExceltoDataset(string FilePath)
        {

            Microsoft.Office.Interop.Excel.Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            Range oRng;
            //  creat a Application object
            oXL = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);

                //   get   WorkSheet object 
                int M = oXL.Worksheets.Count;

                for (int sheetnum = 1; sheetnum <= M; sheetnum++)
                {
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[sheetnum];

                    for (int j = oSheet.UsedRange.Cells.Columns.Count + 10; j > 0; j--)
                    {
                        if (j > oSheet.UsedRange.Cells.Columns.Count)
                        {
                            if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).Text.ToString()))
                            {
                                ((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).EntireColumn.Delete(null);
                            }
                        }
                        else
                        {
                            if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).Text.ToString()))
                            {
                                int IsDeleteCunt = 1;
                                for (int i = 1; i < oSheet.UsedRange.Cells.Rows.Count; i++)
                                {
                                    if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j]).Text.ToString()))
                                    {
                                        IsDeleteCunt++;
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }
                                if (IsDeleteCunt == oSheet.UsedRange.Cells.Rows.Count)
                                {
                                    ((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).EntireColumn.Delete(null);
                                }
                            }
                        }
                    }


                }

                DataSet ds = new DataSet();
                string WrkshtName = "";
                for (int N = 1; N <= M; N++)
                {
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[N];
                    WrkshtName = oSheet.Name;
                    System.Data.DataTable dt = new System.Data.DataTable(WrkshtName);

                    //if (dt != null && dt.Rows.Count > 0)
                    //{
                    ds.Tables.Add(dt);
                    DataRow dr;

                    StringBuilder sb = new StringBuilder();

                    int jValue = oSheet.UsedRange.Cells.Columns.Count;
                    int iValue = oSheet.UsedRange.Cells.Rows.Count;
                    int EmptyColumnCount = 1;
                    //  get data columns               
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];

                        string strValue = oRng.Text.ToString();
                        if (strValue.Trim() == "")
                        {
                            EmptyColumnCount++;
                        }

                        dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                    }
                    if (EmptyColumnCount >= jValue)
                    {
                        ds.Tables.Remove(WrkshtName);
                    }
                    else
                    {
                        //get data in cell
                        for (int i = 2; i <= iValue; i++)
                        {
                            dr = ds.Tables[WrkshtName].NewRow();
                            int k = 0;
                            EmptyColumnCount = 1;
                            for (int j = 1; j <= jValue; j++)
                            {
                                oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                                ((Range)oSheet.Cells[1, j]).EntireColumn.AutoFit();

                                string strValue = oRng.Text.ToString();
                                if (strValue.Trim() == "")
                                {
                                    EmptyColumnCount++;
                                }
                                dr[k] = strValue;
                                k++;
                            }
                            if (EmptyColumnCount < jValue)
                            {
                                ds.Tables[WrkshtName].Rows.Add(dr);
                            }
                        }
                    }
                    //}
                }
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                int hWnd = oXL.Application.Hwnd;
            }
          
        }

对于出口，请参阅此链接;





[ ] 导出到Excel来自C＃中的GridView [ ^ ]



http://stackoverflow.com/questions/10411520/export-grid-view-to-excel-and -save-excel-file-to-folder [ ^ ]



< a href =http://www.dotnetfox.com/articles/export-gridview-data-to-excel-using-As对网和 - C-夏普-1021.aspx> http://www.dotnetfox.com/articles/export-gridview-data-to-excel-using-Asp-Net-and-C-Sharp-1021 .aspx [ ^ ]



http://forums.asp.net/t/1255489.aspx [ ^ ]





我希望这会对你有所帮助。



谢谢

Mohan G

For Export, Refer this links;


[]Export to Excel from GridView in C#[^]

http://stackoverflow.com/questions/10411520/export-grid-view-to-excel-and-save-excel-file-to-folder[^]

http://www.dotnetfox.com/articles/export-gridview-data-to-excel-using-Asp-Net-and-C-Sharp-1021.aspx[^]

http://forums.asp.net/t/1255489.aspx[^]


I hope this will help you.

Thank's
Mohan G

导入 [ ^ ]



导出 [ ^ ]



希望它会有所帮助..

Import[^]

Export[^]

Hope it will help..

这篇关于在Excel和gridview之间导入/导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！