c#操作excel方式三:使用Microsoft.Office.Interop.Excel.dll读取Excel文件

1、引用Microsoft.Office.Interop.Excel.dll

 

2、引用命名空间、使用别名

using System.Reflection;using Excel = Microsoft.Office.Interop.Excel;

 

 

3.写入excel

 

写入函数

 public void ToExcel(string strTitle)        {            int nMax = 9;            int nMin = 4;            int rowCount = nMax - nMin + 1;//总行数            const int columnCount = 4;//总列数            //创建Excel对象            Excel.Application excelApp = new Excel.ApplicationClass();            //新建工作簿            Excel.Workbook workBook = excelApp.Workbooks.Add(true);            //新建工作表            Excel.Worksheet worksheet = workBook.ActiveSheet as Excel.Worksheet;            ////设置标题            //Excel.Range titleRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]);//选取单元格            //titleRange.Merge(true);//合并单元格            //titleRange.Value2 = strTitle; //设置单元格内文本            //titleRange.Font.Name = "宋体";//设置字体            //titleRange.Font.Size = 18;//字体大小            //titleRange.Font.Bold = false;//加粗显示            //titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中            //titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中            //titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框            //titleRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细            //设置表头            string[] strHead = new string[columnCount] { "序号", "范围", "分组1", "分组2" };            int[] columnWidth = new int[4] { 8, 16, 8, 10 };            for (int i = 0; i < columnCount; i++)            {                //Excel.Range headRange = worksheet.Cells[2, i + 1] as Excel.Range;//获取表头单元格                Excel.Range headRange = worksheet.Cells[1, i + 1] as Excel.Range;//获取表头单元格,不用标题则从1开始                headRange.Value2 = strHead[i];//设置单元格文本                headRange.Font.Name = "宋体";//设置字体                headRange.Font.Size = 12;//字体大小                headRange.Font.Bold = false;//加粗显示                headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中                headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中                headRange.ColumnWidth = columnWidth[i];//设置列宽              //  headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框               // headRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细            }            //设置每列格式            for (int i = 0; i < columnCount; i++)            {                //Excel.Range contentRange = worksheet.get_Range(worksheet.Cells[3, i + 1], worksheet.Cells[rowCount - 1 + 3, i + 1]);                Excel.Range contentRange = worksheet.get_Range(worksheet.Cells[2, i + 1], worksheet.Cells[rowCount - 1 + 3, i + 1]);//不用标题则从第二行开始                contentRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中                contentRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中                //contentRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框               // contentRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细                contentRange.WrapText = true;//自动换行                contentRange.NumberFormatLocal = "@";//文本格式            }            //填充数据            for (int i = nMin; i <= nMax; i++)            {                int k = i - nMin;                //excelApp.Cells[k + 3, 1] = string.Format("{0}", k + 1);                //excelApp.Cells[k + 3, 2] = string.Format("{0}-{1}", i - 0.5, i + 0.5);                //excelApp.Cells[k + 3, 3] = string.Format("{0}", k + 3);                //excelApp.Cells[k + 3, 4] = string.Format("{0}", k + 4);                excelApp.Cells[k + 2, 1] = string.Format("{0}", k + 1);                excelApp.Cells[k + 2, 2] = string.Format("{0}-{1}", i - 0.5, i + 0.5);                excelApp.Cells[k + 2, 3] = string.Format("{0}", k + 3);                excelApp.Cells[k + 2, 4] = string.Format("{0}", k + 4);            }            //设置Excel可见            excelApp.Visible = true;        }

 

写入按钮函数:

private void button1_Click(object sender, EventArgs e)        {            ToExcel("方式3");        }

 

结果:

 

 

 

 

4.读取excel

 

添加一个opendiog用于选择要读取的excel

 

 

命名空间

using System.Diagnostics;

 

读取函数:

        private void OpenExcel(string strFileName)        {            object missing = System.Reflection.Missing.Value;            Excel.Application excel = new Excel.ApplicationClass();//lauch excel application            if (excel == null)            {               this.label1.Text="Can't access excel";            }            else            {                excel.Visible = false; excel.UserControl = true;                // 以只读的形式打开EXCEL文件                Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,                 missing, missing, missing, true, missing, missing, missing, missing, missing);                //取得第一个工作薄                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);                //取得总记录行数    (包括标题列)                int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数                //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数                //取得数据范围区域   (不包括标题列)                  Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);                  Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);                Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint);                  Excel.Range rng4 = ws.Cells.get_Range("D2", "D" + rowsint);                  object[,] arry1 = (object[,])rng1.Value2;   //get range's value                object[,] arry2 = (object[,])rng2.Value2;                object[,] arry3 = (object[,])rng3.Value2;   //get range's value                object[,] arry4 = (object[,])rng4.Value2;                //将新值赋给一个数组                string[,] arry = new string[rowsint - 1, 4];                //for (int i = 1; i <= rowsint - 1; i++)                for (int i = 1; i <= rowsint - 2; i++)                {                                       arry[i - 1, 0] = arry1[i, 1].ToString();                                      arry[i - 1, 1] = arry2[i, 1].ToString();                    arry[i - 1, 2] = arry3[i, 1].ToString();                                       arry[i - 1, 3] = arry4[i, 1].ToString();                }                string a = "";                for (int i = 0; i <= rowsint - 3; i++)                {                    a += arry[i, 0] + "|" + arry[i, 1] + "|" + arry[i, 2] + "|" + arry[i, 3]+"\n";                                   }               this.label1.Text=a;            }            excel.Quit(); excel = null;          Process[] procs = Process.GetProcessesByName("excel");            foreach (Process pro in procs)            {                pro.Kill();//没有更好的方法,只有杀掉进程            }            GC.Collect();        }

 

读取按钮代码:

  private void button1_Click(object sender, EventArgs e)        {            if (openFileDialog.ShowDialog() == DialogResult.OK)            {                OpenExcel(openFileDialog.FileName);            }        }

 

 结果;