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
{
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
{
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)
{
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
}
}