客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
Excel操作类使用方法 1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 OperateExcel excel = new OperateExcel(); 4 //打开Excel 5 excel.Open("d:\\abc.xlsx"); 6 //获取工作表 7 var weet = excel.GetSheet("Sheet2"); 8 //写入Excel 9 excel.SetCellValue(weet, 1, 2, "1011"); 10 ////另存为Excel 11 //excel.SaveAs("d:\\bcd.xlsx"); 12 ////注销Excel进程 13 //excel.Close(); 14 excel.SaveAsHtml(HttpContext.Current.Server.MapPath("aa.html")); 15 16 //杀掉Excel进程 17 excel.KillSpecialExcel(); 18 } Excel操作类 需引用：
1 using System; 2 using System.Data; 3 using System.Configuration; 4 using System.Web; 5 using System.Web.Security; 6 using System.Web.UI; 7 using System.Web.UI.WebControls; 8 using System.Web.UI.WebControls.WebParts; 9 using System.Web.UI.HtmlControls; 10 using Microsoft.Office.Core; 11 using System.Runtime.InteropServices; 12 using System.IO; 13 14 /// <summary > 15 /// Excel操作类 16 /// </summary > 17 public class OperateExcel 18 { 19 public string mFilename; 20 public Microsoft.Office.Interop.Excel.Application app; 21 public Microsoft.Office.Interop.Excel.Workbooks wbs; 22 public Microsoft.Office.Interop.Excel.Workbook wb; 23 public Microsoft.Office.Interop.Excel.Worksheets wss; 24 public Microsoft.Office.Interop.Excel.Worksheet ws; 25 public OperateExcel() 26 { 27 // 28 // TODO: 在此处添加构造函数逻辑 29 // 30 } 31 32 /// <summary> 33 /// 创建一个Excel对象 34 /// </summary> 35 public void Create() 36 { 37 app = new Microsoft.Office.Interop.Excel.Application(); 38 wbs = app.Workbooks; 39 wb = wbs.Add(true); 40 } 41 42 /// <summary> 43 /// 打开一个Excel文件 44 /// </summary> 45 /// <param name="FileName">Excel文件路径及名称</param> 46 public void Open(string FileName) 47 { 48 object missing = System.Reflection.Missing.Value; 49 app = new Microsoft.Office.Interop.Excel.Application(); 50 app.Visible = true; 51 wbs = app.Workbooks; 52 wb = wbs.Open(FileName, missing, false, missing, missing, missing,missing, missing, missing, true, missing, missing, missing, missing, missing); 53 //wb = wbs.Add(FileName); 54 mFilename = FileName; 55 } 56 57 /// <summary> 58 /// 复制并打开模板文件 59 /// </summary> 60 /// <param name="Path">模板文件位置及文件名</param> 61 /// <param name="sufix">复制后文件所加后缀</param> 62 /// <param name="IsVisible">是否可见</param> 63 /// <returns>复制后文件位置</returns> 64 public string CopyAndOpenTemplate(string excelName) 65 { 66 try 67 { 68 string templetFilePath = HttpContext.Current.Server.MapPath("~\\XlsTemplate\\SampleZhongshuExcelTemplate.xlsx"); 69 string currentFolder = HttpContext.Current.Server.MapPath("~\\UploadFile\\")+DateTime.Now.ToString("yyyy-MM-dd"); 70 string toPath = currentFolder + "\\" + excelName; 71 //string tempFolderName = DateTime.Now.ToString("yyyy-MM-dd").Replace("-", "").Replace("/", "").Replace("\\", ""); 72 //string targetFolder = HttpContext.Current.Server.MapPath("~\\UploadFile\\" + tempFolderName); 73 //如果不存在则创建 74 if (!Directory.Exists(currentFolder)) 75 { 76 Directory.CreateDirectory(currentFolder); 77 } 78 79 File.Copy(templetFilePath, toPath, true); 80 81 //File.SetAttributes(toPath, FileAttributes.Normal); 82 Open(toPath); 83 return toPath; 84 } 85 catch (Exception e) 86 { 87 // 88 KillSpecialExcel(); 89 throw e; 90 91 } 92 } 93 94 /// <summary> 95 /// 获取一个工作表 96 /// </summary> 97 /// <param name="SheetName">工作表名称</param> 98 /// <returns>Excel工作表</returns> 99 public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName) 100 { 101 Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]; 102 return s; 103 } 104 105 /// <summary> 106 /// 添加一个工作表 107 /// </summary> 108 /// <param name="SheetName">工作表名称</param> 109 /// <returns>Excel工作表</returns> 110 public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName) 111 { 112 Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); 113 s.Name = SheetName; 114 return s; 115 } 116 117 /// <summary> 118 /// 删除一个工作表 119 /// </summary> 120 /// <param name="SheetName">工作表名称</param> 121 public void DelSheet(string SheetName) 122 { 123 ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete(); 124 } 125 126 /// <summary> 127 /// 重命名一个工作表 128 /// </summary> 129 /// <param name="OldSheetName">要改名的工作表</param> 130 /// <param name="NewSheetName">工作表新名称</param> 131 /// <returns>工作表</returns> 132 public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName) 133 { 134 Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName]; 135 s.Name = NewSheetName; 136 return s; 137 } 138 139 /// <summary> 140 /// 重命名一个工作表 141 /// </summary> 142 /// <param name="Sheet">Excel工作表实例</param> 143 /// <param name="NewSheetName">新命名的工作表</param> 144 /// <returns>Excel工作表</returns> 145 public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName) 146 { 147 Sheet.Name = NewSheetName; 148 return Sheet; 149 } 150 151 /// <summary> 152 /// 设置工作表的值1 153 /// </summary> 154 /// <param name="ws">要设值的工作表</param> 155 /// <param name="x">行</param> 156 /// <param name="y">列</param> 157 /// <param name="value">要设置的值</param> 158 public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value) 159 { 160 ws.Cells[x, y] = value; 161 } 162 163 /// <summary> 164 /// 设置工作表的值2 165 /// </summary> 166 /// <param name="ws">工作表的名称</param> 167 /// <param name="x">行</param> 168 /// <param name="y">列</param> 169 /// <param name="value">要设置的值</param> 170 public void SetCellValue(string ws, int x, int y, object value) 171 { 172 GetSheet(ws).Cells[x, y] = value; 173 } 174 175 /// <summary> 176 /// 设置工作表属性 177 /// </summary> 178 /// <param name="ws">工作表</param> 179 /// <param name="Startx">开始的行</param> 180 /// <param name="Starty">开始的列</param> 181 /// <param name="Endx">结束的行</param> 182 /// <param name="Endy">结束的列</param> 183 /// <param name="size">大小</param> 184 /// <param name="name">字体名称</param> 185 /// <param name="color">颜色</param> 186 /// <param name="HorizontalAlignment">对齐方式</param> 187 public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment) 188 { 189 //name = "宋体 "; 190 //size = 12; 191 //color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic; 192 //HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight; 193 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; 194 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; 195 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; 196 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; 197 } 198 199 /// <summary> 200 /// 设置工作表的值 201 /// </summary> 202 /// <param name="ws">工作表的名称</param> 203 /// <param name="Startx">开始的行</param> 204 /// <param name="Starty">开始的列</param> 205 /// <param name="Endx">结束的行</param> 206 /// <param name="Endy">结束的列</param> 207 /// <param name="size">大小</param> 208 /// <param name="name">字体名称</param> 209 /// <param name="color">颜色</param> 210 /// <param name="HorizontalAlignment">对齐方式</param> 211 public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment) 212 { 213 //name = "宋体 "; 214 //size = 12; 215 //color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic; 216 //HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight; 217 Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn); 218 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; 219 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; 220 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; 221 ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; 222 } 223 224 225 226 227 /// <summary> 228 /// 合并单元格 229 /// </summary> 230 /// <param name="ws">工作表</param> 231 /// <param name="x1">开始的行</param> 232 /// <param name="y1">开始的列</param> 233 /// <param name="x2">结束的行</param> 234 /// <param name="y2">结束的列</param> 235 public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2) 236 { 237 ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing); 238 } 239 240 /// <summary> 241 /// 合并单元格 242 /// </summary> 243 /// <param name="ws">工作表名称</param> 244 /// <param name="x1">开始的行</param> 245 /// <param name="y1">开始的列</param> 246 /// <param name="x2">结束的行</param> 247 /// <param name="y2">结束的列</param> 248 public void UniteCells(string ws, int x1, int y1, int x2, int y2) 249 { 250 GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing); 251 } 252 253 /// <summary> 254 /// 将表格插入到Excel的指定工作表指定位置 255 /// </summary> 256 /// <param name="dt">DataTable</param> 257 /// <param name="ws">工作表名称</param> 258 /// <param name="startX">开始行</param> 259 /// <param name="startY">开始列</param> 260 public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY) 261 { 262 for (int i = 0; i <= dt.Rows.Count - 1; i++) 263 { 264 for (int j = 0; j <= dt.Columns.Count - 1; j++) 265 { 266 GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString(); 267 } 268 } 269 } 270 271 272 273 /// <summary> 274 /// DataTable表格添加到Excel指定工作表的指定位置 275 /// </summary> 276 /// <param name="dt">DataTable</param> 277 /// <param name="ws">工作表名称</param> 278 /// <param name="startX">开始行</param> 279 /// <param name="startY">开始列</param> 280 public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY) 281 { 282 for (int i = 0; i <= dt.Rows.Count - 1; i++) 283 { 284 for (int j = 0; j <= dt.Columns.Count - 1; j++) 285 { 286 GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j]; 287 } 288 } 289 } 290 291 /// <summary> 292 /// DataTable表格添加到Excel指定工作表的指定位置 293 /// </summary> 294 /// <param name="dt">DataTable</param> 295 /// <param name="ws">工作表</param> 296 /// <param name="startX">开始行</param> 297 /// <param name="startY">开始列</param> 298 public void AddTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY) 299 { 300 for (int i = 0; i <= dt.Rows.Count - 1; i++) 301 { 302 for (int j = 0; j <= dt.Columns.Count - |
2023-10-27
2022-08-15
2022-08-17
2022-09-23
2022-08-13
六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期
今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知
今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道
今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要
我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多
今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不
电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很
我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法
今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win
今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎
请发表评论