应用c#从Excel读取数据

最新推荐文章于 2024-07-09 10:27:57 发布

原创最新推荐文章于 2024-07-09 10:27:57 发布 · 920 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #c# #null #application #string #import

ASP.NET 专栏收录该内容

3 篇文章

订阅专栏

这段代码展示了如何使用C#初始化Excel应用程序对象，打开工作簿，读取工作表上的数据，并进行数据处理。通过遍历单元格，将非空单元格的数据拼接成字符串数组，然后调用ImportSurvey方法进行数据导入。此外，还演示了如何向Excel文件写入数据，创建二维字符串数组并填充到工作表中。

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

//初始化对象

Application app = null;

Workbook MyBook = null;

Worksheet mySheet = null;

            string sPath = ConfigurationManager.AppSettings["ExportTemp"].ToString();
            string sMsg = "";
            try
            {
                sPath += Path.GetFileName(file.FileName);

                try
                {
                    file.SaveAs(sPath);

ModelSurveyCommon oModel = new ModelSurveyCommon();

//创建对象

                    app = new Application();
                    MyBook = app.Workbooks.Open(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    mySheet = (Worksheet)MyBook.Worksheets[1];
                    Range cellB;
                    string sValue, aTemp = "";
                    cellB = (Range)mySheet.Cells[1, 1];//取得单元格[1，1]的数据

                    if (cellB.Value2.ToString() == "SURVEY_COMMON")
                    {
                        for (int j = 1; j < mySheet.UsedRange.Columns.Count + 1; j++)
                        {
                            cellB = (Range)mySheet.Cells[2, j];
                            if (cellB.Value2 != "" && cellB.Value2 != null)
                            {
                                aTemp = aTemp + cellB.Value2.ToString() + "#^$@@";
                            }
                            else
                            {
                                aTemp = aTemp + "#^$@@";
                            }
                        }
                        string[] sFieldName = Common.Common.SplitString(aTemp, "#^$@@");
                        //Response.Write("aTemp:" + aTemp + "<br>");
                        for (int i = 3; i < mySheet.UsedRange.Rows.Count + 1; i++)
                        {
                            sValue = "";
                            for (int j = 1; j < mySheet.UsedRange.Columns.Count + 1; j++)
                            {
                                cellB = (Range)mySheet.Cells[i, j];
                                if (cellB.Value2 != "" && cellB.Value2 != null)
                                {
                                    sValue = sValue + cellB.Value2.ToString() + "#^$@@";
                                }
                                else
                                {
                                    sValue = sValue + "#^$@@";
                                }
                            }
                            //Response.Write("sValue:" + sValue);
                            //Response.Write("sValue:" + sValue + "<br>");
                            string[] sFiledValue = Common.Common.SplitString(sValue, "#^$@@");

                            oModel.ImportSurvey(sFieldName, sFiledValue);
                        }
                        sMsg = "Import Successfully.";
                    }
                    else
                    {
                        sMsg = "Table incorrect.";
                    }

                }
                catch
                {
                    sMsg = "Import data error.";
                }
            }
            catch
            {
                sMsg = "Please choose file.";
            }

//清空对象

            if (mySheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                mySheet = null;
            }
            if (MyBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
                MyBook = null;
            }

            //关闭对象
            app.Workbooks.Close();
            app.Quit();

            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
            GC.Collect(); //垃圾回收

利用二组数组导入数据到EXCEL

string sPath =@"C:/Inetpub/wwwroot/test_20110222/test/Report/";
string sFileName = "test_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
sPath += sFileName;

System.IO.File.Copy(@"C:/Inetpub/wwwroot/test_20110222/test/Report/test.xls", sPath, true);

            Application app = null;
            Workbook MyBook = null;
            Worksheet mySheet = null;
            app = new Application();
            MyBook = app.Workbooks.Open(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            mySheet = (Worksheet)MyBook.Worksheets[1];
            int row = 10000, colCount =30;
            string[,] ss = new string[row, colCount];

            for (int j = 1; j < row; j++)
            {
                for (int k = 1; k < colCount; k++)
                {
                    ss[j, k] = j+":"+k;
                   // mySheet.Cells[j, k] = j + ":" + k;
                }
            }

            mySheet.get_Range("A2", "Z10000").Value2 = ss;

            MyBook.Save();

            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
            GC.Collect();
            GC.WaitForPendingFinalizers();