C#操作Excel（读/写）

2023-12-05 17:45: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[1, 1] = "First Row First Column";
            excel.Cells[1, 2] = "First Row Second Column";
            excel.Cells[2, 1] = "Second Row First Column";
            excel.Cells[2, 2] = "Second Row Second Column";
            excel.Visible = true;//使Excel可视
           }

View Code

（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 > 0)
                    {
                        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[1];
                        Microsoft.Office.Interop.Excel.Range range;
                        string[] NameS = { "序号?", "姓名", "性别", "电话", "手机型号", "电脑型号", "汽车品牌", "购买时间" };
                        for (int i = 0; i < NameS.Length; i++)
                        {
                            worksheet.Cells[1, i + 1] = NameS[i];
                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                            range.Interior.ColorIndex = 15;
                            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 = 0; r < List.Count; r++)
                        {
                            worksheet.Cells[r + 2, 1] = (r + 1); 
                            worksheet.Cells[r + 2, 2] = “张三”;
                            worksheet.Cells[r + 2, 3] = “男”;
                            worksheet.Cells[r + 2, 4] = “18818881888”;
                            worksheet.Cells[r + 2, 5] =”三星”;
                            worksheet.Cells[r + 2, 6] =“攒的”;                            worksheet.Cells[r + 2, 7] =“大挂”;
                            worksheet.Cells[r + 2, 8] = “2014-01-25”;
                            Range rng01 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, 8];
                            Range rng02 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, 2];
                            rng01.ColumnWidth = 45; //第八列变宽
                            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);
            }
        }

View Code

（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 = 0;
            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 = 0; i < count; i++)
            {
                string error = string.Empty;
                var model = List[i];
                try
                {
                    if (Cis.IsHasid(model))// 已存在，不可插入
               {
                        error += "第" + (i + 2) + "行报错：该id:" + model.id + "已存在";
                    }
                }
                catch (Exception ex)
                {
                    error += "第" + (i + 2) + "行报错： 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 > 0)
                {
                    var sheetname = rows[0][2].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 > 1)
                    {
                        for (int i = 1; i < count; i++)
                        {
                            var now = DateTime.Now;
                            List.Add(new Model. it { id = dtExcel.Rows[i][0].ToString(), Tid = dtExcel.Rows[i][1].ToString(), State = 11, Ontime = now, Status = 1, 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;
            }
        }

View Code

C#操作Excel（读/写）

码农公寓

相关文章