62,072
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
public class ExcelOperate
{
private object mValue = System.Reflection.Missing.Value;
public ExcelOperate()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void WriteCell(Excel._Worksheet CurSheet,object objCell, object objValue)
{
CurSheet.get_Range(objCell, mValue).Value2 = objValue;
}
/// <summary>
/// 合并单元格,并在合并后的单元格中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteAfterMerge(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;
}
/// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Excel._Worksheet CurSheet,object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}
/// <summary>
/// 单元格自动换行
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void AutoWrapText(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell,objEndCell).WrapText=true;
}
/// <summary>
/// 设置整个连续区域的字体颜色
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="clrColor">颜色</param>
public void SetColor(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
public void SetBorderAll(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 另存为html文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveHtml(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet,Excel._Workbook CurBook,Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch(System.Exception ex)
{
HttpContext.Current.Response.Write( "在释放Excel内存空间时发生了一个错误:"+ex);
}
finally
{
foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
System.GC.SuppressFinalize(this);
}
}