C#操作Excel（读/写）

2023-08-17 14:29:10

http://www.cnblogs.com/litianfei/archive/2008/03/21/1116906.html 写的很详细

一、操作Excel 首先导出Excel

（1）示例：

/// <summary>

        /// 操作Excel

        /// </summary>

        /// <param name="args"></param>

        static void Main(string[] args)

        {

            Application excel = new Application();//引用Excel对象

            excel.Application.Workbooks.Add(true);//引Excel工作薄

            excel.Cells[, ] = "First Row First Column";

            excel.Cells[, ] = "First Row Second Column";

            excel.Cells[, ] = "Second Row First Column";

            excel.Cells[, ] = "Second Row Second Column";

            excel.Visible = true;//使Excel可视

           }

（2）实例：

 private void InputEx()

        {

            try

            {

                String Pid=Request.QueryString[“id”];

                if (Pid != "")

                {

                    string STime = " 1990-01-01 00:00:00";

                    string Etime = DateTime.Now.ToString();

                    if (!string.IsNullOrEmpty(begintime.Value))

                    {

                        STime = begintime.Value + " 00:00:00";

                    }

                    if (!string.IsNullOrEmpty(endtime.Value))

                    {

                        Etime = endtime.Value + " 23:59:59";

                    }

                    IList<Model.Renew> List = new BLL.Service().GetInputEx(Pid,STime,Etime);

                    #region 导出

                    if (List.Count > )

                    {

                        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

// 创建Excel应用程序对象的一个实例，相当于我们从开始菜单打开Excel应用程序

                        if (xlApp == null)

                        {

                            ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert('您未安装Office');</script>");

                            return;

                        }

                        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;

                        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                        Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

                        Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[];

                        Microsoft.Office.Interop.Excel.Range range;

                        string[] NameS = { "序号?", "姓名", "性别", "电话", "手机型号", "电脑型号", "汽车品牌", "购买时间" };

                        for (int i = ; i < NameS.Length; i++)

                        {

                            worksheet.Cells[, i + ] = NameS[i];

                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ];

                            range.Interior.ColorIndex = ;

                            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;

                            range.Borders.LineStyle = XlLineStyle.xlContinuous;

                            range.Borders.LineStyle = XlLineStyle.xlContinuous;

                            range.ColumnWidth = 15.5;

                            range.Font.Bold = true;

                        }

                        for (int r = ; r < List.Count; r++)

                        {

                            worksheet.Cells[r + , ] = (r + );

                            worksheet.Cells[r + , ] = “张三”;

                            worksheet.Cells[r + , ] = “男”;

                            worksheet.Cells[r + , ] = “”;

                            worksheet.Cells[r + , ] =”三星”;

                            worksheet.Cells[r + , ] =“攒的”;                            worksheet.Cells[r + , ] =“大挂”;

                            worksheet.Cells[r + , ] = “--”;

                            Range rng01 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + , ];

                            Range rng02 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + , ];

                            rng01.ColumnWidth = ; //第八列变宽

                            rng02.HorizontalAlignment = XlHAlign.xlHAlignLeft; //第二列居左

                        }

                        xlApp.Visible = true;

                    }

                    else

                    {

                        ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert('根据条件未查询到数据');</script>");

                        return;

                    }

                    #endregion

                }

                else

                {

                    ClientScript.RegisterStartupScript(this.GetType(), "", "<script type=\"text/javascript\">alert(请选择查询条件?');</script>");

                    return;

                }

            }

            catch (Exception ex)

            {

                Common.Log.Error("导出信息页面错误信息：" + ex.Message);

            }

        }

（3）主要参数（引用上面链接内容）：

