c# - Excel API Wrapper
来源：互联网 发布：电脑快捷键软件 编辑：程序博客网 时间：2024/05/02 01:01
写这个类是因为想在Excel里画一些东西, 就把Excel API 包了包, 其实它挺好用的, Trust me:)。
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.Office.Interop;using Microsoft.Office.Interop.Excel;using Microsoft.Office.Core;using System.Threading;using System.Diagnostics;using System.IO;namespace ExcelWriter{ public enum TextAlignment { Left = 0, Center = 1, Right = 2 } public class Class_ExcelDoc { #region Members private Application app = null; private Workbook workbook = null; private Worksheet worksheet = null; private Range workSheet_range = null; private string filePath = ""; #endregion #region Constructors /// <summary> /// Create an excel /// </summary> /// <param name="visibility"></param> public Class_ExcelDoc(bool visibility = true) { CreateDoc(visibility); } /// <summary> /// Open an existing excel /// </summary> /// <param name="filePath"></param> /// <param name="visibility"></param> public Class_ExcelDoc(string filePath, bool visibility = true) { OpenDoc(filePath, visibility); } /// <summary> /// Open an existing excel with sheet selected /// </summary> /// <param name="filePath"></param> /// <param name="sheetIndex"></param> /// <param name="visibility"></param> public Class_ExcelDoc(string filePath, int sheetIndex, bool visibility = true) { OpenDoc(filePath, sheetIndex, visibility); } #endregion #region Methods /// <summary> /// Create a new doc /// </summary> /// <param name="visibility"></param> private void CreateDoc(bool visibility = true) { try { app = new Application(); app.Visible = visibility; workbook = app.Workbooks.Add(1); worksheet = (Worksheet)workbook.Sheets[1]; } catch (Exception e) { Console.Write("Error {0}", e.Message); } } private void OpenDoc(string path, bool visibility = true) { try { app = new Application(); app.Visible = visibility; WorkbookInitialize(path); worksheet = (Worksheet)workbook.Sheets[1]; } catch (Exception e) { Console.Write("Error {0}", e.Message); } } private void WorkbookInitialize(string filepath) { if (File.Exists(filepath)) { workbook = app.Workbooks.Open(filepath); this.filePath = filepath; } else { workbook = app.Workbooks.Add(1); this.filePath = ""; } } private void OpenDoc(string path, int sheetIndex, bool visibility = true) { try { app = new Application(); app.Visible = visibility; WorkbookInitialize(path); worksheet = (Worksheet)workbook.Sheets[sheetIndex]; } catch (Exception e) { Console.Write("Error {0}", e.Message); } } public void SelectSheet(int index) { if (worksheet != null) { worksheet = (Worksheet)workbook.Sheets[index]; } } public void SetVisibility(bool visibility) { if (app != null) { app.Visible = visibility; } } /// <summary> /// This method will merge the cells from cellFrom to cellTo. /// You can designate the text and font background color and so on. /// </summary> /// <param name="cellFrom"></param> /// <param name="cellTo"></param> /// <param name="headerText"></param> /// <param name="font"></param> /// <param name="bgColor"></param> /// <param name="textAlignment"></param> /// <param name="columnWidth"></param> /// <param name="thickBorder"></param> public void DrawHeader(string cellFrom, string cellTo, string headerText, System.Drawing.Font font, System.Drawing.Color fontColor, System.Drawing.Color bgColor, TextAlignment textAlignment = TextAlignment.Center, double columnWidth = 0, bool thickBorder = false) { int startAtRow = Helper.GetRowNum(cellFrom); int startAtCol = Helper.GetColumnNum(cellFrom); worksheet.Cells[startAtRow, startAtCol] = headerText; workSheet_range = worksheet.get_Range(cellFrom, cellTo); workSheet_range.Merge(); RenderExcelFont(workSheet_range, font, fontColor); workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); workSheet_range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(bgColor); if (textAlignment == TextAlignment.Left) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else if (textAlignment == TextAlignment.Center) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } else if (textAlignment == TextAlignment.Right) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } if (columnWidth != 0) { workSheet_range.ColumnWidth = columnWidth; } if (thickBorder) { workSheet_range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; } } /// <summary> /// This method fill a cell with specific text,font,fontcolor,bgcolor,textAlignment and so on. /// </summary> /// <param name="cellLoc"></param> /// <param name="cellText"></param> /// <param name="font"></param> /// <param name="fontColor"></param> /// <param name="bgColor"></param> /// <param name="textAlignment"></param> /// <param name="columnWidth"></param> /// <param name="thickBorder"></param> public void DrawData(string cellLoc, string cellText, System.Drawing.Font font, System.Drawing.Color fontColor, System.Drawing.Color bgColor, TextAlignment textAlignment = TextAlignment.Left, double columnWidth = 0, bool thickBorder = false, bool noBorder = false, bool displayAsMinutes = false) { int startAtRow = Helper.GetRowNum(cellLoc); int startAtCol = Helper.GetColumnNum(cellLoc); workSheet_range = worksheet.get_Range(cellLoc, cellLoc); RenderExcelFont(workSheet_range, font, fontColor); workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); workSheet_range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; if (displayAsMinutes) { worksheet.Cells[startAtRow, startAtCol] = Helper.ToMinute(cellText); } else { worksheet.Cells[startAtRow, startAtCol] = cellText; } if (textAlignment == TextAlignment.Left) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else if (textAlignment == TextAlignment.Center) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } else if (textAlignment == TextAlignment.Right) { workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } if (bgColor != null) { workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(bgColor); } if (columnWidth != 0) { workSheet_range.ColumnWidth = columnWidth; } if (thickBorder) { workSheet_range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; } if (noBorder) { workSheet_range.Borders.Value = 0; } } /// <summary> /// /// </summary> /// <param name="work_Range"></param> /// <param name="font"></param> /// <param name="font_color"></param> private void RenderExcelFont(Range work_Range, System.Drawing.Font font, System.Drawing.Color font_color) { work_Range.Font.Name = font.Name; work_Range.Font.Size = font.Size; work_Range.Font.Color = System.Drawing.ColorTranslator.ToOle(font_color); work_Range.Font.Bold = font.Bold; work_Range.Font.Italic = font.Italic; work_Range.Font.Strikethrough = font.Strikeout; work_Range.Font.Underline = font.Underline; } /// <summary> /// Exit /// </summary> public void Exit() { Process[] pProcess; pProcess = System.Diagnostics.Process.GetProcessesByName("Excel"); if (pProcess.Length > 0) { pProcess[0].Kill(); } } /// <summary> /// Auto save /// </summary> public void Save() { if (workbook != null) { if (this.filePath.Equals("") || this.filePath==null) { this.filePath = System.Environment.CurrentDirectory + @"\" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx"; } workbook.SaveAs(this.filePath); } } /// <summary> /// Read data from a range /// </summary> /// <param name="cellFrom"></param> /// <param name="cellTo"></param> /// <returns></returns> public object[,] ReadData(string cellFrom, string cellTo) { try { Range excelRange = worksheet.get_Range(cellFrom, cellTo); object[,] valueArray = (object[,])excelRange.get_Value( XlRangeValueDataType.xlRangeValueDefault); return valueArray; } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } #endregion }}using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using System.Text.RegularExpressions;namespace ExcelWriter{ class Helper { #region Excel Helpers /// <summary> /// ABC=>123 /// </summary> /// <param name="columnName"></param> /// <returns></returns> public static int ToIndex(string columnName) { if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) throw new Exception("Invalid parameter"); int index = 0; char[] chars = columnName.ToUpper().ToCharArray(); for (int i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1); } return index; } /// <summary> /// 123=>ABC /// </summary> /// <param name="index"></param> /// <returns></returns> public static string ToName(int index) { if (index <= 0) throw new Exception("Invaild parameter"); index--; List<string> chars = new List<string>(); do { if (chars.Count > 0) index--; chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString()); index = (int)((index - index % 26) / 26); } while (index > 0); return String.Join(string.Empty, chars.ToArray()); } /// <summary> /// ss=>mm:ss /// </summary> /// <param name="senondStr"></param> /// <returns></returns> public static string ToMinute(string senondStr) { if (!Regex.IsMatch(senondStr, @"\d+.?\d*")) { return senondStr; } int temp = 0; if (senondStr.Contains('.')) { senondStr = senondStr.Substring(0, senondStr.IndexOf('.')); } string result = null; if (Int32.TryParse(senondStr, out temp)) { TimeSpan span = new TimeSpan(0, 0, temp); int normalizedHours = span.Hours; int normalizedMinutes = span.Minutes; int normalizedSeconds = span.Seconds; if (normalizedHours <= 0) { result = "'" + String.Format(@"{0,2:D2}:{1,2:D2}", normalizedMinutes, normalizedSeconds).ToString(); } else { result = "More than 1 hour."; } } if (result == null) { result = "Invalid Data."; } return result.ToString(); } /// <summary> /// Return the number of row depend on a location /// Eg:B3 will return 3 /// </summary> /// <param name="cellLoc"></param> /// <returns></returns> public static int GetRowNum(string cellLoc) { StringBuilder sb = new StringBuilder(); char[] array = cellLoc.ToCharArray(); for (int i = 0; i < array.Length; i++) { if (char.IsDigit(array[i])) { sb.Append(array[i]); } } return Int32.Parse(sb.ToString()); } /// <summary> /// Return the number of column depend on a location /// Eg:B3 will return 2.(B stands for 2) /// </summary> /// <param name="cellLoc"></param> /// <returns></returns> public static int GetColumnNum(string cellLoc) { StringBuilder sb = new StringBuilder(); char[] array = cellLoc.ToCharArray(); for (int i = 0; i < array.Length; i++) { if (char.IsLetter(array[i])) { sb.Append(array[i]); } } return ToIndex(sb.ToString()); } #endregion }}
怎么用昵？
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;namespace ExcelWriter{ public partial class ExcelWriter : Form { public Class_ExcelDoc excell_app; public ExcelWriter() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { System.Drawing.Font font = new System.Drawing.Font("Calibri", 12f, FontStyle.Italic); Color[] colorList = { Color.Red, Color.Tomato, Color.Orange, Color.Yellow, Color.YellowGreen, Color.Green, Color.DarkGreen, Color.LightSkyBlue, Color.Blue, Color.Purple}; excell_app = new Class_ExcelDoc(); excell_app.DrawHeader("B2", "E3", "HelloWorld", font, Color.Black, Color.PowderBlue, thickBorder: true); excell_app.DrawHeader("B4", "C4", "Test", font, Color.Red, Color.Yellow, thickBorder: true); excell_app.DrawHeader("D4", "E4", "Test", font, Color.Red, Color.Yellow, thickBorder: true); for (int i = 0; i < colorList.Length; i++) { for (int j = 2; j < i + 3; j++) { excell_app.DrawData(Helper.ToName(j) + (i + 6).ToString(), i.ToString(), font, colorList[(i + 5) % colorList.Length], colorList[i % colorList.Length], TextAlignment.Center); } } excell_app.Save(); } private void ExcelWriter_FormClosing(object sender, FormClosingEventArgs e) { if (excell_app != null) { excell_app.Exit(); } } }}
请参考：
http://msdn.microsoft.com/en-us/library/ms262200
- c# - Excel API Wrapper
- c# - TFS 2010 API TestRun Wrapper
- fbx sdk c# wrapper
- google group dcmtk c# wrapper
- C# API: 生成和读取Excel文件
- A wrapper class for the DirectSound API
- Wrapper
- wrapper
- 简易通用数据库API c++ wrapper: hisqlapi-1.0.0.9 发布
- 用Java创建Excel文件，Java Excel API的使用,C#也是一样
- A Simple C# Wrapper for the AviFile Library
- Another Simple C# Wrapper For FFmpeg 绝对经典！
- MongoDb C# Wrapper 类 （MongoDb Driver 1.9）
- c# excel
- C# Excel
- c# Excel
- C# excel
- C# excel
- LFS安装（3）安装软件包
- 动态规划
- android自定义listview，添加监听器，解决屏幕滑动组件状态干扰的问题checkbox
- Android ApiDemo学习（五）Animation—— 5 Events
- Objective-C 2.0 内存管理
- c# - Excel API Wrapper
- IE下js调试工具的安装--Companion.JS+DebugBar
- RAISE_APPLICATION_ERROR用法
- 简单Web service 身份验证解决方案
- 2012联发科校园招聘手机软件部门试题详解
- c#中List、Dictionary、ArrayList、Hashtable和数组的区别
- MPI 堆排序
- 必备知识
- matplotlib-绘制精美图表
