最近处理Farpoint保存下来的Excel时,发现表格式有各种问题,下面是修改Excel格式操作到内容,做下笔记 :)
/// <summary>
/// Excel表相关操作
/// </summary>
/// <param name="firstpath">第一个Excel表路径</param>
/// <param name="secondpath">第二个Excel表路径</param>
private void ExcelOperation(string firstpath, string secondpath)
{
try
{
Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook fWorkbook = App.Workbooks.Open(firstpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook sWorkbook = App.Workbooks.Open(secondpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
App.DisplayAlerts = false; //屏蔽操作提示
try
{
((Microsoft.Office.Interop.Excel.Worksheet)sWorkbook.Sheets[1]).Delete(); //删除第二个表第一个Sheet
sWorkbook.Save();
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)fWorkbook.Sheets[1]; //获取第一个表第一个Sheet
sheet.Unprotect(Type.Missing); //取消工作表保护
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[1, Type.Missing]; //获取第一行
sheet.Activate();
App.ActiveWindow.FreezePanes = false; //解除冻结
App.ActiveWindow.SplitColumn = 0;
App.ActiveWindow.SplitRow = 0;
range.Interior.ColorIndex = 0; //设置第一行为白色
range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown); //删除第一行
sheet.Copy(sWorkbook.Sheets[1], Type.Missing); //复制到第二个表中
sWorkbook.Save();
}
catch (Exception ex)
{
}
finally
{
fWorkbook.Close(false, firstpath, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(fWorkbook);
fWorkbook = null;
sWorkbook.Close(false, secondpath, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sWorkbook);
sWorkbook = null;
App.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
}
}
catch (Exception ex)
{
}
}