C#操作EXCEL

最新推荐文章于 2024-11-12 00:10:25 发布

原创最新推荐文章于 2024-11-12 00:10:25 发布 · 970 阅读

0 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了一种使用Microsoft.Office.Interop.Excel.dll来创建并填充Excel文件的方法。通过C#代码实现自动化生成Excel文档，包括设置标题、数据及保存路径等功能。

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

使用Microsoft.Office.Interop.Excel.dll生成Excel文件：

  1 public class CreateExcel
  2     {
  3         private static Microsoft.Office.Interop.Excel.Application app = null;
  4 
  5         private static Microsoft.Office.Interop.Excel.Workbook workbook = null;
  6 
  7         private static Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
  8 
  9         private static Microsoft.Office.Interop.Excel.Range workSheet_range = null;
 10                     
 11         /// <summary>
 12         /// 新建Excel文件
 13         /// </summary>
 14         /// <param name="dataheader">添加Excel标题表(字段1:英文说明，字段二:中文说明</param>
 15         /// <param name="dataitem">添加Excel数据表</param>
 16         /// <param name="filepath">保存Excel文件物理路径</param>
 17         public static void CreateNewExcel(DataTable dataheader, DataTable dataitem, string filepath)
 18         {
 19             //新建Excel文件
 20 
 21             app = new Microsoft.Office.Interop.Excel.Application();
 22 
 23             app.Visible = true;
 24 
 25             workbook = app.Workbooks.Add(1);//创建workbook 
 26 
 27             worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];//创建worksheet 
 28 
 29             //向Excel中添加标题，第一行为英文说明，第二行为中文说明
 30 
 31              if(dataheader!=null){
 32               for (int i = 0; i < dataheader.Rows.Count; i++)
 33               {
 34                   AddExcelData(1, i + 1, dataheader.Rows[i][0].ToString(), "@", BackColumnName((i + 1).ToString()) + "1", BackColumnName((i + 1).ToString()) + "1");
 35               }
 36 
 37               for (int i = 0; i < dataheader.Rows.Count; i++)
 38               {
 39                   AddExcelData(2, i + 1, dataheader.Rows[i][1].ToString(), "@", BackColumnName((i + 1).ToString()) + "2", BackColumnName((i + 1).ToString()) + "2");
 40               }
 41            }
 42 
 43 
 44             //向Excel中添加数据，数据从第三行开始
 45 
 46             if (dataitem != null)
 47             {
 48                 for (int i = 2; i < dataitem.Rows.Count; i++)
 49                 {
 50                     for (int j = 0; j < dataitem.Columns.Count; j++)
 51                     {
 52                         AddExcelData(i + 1, j + 1, dataitem.Rows[i][j].ToString(), "@", BackColumnName((j + 1).ToString()) + (i + 1).ToString(), BackColumnName((j + 1).ToString()) + (i + 1).ToString());
 53                     }
 54                 }
 55             }
 56 
 57             //保存Excel文件
 58 
 59             workbook.SaveCopyAs(filepath);
 60 
 61             app.Quit();
 62         }
 63 
 64         /// <summary>
 65         /// 向Excel中添加数据
 66         /// </summary>
 67         /// <param name="row">行号</param>
 68         /// <param name="col">列号</param>
 69         /// <param name="data">数据</param>
 70         /// <param name="format">单元格格式</param>
 71         /// <param name="cell1">范围开始格</param>
 72         /// <param name="cell2">范围结束格</param>
 73         public static void AddExcelData(int row, int col, string data, string format, string cell1, string cell2)
 74         {
 75             worksheet.Cells[row, col] = data;
 76 
 77             workSheet_range = worksheet.get_Range(cell1, cell2);
 78 
 79             workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
 80 
 81             workSheet_range.NumberFormat = format;
 82         }
 83 
 84         /// <summary>
 85         /// 返回Excel列标识
 86         /// </summary>
 87         /// <param name="code">列号</param>
 88         /// <returns>返回列标识</returns>
 89         public static string BackColumnName(string code)
 90         {
 91             string resvalue = "";
 92 
 93             string columnlist = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
 94 
 95             #region
 96 
 97             DataTable columntable = new DataTable();
 98 
 99             columntable.Columns.Add("code");
100 
101             columntable.Columns.Add("name");
102 
103             for (int i = 0; i < 256; i++)
104             {
105                 if (i < columnlist.Length)
106                 {
107                     DataRow row = columntable.NewRow();
108 
109                     row["code"] = (i + 1).ToString().Trim();
110 
111                     row["name"] = columnlist[i].ToString().Trim();
112 
113                     columntable.Rows.Add(row);
114                 }
115                 else
116                 {
117                     for (int j = 0; j < columnlist.Length; j++)
118                     {
119                         for (int k = 0; k < columnlist.Length; k++)
120                         {
121                             if (i < 256)
122                             {
123                                 DataRow row = columntable.NewRow();
124 
125                                 row["code"] = (i + 1).ToString().Trim();
126 
127                                 row["name"] = columnlist[j].ToString().Trim() + columnlist[k].ToString().Trim();
128 
129                                 columntable.Rows.Add(row);
130                             }
131 
132                             i++;
133                         }
134                     }
135                 }
136             }
137 
138             #endregion
139 
140             for (int i = 0; i < columntable.Rows.Count; i++)
141             {
142                 if (columntable.Rows[i][0].ToString().Trim() == code.Trim())
143                 {
144                     resvalue = columntable.Rows[i][1].ToString().Trim();
145                 }
146             }
147 
148             return resvalue;
149         }
150     }