public void SetExcelBorder()
{
Application excelApp = null;
Workbook excelWorkBook = null;
Worksheet excelWorkSheet = null;
Range excelRange = null;
object Nothing = System.Reflection.Missing.Value;
//记录临时文件位置
string strFilePath = Server.MapPath("../Temp/") + "Temp.xls";
string strTemplateName = Server.MapPath("../Template/") + "Template.xls";
try
{
//删除临时文件
if (File.Exists(strFilePath))
{
File.Delete(strFilePath);
}
//拷贝文件
File.Copy(strTemplateName, strFilePath);
//打开临时报表文件
excelApp = new Microsoft.Office.Interop.Excel.Application();
//设置不允许后台错误检查
excelApp.ErrorCheckingOptions.BackgroundChecking = false;
excelWorkBook = excelApp.Workbooks._Open(strFilePath, Nothing, Nothing, Nothing, Nothing,
Nothing,Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
//打开工作簿一
excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets.get_Item(1);
设定起始单元格位置
excelRange = excelWorkSheet.get_Range(excelApp.Cells[6, 3], excelApp.Cells[21, 20]);
//单元格自适应宽度
excelRange.Columns.AutoFit();
excelRange.Rows.AutoFit();
//加入表格边框
Microsoft.Office.Interop.Excel.Borders pborders = excelRange.Borders;
//设置左边框
pborders.get_Item(XlBordersIndex.xlEdgeLeft);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
object pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
设置上边框线
pborders.get_Item(XlBordersIndex.xlEdgeTop);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
设置下边框线
pborders.get_Item(XlBordersIndex.xlEdgeBottom);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
设置右边框线
pborders.get_Item(XlBordersIndex.xlEdgeRight);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
设置内格竖线
pborders.get_Item(XlBordersIndex.xlInsideVertical);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
设置内格横边框线
pborders.get_Item(XlBordersIndex.xlInsideHorizontal);
pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
catch
{
throw new Exception("写入到Excel文件失败!");
}
finally
{
//释放EXCEL资源,垃圾回收
if (excelApp != null)
{
if (excelWorkBook != null)
{
if (excelWorkSheet != null)
{
if (excelRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
excelRange = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkSheet);
excelWorkSheet = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkBook);
excelWorkBook = null;
}
excelApp.Quit();
//强行杀死打开的Excel进程
IntPtr excelIPtr = new IntPtr(excelApp.Hwnd);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
GC.Collect();
}
}