using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Globalization;
using System.Threading;
using Ziye.Common;
namespace Ziye.BusinessLogic.Implementation
{
public class ExcelOperation
{
// 如果客户端计算机运行的是英语版本的 Excel,并且当前用户的区域设置配置为英语之外的某个语言，则 Excel 将尝试查找针对所配置语言的语言包。如果没有找到所需语言包，则会报告错误。
CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;
ExcelInstance excelInstance;
public ExcelOperation(ExcelInstance excelInstance)
{
this.excelInstance = excelInstance;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
}
#region Public Excel Operation Methods
public void OpenExcelSheet(string fileDirectory, string fileName, long sheetNumber)
{
string filePath = Path.Combine(fileDirectory, fileName);
if (File.Exists(filePath))
{
try
{
// Open file and get workbook instance
this.excelInstance.ExcelWorkbook = this.excelInstance.ExcelApp.Workbooks._Open(filePath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
throw new Exception("Can't open " + fileName + " on the server! " + ex.Message);
}
}
else
{
throw new Exception("Open excel error: can't find +" + fileName + " !");
}
if (this.excelInstance.ExcelWorkbook.Worksheets.Count != 0)
{
// choose which sheet to process
this.excelInstance.ExcelWorksheet = this.excelInstance.ExcelWorkbook.Sheets[sheetNumber] as Excel.Worksheet;
}
else
{
throw new Exception(fileName + " does not contain any worksheet!");
}
}
public void InitialColor(Color foreColor, Color backColor)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.UsedRange;
range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);
}
public string ReadExcelCell(long rowNumber, long columnNumber)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
object cellValue = range.get_Value(Type.Missing);
DateTime? dateTime = cellValue as DateTime?;
if (dateTime.HasValue)
{
IFormatProvider culture = new CultureInfo("zh-CN", true);
cellValue = dateTime.Value.ToString(culture);
}
return cellValue == null ? string.Empty : cellValue.ToString().WTrim();
}
public string ReadExcelCell(long rowNumber, string columnName)
{
long columnNumber = this.GetColumnNumberByColumnName(columnName);
return ReadExcelCell(rowNumber, columnNumber);
}
public void AddComment(long rowNumber, long columnNumber, string comment)
{
try
{
if (!string.IsNullOrEmpty(comment))
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
range.AddComment(comment);
}
}
catch(Exception ex)
{
Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
throw new Exception("Error eccured when add Comment" +ex.Message);
}
}
public void DelComment()
{
throw new NotImplementedException();
}
public string GetColumnNameByColumnNumber(long columnNumber)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[1, columnNumber];
string cellValue = range.Text.ToString();
return cellValue;
}
public long GetColumnNumberByColumnName(string columnName)
{
return this.ReadOneRow(1).IndexOf(columnName)+1;
}
public long UsedRowNumber()
{
return (this.excelInstance.ExcelWorksheet.UsedRange.Rows.Count);
}
public long UsedColumnNumber()
{
return (this.excelInstance.ExcelWorksheet.UsedRange.Columns.Count);
}
public IList<string> ReadOneRow(long rowNumber)
{
IList<string> retList = new List<string>();
for (long columnNumber = 1; columnNumber <= this.UsedColumnNumber(); columnNumber++)
{
retList.Add(this.ReadExcelCell(rowNumber, columnNumber));
}
return retList;
}
public void WriteExcelCell(long rowNumber, long columnNumber, string cellValue)
{
throw new NotImplementedException();
}
public void SetExcelCellFontColor(long rowNumber, long columnNumber, Color color)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
range.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
}
public void SetExcelCellBackgroundColor(long rowNumber, long columnNumber, Color color)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
}
public void SetExcelCellMark(long rowNumber, long columnNumber, Color foreColor, Color backColor, string comment)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);
if (!string.IsNullOrEmpty(comment))
{
range.AddComment(comment);
}
}
public void SetExcelRowBackgroundColor(long rowNumber, long startColumnNumber, long columnCount, Color color)
{
for (long i = startColumnNumber; i <= columnCount; i++)
{
Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, i];
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
}
}
public void SaveExcel(string targetDirectory, string fileName)
{
bool oldDisplayAlertState = this.excelInstance.ExcelApp.DisplayAlerts;
string filePath = Path.Combine(targetDirectory, fileName);
DirectoryInfo directoryInfo = new DirectoryInfo(targetDirectory);
try
{
if (directoryInfo.Exists == false)
{
directoryInfo.Create();
}
}
catch (Exception ex)
{
Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
throw new Exception("Can't create the directory which used to save the excel! " + ex.Message);
}
try
{
this.excelInstance.ExcelApp.DisplayAlerts = false;
this.excelInstance.ExcelWorkbook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true);
this.excelInstance.ExcelApp.DisplayAlerts = oldDisplayAlertState;
}
catch (Exception ex)
{
Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
throw new Exception("Can't save the excel! " + ex.Message);
}
}
public void CloseExcelInstance()
{
Thread.CurrentThread.CurrentCulture = oldCultureInfo;
if (this.excelInstance.ExcelWorkbook != null)
{
this.excelInstance.ExcelWorkbook.Close(false, Type.Missing, Type.Missing);
}
if (this.excelInstance.ExcelApp != null)
{
this.excelInstance.ExcelApp.DisplayAlerts = false;
this.excelInstance.ExcelApp.Quit();
}
if (this.excelInstance.ExcelWorksheet != null)
{
if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorksheet) >= 0)
{
this.excelInstance.ExcelWorksheet = null;
}
}
if (this.excelInstance.ExcelWorkbook != null)
{
if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorkbook) >= 0)
{
this.excelInstance.ExcelWorkbook = null;
}
}
if (this.excelInstance.ExcelApp != null)
{
if (Marshal.ReleaseComObject(this.excelInstance.ExcelApp) >= 0)
{
this.excelInstance.ExcelApp = null;
}
}
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
#endregion
}
}
