EXCEL操作类
来源：互联网 发布：130万网络摄像机软件 编辑：程序博客网 时间：2024/05/14 23:35
using System;using System.Collections.Generic;using System.Text;using Word = Microsoft.Office.Interop.Word;using Excel = Microsoft.Office.Interop.Excel;using Office = Microsoft.Office.Core;using Microsoft.VisualStudio.Tools.Applications.Runtime;namespace CRMScenarios{ /// <summary> /// All the common routines for MSOffice documents handling. /// </summary> static class OfficeApi { /// <summary> /// Get the default subject for attachment /// </summary> /// <returns></returns> internal static string GetSubjectForAttachment() { return global::CRMScenarios.Resources.subjectOfActivityCreated; } /// <summary> /// Is the document already saved? /// </summary> /// <param name="OfficeDoc"></param> /// <returns></returns> internal static bool IsDocumentSaved(object OfficeDoc) { if (OfficeDoc is Word.Document) { return ((Word.Document)OfficeDoc).Saved; } if (OfficeDoc is Excel.Workbook) { return ((Excel.Workbook)OfficeDoc).Saved; } return false; } /// <summary> /// Create a table in word document at the given range. /// </summary> /// <param name="Document"></param> /// <param name="range"></param> /// <param name="numCols"></param> /// <returns></returns> internal static Word.Table CreateTable(Word.Document Document, Word.Range range, int numCols) { Word.Table table = null; object defaultTableBehavior = System.Reflection.Missing.Value; object autoFitBehavior = System.Reflection.Missing.Value; try { table = Document.Tables.Add(range, 1, numCols, ref defaultTableBehavior, ref autoFitBehavior); object styleName = "Table Grid"; table.set_Style(ref styleName); } catch (System.Runtime.InteropServices.COMException e) { if (e.ErrorCode == -2146823683) { throw new Exception(global::CRMScenarios.Resources.TableCannotCreate, e); } } catch (System.Exception) { /* * dont have to bother. */ } return table; } /// <summary> /// Activate the windo for the given document. /// </summary> /// <param name="OfficeDoc"></param> internal static void ActivateWindow(object OfficeDoc) { if (OfficeDoc is Word.Document) { ((Word.Document)OfficeDoc).Activate(); ((Word.Document)OfficeDoc).Application.Activate(); ((Word.Document)OfficeDoc).Application.ShowMe(); ((Word.Document)OfficeDoc).ActiveWindow.Activate(); ((Word.Document)OfficeDoc).ActiveWindow.SetFocus(); ; } if (OfficeDoc is Excel.Workbook) { ; } } /// <summary> /// Says whether the document belongs to an outlook mail item or not. /// </summary> /// <param name="OfficeDoc"></param> /// <returns></returns> internal static bool IsOutLook(object OfficeDoc) { try { if (((Word.Document)OfficeDoc).Email.CurrentEmailAuthor != null) { return true; } } catch { // nothing. } return false; } /// <summary> /// Is the format of the document supported by the solution. /// </summary> /// <param name="doc"></param> /// <returns></returns> private static bool IsSupportedFormat(Word.Document doc) { if (doc == null) { return false; } int format = doc.SaveFormat; if ((format == (int)Word.WdSaveFormat.wdFormatHTML) || (format == (int)Word.WdSaveFormat.wdFormatRTF) || (format == (int)Word.WdSaveFormat.wdFormatDocument) || (format == (int)Word.WdSaveFormat.wdFormatTemplate)) { return true; } return false; } /// <summary> /// Get the name of the office docuemnt /// </summary> /// <param name="OfficeDoc"></param> /// <returns></returns> internal static string GetDocumentName(object OfficeDoc) { if (OfficeDoc is Word.Document) { return ((Word.Document)OfficeDoc).Name; } if (OfficeDoc is Excel.Workbook) { return ((Excel.Workbook)OfficeDoc).Name; } return string.Empty; } /// <summary> /// Says whether the document is read only. /// </summary> /// <param name="OfficeDoc"></param> /// <returns></returns> internal static bool IsReadOnlyDocument(object OfficeDoc) { if (OfficeDoc is Word.Document) { return ((Word.Document)OfficeDoc).ReadOnly; } if (OfficeDoc is Excel.Workbook) { return ((Excel.Workbook)OfficeDoc).ReadOnly; } return true; } /// <summary> /// Saves the document. /// </summary> /// <param name="OfficeDoc"></param> internal static void SaveTheDocument(object OfficeDoc) { if (IsReadOnlyDocument(OfficeDoc)) { Util.ShowMsg(global::CRMScenarios.Resources.DocReadOnlyMessageRename); return; } if (OfficeDoc is Word.Document) { ((Word.Document)OfficeDoc).Save(); } if (OfficeDoc is Excel.Workbook) { ((Excel.Workbook)OfficeDoc).Save(); } } /// <summary> /// Insert date to the office document /// The table is represented in the array which is of two sub arrays in which the first one /// corresponds to column one and second sube array is column two. /// </summary> /// <param name="OfficeDoc"></param> /// <param name="dataToBeInserted"></param> internal static void InsertAnyDataToDocAsTable(object OfficeDoc, string[][] dataToBeInserted) { if (OfficeDoc == null) { return; } if (OfficeDoc is Word.Document) { try { InsertAnyDataToDocAsTable((Word.Document)OfficeDoc, dataToBeInserted); ((Word.Document)OfficeDoc).Activate(); } catch (System.Runtime.InteropServices.COMException comExp) { if (comExp.ErrorCode == -2146823037) { Util.ShowMsg(global::CRMScenarios.Resources.TableNotHere); } } catch (Exception InsertionExp) { if (InsertionExp.Message.Equals(global::CRMScenarios.Resources.TableCreationError)) { Util.ShowError(global::CRMScenarios.Resources.wrongFormatMessage); } } } if (OfficeDoc is Excel.Workbook) { InsertAnyDataToDocAsTable((Excel.Workbook)OfficeDoc, dataToBeInserted); } } internal static void InsertAnyDataToDocAsTable(Excel.Workbook WorkBook, string[][] dataToBeInserted) { if ((dataToBeInserted == null) || (dataToBeInserted.Length < 2)) { return; } int rows = dataToBeInserted.Length; int columns = dataToBeInserted[0].Length; Excel.Range range = WorkBook.Application.ActiveCell; int iRow = 0; for (iRow = 0; iRow < rows; iRow++) { int iCol = 0; for (iCol = 0; iCol < columns; iCol++) { if (range.get_Offset(iRow, iCol).Value2 != null) break; } if (iCol < columns) { break; } } if (iRow < rows) { Util.ShowMsg(global::CRMScenarios.Resources.noSpaceWarningExcel); return; } for (iRow = 0; iRow < rows; iRow++) { int iCol = 0; if (dataToBeInserted[iRow] == null) { continue; } for (iCol = 0; iCol < dataToBeInserted[iRow].Length; iCol++) { range.get_Offset(iRow, iCol).Value2 = dataToBeInserted[iRow][iCol]; } } } internal static void InsertAnyDataToDocAsTable(Word.Document Document, string[][] dataToBeInserted) { if ((dataToBeInserted == null) || (dataToBeInserted.Length < 2)) { return; } if ((Document != null) && (IsSupportedFormat(Document) == false)) { Util.ShowError(global::CRMScenarios.Resources.TableFromatWrong); return; } Word.Selection sel = Document.Application.Selection; sel.Words.Last.InsertBefore("\n\n"); Word.Range range = sel.Range; Word.Table table = null; try { table = CreateTable(Document, range, dataToBeInserted[0].Length); } catch (Exception e) { throw new Exception(global::CRMScenarios.Resources.TableCreationError, e); } if (table == null) { Util.ShowError(global::CRMScenarios.Resources.TableCreationUnknown); return; } object beforeRow = System.Reflection.Missing.Value; for (int row = 0; row < dataToBeInserted.Length; row++) { if (dataToBeInserted[row] == null) { continue; } if (row < (dataToBeInserted.Length - 1)) { table.Rows.Add(ref beforeRow); } for (int col = 0; col < dataToBeInserted[row].Length; col++) { table.Cell(row + 1, col + 1).Range.Text = dataToBeInserted[row][col]; table.Cell(row + 1, col + 1).Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft; } } table.Borders.OutsideLineWidth = Word.WdLineWidth.wdLineWidth100pt; table.Borders.InsideLineWidth = Word.WdLineWidth.wdLineWidth100pt; table.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; table.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; table.Borders.InsideColor = Microsoft.Office.Interop.Word.WdColor.wdColorBlack; table.Borders.InsideColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdBlack; table.Borders.OutsideColor = Microsoft.Office.Interop.Word.WdColor.wdColorBlack; table.Borders.OutsideColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdBlack; } /// <summary> /// Insert data to office document as a table with headers. /// </summary> /// <param name="OfficeDoc"></param> /// <param name="header1"></param> /// <param name="header2"></param> /// <param name="data"></param> internal static void InsertDataToDocument(object OfficeDoc, string header1, string header2, string[] data) { if (OfficeDoc == null) { return; } if (OfficeDoc is Word.Document) { try { InsertDataToDocument((Word.Document)OfficeDoc, header1, header2, data); } catch (System.Runtime.InteropServices.COMException comExp) { if (comExp.ErrorCode == -2146823037) { Util.ShowError(global::CRMScenarios.Resources.TableNotHere); } } catch (Exception) { Util.ShowError(global::CRMScenarios.Resources.wrongFormatMessage); } } if (OfficeDoc is Excel.Workbook) { InsertDataToDocument((Excel.Workbook)OfficeDoc, header1, header2, data); } } internal static void InsertDataToDocument(Excel.Workbook WorkBook, string header1, string header2, string[] data) { if (data == null) { return; } int numOfRows = data.Length / 2; Excel.Range range = WorkBook.Application.ActiveCell; int i = 0; for (i = 0; i < numOfRows; i++) { if (range.get_Offset(i, 0).Value2 != null) break; if (range.get_Offset(i, 1).Value2 != null) break; } if (i < numOfRows) { Util.ShowMsg(global::CRMScenarios.Resources.noSpaceWarningExcel); return; } range.get_Offset(0, 0).Value2 = header1; range.get_Offset(0, 1).Value2 = header2; for (i = 0; i < numOfRows; i++) { range.get_Offset(i + 1, 0).Value2 = data[i * 2]; range.get_Offset(i + 1, 1).Value2 = data[(i * 2) + 1]; } } internal static void InsertDataToDocument(Word.Document Document, string header1, string header2, string[] data) { if (data == null) { return; } if ((Document != null) && (IsSupportedFormat(Document) == false)) { Util.ShowError(global::CRMScenarios.Resources.TableFromatWrong); return; } // Clear out any existing information. object start = System.Reflection.Missing.Value; object end = System.Reflection.Missing.Value; object unit = System.Reflection.Missing.Value; object count = System.Reflection.Missing.Value; Word.Selection sel = Document.Application.Selection; sel.Words.Last.InsertBefore("\n\n"); Word.Range range = sel.Range; // Add the table. object defaultTableBehavior = System.Reflection.Missing.Value; object autoFitBehavior = System.Reflection.Missing.Value; Word.Table table = null; try { table = CreateTable(Document, range, 2); } catch (Exception e) { throw new Exception(global::CRMScenarios.Resources.TableCreationError, e); } if (table == null) { Util.ShowError(global::CRMScenarios.Resources.TableCreationUnknown); return; } table.Cell(1, 1).Range.Text = header1; table.Cell(1, 1).Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft; table.Cell(1, 2).Range.Text = header2; table.Cell(1, 2).Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft; object beforeRow = System.Reflection.Missing.Value; for (int i = 0; i < (data.Length / 2); i++) { table.Rows.Add(ref beforeRow); table.Cell(i + 2, 1).Range.Text = data[i * 2]; table.Cell(i + 2, 2).Range.Text = data[(i * 2) + 1]; } table.Borders.OutsideLineWidth = Word.WdLineWidth.wdLineWidth100pt; table.Borders.InsideLineWidth = Word.WdLineWidth.wdLineWidth100pt; table.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; table.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; table.Borders.InsideColor = Microsoft.Office.Interop.Word.WdColor.wdColorBlack; table.Borders.InsideColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdBlack; table.Borders.OutsideColor = Microsoft.Office.Interop.Word.WdColor.wdColorBlack; table.Borders.OutsideColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdBlack; } /// <summary> /// Just insert data to the document. Not in table format. /// Just as plain text /// </summary> /// <param name="OfficeDoc"></param> /// <param name="data"></param> internal static void InsertDataToDocument(object OfficeDoc, string[] data) { if (OfficeDoc == null) { return; } if (OfficeDoc is Word.Document) { InsertDataToDocument((Word.Document)OfficeDoc, data); } if (OfficeDoc is Excel.Workbook) { InsertDataToDocument((Excel.Workbook)OfficeDoc, data); } } internal static void InsertDataToDocument(Excel.Workbook WorkBook, string[] data) { Excel.Range range = WorkBook.Application.ActiveCell; int i = 0; for (i = 0; i < data.Length; i++) { if (range.get_Offset(i, 0).Value2 != null) break; } if (i < data.Length) { Util.ShowMsg(global::CRMScenarios.Resources.noSpaceWarningExcel); return; } int j = 0; for (i = 0; i < data.Length; i++) { if ((data[i] == null) || (data[i] == string.Empty) || (data[i].Trim() == string.Empty)) { continue; } range.get_Offset(j++, 0).Value2 = data[i]; } } internal static void InsertDataToDocument(Word.Document Document, string[] data) { if (data == null) { return; } Word.Selection sel = Document.Application.Selection; Word.Range range = sel.Range; string insertText = string.Empty; foreach (string s in data) { if ((s == null) || (s == string.Empty)) { continue; } insertText = insertText + "\n" + s; } sel.TypeText(insertText); return; } /// <summary> /// Get the full path of a given office document. /// </summary> /// <param name="OfficeDoc"></param> /// <returns></returns> internal static string GetFullQualifiedFilePath(object OfficeDoc) { string returnPath = null; object _AssemblyLocation = null; object _AssemblyLocation0 = null; object _AssemblyLocation1 = null; object SolutionId = null; _AssemblyLocation = OfficeApi.GetCustomProperty(OfficeDoc, "_AssemblyLocation"); _AssemblyLocation0 = OfficeApi.GetCustomProperty(OfficeDoc, "_AssemblyLocation0"); _AssemblyLocation1 = OfficeApi.GetCustomProperty(OfficeDoc, "_AssemblyLocation1"); SolutionId = OfficeApi.GetCustomProperty(OfficeDoc, "Solution ID"); OfficeApi.RemoveCustomProperty(OfficeDoc, "_AssemblyLocation"); OfficeApi.RemoveCustomProperty(OfficeDoc, "_AssemblyLocation0"); OfficeApi.RemoveCustomProperty(OfficeDoc, "_AssemblyLocation1"); OfficeApi.RemoveCustomProperty(OfficeDoc, "CRMVSTO"); OfficeApi.RemoveCustomProperty(OfficeDoc, "_AssemblyName"); if (OfficeDoc is Excel.Workbook) { OfficeApi.RemoveCustomProperty(OfficeDoc, "Solution ID"); } SaveTheDocument(OfficeDoc); if (OfficeDoc is Word.Document) { returnPath = GetFullQualifiedFilePath((Word.Document)OfficeDoc); } if (OfficeDoc is Excel.Workbook) { returnPath = GetFullQualifiedFilePath((Excel.Workbook)OfficeDoc); } // 2 for attach doc. only if attach doc we reach here. OfficeApi.SetCustomProperty(OfficeDoc, "2", "CRMVSTO"); OfficeApi.SetCustomProperty(OfficeDoc, "*", "_AssemblyName"); if (OfficeDoc is Excel.Workbook) { OfficeApi.SetCustomProperty(OfficeDoc, (string)SolutionId, "Solution ID"); } if (_AssemblyLocation != null) { OfficeApi.SetCustomProperty(OfficeDoc, (string)_AssemblyLocation, "_AssemblyLocation"); } else if ((_AssemblyLocation0 != null) && (_AssemblyLocation1 != null)) { OfficeApi.SetCustomProperty(OfficeDoc, (string)_AssemblyLocation0, "_AssemblyLocation0"); OfficeApi.SetCustomProperty(OfficeDoc, (string)_AssemblyLocation1, "_AssemblyLocation1"); } SaveTheDocument(OfficeDoc); return returnPath; } internal static string GetFullQualifiedFilePath(Excel.Workbook WorkBook) { string returnPath = null; try { object o = System.Reflection.Missing.Value; object sC = Excel.XlSaveAction.xlSaveChanges; string originalPath = WorkBook.Path + "\\" + WorkBook.Name; string tempLocation = System.IO.Path.GetTempPath(); System.IO.FileInfo fi = new System.IO.FileInfo(originalPath); WorkBook.Save(); returnPath = tempLocation + /* System.Guid.NewGuid().ToString() + */ WorkBook.Name; fi.CopyTo(returnPath, true); } catch (System.Exception) { return returnPath; }; return returnPath; } internal static string GetFullQualifiedFilePath(Word.Document Document) { string returnPath = null; try { object o = System.Reflection.Missing.Value; object sC = Word.WdSaveOptions.wdSaveChanges; string originalPath = Document.Path + "\\" + Document.Name; string tempLocation = System.IO.Path.GetTempPath(); System.IO.FileInfo fi = new System.IO.FileInfo(originalPath); Document.Save(); returnPath = tempLocation + /* System.Guid.NewGuid().ToString() + */ Document.Name; fi.CopyTo(returnPath, true); } catch (System.Exception) // TODO - catch relevant bugs { return returnPath; } return returnPath; } /// <summary> /// Remove the specified custom property of the document /// </summary> /// <param name="OfficeDoc"></param> /// <param name="propertyName"></param> internal static void RemoveCustomProperty(object OfficeDoc, string propertyName) { if (OfficeDoc is Word.Document) { RemoveCustomProperty((Word.Document)OfficeDoc, propertyName); } if (OfficeDoc is Excel.Workbook) { RemoveCustomProperty((Excel.Workbook)OfficeDoc, propertyName); } } internal static void RemoveCustomProperty(Excel.Workbook WorkBook, string propertyName) { try { Office.DocumentProperties bookProperties = (Office.DocumentProperties)WorkBook.CustomDocumentProperties; bookProperties[propertyName].Delete(); WorkBook.Saved = false; } catch { } } internal static void RemoveCustomProperty(Word.Document Document, string propertyName) { try { Office.DocumentProperties docProperties = (Office.DocumentProperties)Document.CustomDocumentProperties; docProperties[propertyName].Delete(); Document.Saved = false; } catch (Exception) { } } /// <summary> /// Get the value of the required custom property. /// </summary> /// <param name="OfficeDoc"></param> /// <param name="propertyName"></param> /// <returns></returns> internal static object GetCustomProperty(object OfficeDoc, string propertyName) { if (OfficeDoc is Word.Document) { return GetCustomProperty((Word.Document)OfficeDoc, propertyName); } if (OfficeDoc is Excel.Workbook) { return GetCustomProperty((Excel.Workbook)OfficeDoc, propertyName); } return null; } internal static object GetCustomProperty(Excel.Workbook WorkBook, string propertyName) { try { Office.DocumentProperties bookProperties = (Office.DocumentProperties)WorkBook.CustomDocumentProperties; return bookProperties[propertyName].Value; } catch { } return null; } internal static object GetCustomProperty(Word.Document Document, string propertyName) { try { Office.DocumentProperties docProperties = (Office.DocumentProperties)Document.CustomDocumentProperties; return docProperties[propertyName].Value; } catch (Exception) { } return null; } /// <summary> /// Set the value of the specified custom property. /// Will create the property if needed. /// </summary> /// <param name="OfficeDoc"></param> /// <param name="value"></param> /// <param name="propertyName"></param> internal static void SetCustomProperty(object OfficeDoc, string value, string propertyName) { if (OfficeDoc is Word.Document) { SetCustomProperty((Word.Document)OfficeDoc, value, propertyName); } if (OfficeDoc is Excel.Workbook) { SetCustomProperty((Excel.Workbook)OfficeDoc, value, propertyName); } } internal static void SetCustomProperty(Excel.Workbook WorkBook, string value, string propertyName) { object o = System.Reflection.Missing.Value; try { Office.DocumentProperties bookProperties = (Office.DocumentProperties)WorkBook.CustomDocumentProperties; try { bookProperties[propertyName].Value = value; } catch { bookProperties.Add(propertyName, false, Office.MsoDocProperties.msoPropertyTypeString, value, o); } WorkBook.Saved = false; } catch //(System.Exception exp) { } } internal static void SetCustomProperty(Word.Document Document, string value, string propertyName) { object o = System.Reflection.Missing.Value; try { Office.DocumentProperties docProperties = (Office.DocumentProperties)Document.CustomDocumentProperties; try { docProperties[propertyName].Value = value; } catch { } docProperties.Add(propertyName, false, Office.MsoDocProperties.msoPropertyTypeString, value, o); Document.Saved = false; } catch //(System.Exception exp) { } } }}
- php操作excel类
- Excel 操作类
- Excel操作类
- Excel文件操作类
- Excel文件操作类
- Excel操作类C#
- C#Excel操作类
- Excel操作基类
- C# Excel操作类
- Qt操作excel类
- Excel操作类
- .net excel操作类
- Excel操作类
- asp操作Excel类
- Excel操作类备份
- Excel操作类备份
- Excel操作类备份
- Excel操作类备份
- Android中如何编译运行系统自带桌面Launcher2源码
- 读取mp3里的图片
- 国力强大并非一味靠“忍”
- Android数据存储（五） SQLite数据库在Android中的API
- 仿快拍二维码的手动输码页面
- EXCEL操作类
- HYSBZ 1048(记忆化搜索）
- HTML5 WebSockets
- 土坷垃的情怀
- Category Protocol 使用场合
- 家乡的小河
- 线程同步——事件构造
- 省市二级联动数据库生成代码（JAVA）
- 35岁前想要成功的12级跳