Excel的处理：如果在你已知Excel格式的情况下，可以控制Excel,如下：
1、显示当前窗口：ExcelApp.Visible := True;
2、更改 Excel 标题栏：ExcelApp.Caption := '标题内容';
3、添加新工作簿：ExcelApp.WorkBooks.Add;
4、设置第2个工作表为活动工作表：ExcelApp.WorkSheets[2].Activate;
5、给单元格赋值：ExcelApp.Cells[1,1].Value := '第一行第一列';
6、设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth
:= 5;
7、设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelApp.ActiveSheet.Rows[2].RowHeight
:= 1/0.035; // 1厘米
8、文字水平居中：Excelid.worksheets[1].Rows[1].HorizontalAlignment :=
$FFFFEFF4; 文字垂直居中：Excelid.worksheets[1].Rows[1].VerticalAlignment := $FFFFEFF4;
9、插入一行或一列：a. ExcelApp.ActiveSheet.Rows[2].Insert;b.
ExcelApp.ActiveSheet.Columns[1].Insert;
10、删除一行或一列：a. ExcelApp.ActiveSheet.Rows[2].Delete;b.
ExcelApp.ActiveSheet.Columns[1].Delete;
11、合并单元格：ExcelApp.worksheets[1].range[A1:F8'].Merge(abc);注：要声明变量abc: Variant;
12、竖行显示文字：ExcelApp.worksheets[1].Cells.Item[1,1].Orientation:=
xlVertical;
13、单元格加边线：ExcelApp.worksheets[1].Range[A1:F8].Borders.LineStyle := 1;
14、在第8行之前插入分页符：ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
15、在第4列之前删除分页符：ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
16、指定边框线宽度：ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
17、拷贝操作：a.拷贝整个工作表：ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;从A1位置开始粘贴：ExcelApplication1.ActiveSheet.Range.[ 'A1'
].PasteSpecial;
d.从文件尾部开始粘贴：ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18、清除第一行第四列单元格公式：ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
19、工作表保存：if not ExcelApp.ActiveWorkBook.Saved then ExcelApp.ActiveSheet.PrintPreview;
20、工作表另存为：ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
21、放弃存盘：ExcelApp.ActiveWorkBook.Saved := True;
22、关闭工作簿：ExcelApp.WorkBooks.Close;
23、退出 Excel：ExcelApp.Quit;
下面是有关打印页面控制的语句：
24、设置第一行字体属性：ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color
:= clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold
:= True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine
:= True;
ExcelApp.ActiveSheet.Rows[1].Font.size:=10;
25、进行页面设置：a.页眉：ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：ExcelApp.ActiveSheet.PageSetup.CenterFooter := '共&N页第&P页';
c.页眉到顶端边距2cm：ExcelApp.ActiveSheet.PageSetup.HeaderMargin :=
2/0.035;
d.页脚到底端边距3cm：ExcelApp.ActiveSheet.PageSetup.HeaderMargin :=
3/0.035;
e.顶边距2cm：ExcelApp.ActiveSheet.PageSetup.TopMargin :=
2/0.035;
f.底边距2cm：ExcelApp.ActiveSheet.PageSetup.BottomMargin :=
2/0.035;
g.左边距2cm：ExcelApp.ActiveSheet.PageSetup.LeftMargin :=
2/0.035;
h.右边距2cm：ExcelApp.ActiveSheet.PageSetup.RightMargin :=
2/0.035;
i.页面水平居中：ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
26、打印预览工作表：ExcelApp.ActiveSheet.PrintPreview;
27、打印输出工作表：ExcelApp.ActiveSheet.PrintOut;
对Excel的其他控制：
28、excel的多单元格合计功能：ExcelApp..Cells[ARow, ACol].Formula
:= '=
SUM($+IntToStr(BeginRow) +:$ + IntToStr(EndRow) +');
注：声明变量ARow, ACol: Integer;
29、打开已经存在的Excel文件： ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);

二、读取Excel

 List<Model.it> List = new List<Model.it>();

         /// <summary>

        /// 导入按钮时间，将Excel数据导入列表里

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        public void GetExcel_Click(object sender, EventArgs e)

        {

            string fullpath = string.Empty;

            SaveFile(ref fullpath);

            if (string.IsNullOrEmpty(fullpath))

            {

                MessageBox("文件格式错误", "", Page.ClientScript);

                return;

            }

            List.Clear();

            ExcelToList(fullpath);

        }

        /// <summary>

        /// 保存按钮事件，将excel数据保存到数据库

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        public void Save_Click(object sender, EventArgs e)

        {

            int id = Request.QueryString[“id”];

            string log = string.Empty;

            int errorcount = ;

            DateTime dt = DateTime.Now;

            string tag = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Millisecond.ToString();

            int count = List.Count;

            for (int i = ; i < count; i++)

            {

                string error = string.Empty;

                var model = List[i];

                try

                {

                    if (Cis.IsHasid(model))// 已存在，不可插入

               {

                        error += "第" + (i + ) + "行报错：该id:" + model.id + "已存在";

                    }

                }

                catch (Exception ex)

                {

                    error += "第" + (i + ) + "行报错： id=" + model.id + "错误原因：" + ex.Message;

                }

                if (!string.IsNullOrEmpty(error))

                {

                    log += error;

                    errorcount++;

                    FileIO.WriteFile(Server.MapPath("~/Log/UploadExcelPath") + "/logs " + "_" + tag + ".txt", error);

                }

            }

            List.Clear();

            btn_Save.Visible = true;

            if (!string.IsNullOrEmpty(log))

            {

                ScriptManager.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(), "alert(‘计划导入" + count + "行记录，其中" + errorcount + "行错，" + (count - errorcount) + "行成功，<a  target=\"_blank\" href=\"" + "/Log/UploadExcelPath" + "/logs" + "_" + tag + ".txt" + "\">查看错误地址</a>');");

            }

            else

                MessageBox("全部导入成功", "", Page.ClientScript);

        }

  /// <summary>

        ///上传提交的文件

        /// </summary>

        public void SaveFile(ref string fullpath)

        {

            string fileName = this.fileUpExcel.FileName;

            string path = Server.MapPath("~/Log/UploadExcelPath");

            string fileType = fileUpExcel.PostedFile.ContentType;

            if ((fileType == "application/vnd.ms-excel" ||

                      fileType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") ||

                      (fileType == "application/octet-stream" &&

                      (Path.GetExtension(fileName) == ".xls" || Path.GetExtension(fileName) == ".xlsx")))

            {

                DateTime dt = DateTime.Now;

                string tag = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Millisecond.ToString();

                string name = "nit_" + tag + "_" + fileName;

                //判断是否有该目录?

                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(path);

                if (!dir.Exists)

                {

                    dir.Create();

                }

                fullpath = path + "//" + name;

                //如果存在,删除文件

                if (File.Exists(fullpath))

                {

                    File.Delete(fullpath);

                }

                this.fileUpExcel.SaveAs(fullpath);//上传

            }

        }

/// <summary>

        /// 导出Excel到List并展现出来

        /// </summary>

        /// <param name="fullpath"></param>

        /// 引用using System.Data.OleDb;

        public void ExcelToList(string fullpath)

        {

            DataTable dtExcel = null;

            // using (OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"; Data Source=" + fullpath + ";"))//32位?

            using (OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"; Data Source=" + fullpath + ";"))//64位?

            {

                oleCon.Open();

                DataTable dtNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取excel表名

                oleCon.Close();

                var rows = dtNames.Rows;

                if (rows.Count > )

                {

                    var sheetname = rows[][].ToString();

                    OleDbDataAdapter oldAdapter = new OleDbDataAdapter("select * from [" + sheetname + "]", oleCon);//获取表数据

                    DataSet ds = new DataSet();

                    oldAdapter.Fill(ds, "table");

                    oleCon.Close();

                    dtExcel = ds.Tables["table"];

                    var count = dtExcel.Rows.Count;

                    if (count > )

                    {

                        for (int i = ; i < count; i++)

                        {

                            var now = DateTime.Now;

                            List.Add(new Model. it { id = dtExcel.Rows[i][].ToString(), Tid = dtExcel.Rows[i][].ToString(), State = , Ontime = now, Status = , Update_time = now });

                        }

                        clist.DataSource = List;

                        clist.DataBind();

                        btn_Save.Visible = true;

                    }

                    else

                    {

                        MessageBox("Excel表格无数据,请重新导入", "", Page.ClientScript);

                        return;

                    }

                }

                else

                {

                    MessageBox("Excel表格无数据,请重新导入", "", Page.ClientScript);

                    return;

                }

            }

            if (dtExcel == null)

            {

                //读取错误

                MessageBox("Excel读取错误,请重新导入", "", Page.ClientScript);

                return;

            }

        }

码农公寓

相关文章