54SA.COM|专注于系统运维管理,为中国SA提供动力!
Getting online shouldn't be tough. $7.99 .COMs
系统管理员之家Banner
当前位置: 主页 > 编程开发 > C#开发 >

利用C#和Excel进行报表开发(二)

时间:2011-05-03 12:48来源:未知 编辑:admin

补充阅读:利用C#和Excel进行报表开发(一)

 

在用Excel作报表的时候,可能会碰到分页的情况,这样就要分成多个Sheet显示,如果要在每页都保留表头,就需要将第一页作为模板设计的表头部分拷贝到其他的Sheet上,这时就要用用到Excel中的单元格拷贝。

下面的代码说明如何一个Sheet上的选定内容拷贝到另一个Sheet上:

ThisApplication = new Excel.Application();
ThisWorkbook = ThisApplication.Workbooks.Open("z:Book1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ThisApplication.DisplayAlerts = false;
xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(1);
Excel.Range range = xlSheet.get_Range("A1", Type.Missing);
range.Value = "123";
Excel.Worksheet sheet1 = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(2);
Excel.Range range1 = sheet1.get_Range("B1", Type.Missing);
range.Copy(range1);
ThisWorkbook.SaveAs("z:Book2.xls", Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

可以看到,核心的代码是很简单的,就是选定一个源区域,然后指定另一个目标区域,调用源区域的Copy方法将内容整个复制到目标区域,但是你会发现是连单元格的格式等属性一并拷贝过去的,如果要只拷贝单元格中的文本呢?那就要用到windows剪贴板了:

xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(1);
Excel.Range range = xlSheet.get_Range("A1", Type.Missing);
range.Value = "123";
System.Windows.Forms.Clipboard.SetDataObject(range.Value.ToString());
Excel.Worksheet sheet1 = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(2);
Excel.Range range1 = sheet1.get_Range("B1", Type.Missing);
sheet1.Paste(range1, false);

要注意的是,这种方式只能选一个单元格,复制一个,不能选定一批单元格进行复制。

上面的例子只给出了主要部分的代码,其他的像销毁Excel进程等操作请参考前面的几篇Excel作报表的随笔。

在用Excel作报表的时候,可能需要操作单元格的边框和填充颜色和纹理等操作,下面的代码说明如何设置选中的单元格的填充纹理和边框。

try
{
ThisApplication = new Excel.Application();  ThisWorkbook = ThisApplication.Workbooks.Open("z:Book1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);   ThisApplication.DisplayAlerts = false;
xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(1);
Excel.Range range = xlSheet.get_Range("G4","H5");
range.Value = "123";
Excel.Style st = ThisWorkbook.Styles.Add("PropertyBorder", Type.Missing);
range.Interior.Pattern = Excel.XlPattern.xlPatternCrissCross;
range.Borders.Weight = 2;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlDot;
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
ThisWorkbook.SaveAs("z:Book2.xls", Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
ThisWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
ThisApplication.Workbooks.Close();   ThisApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisApplication);
ThisWorkbook = null;
ThisApplication = null;
GC.Collect();
this.Close();
}

 

 

 

在Excel中,行和列实际上仍然是Range,只不过取得行和列的方法和前面几篇随笔出现的有点不同,要使用Worksheet的Rows和Columns属性的get_Item方法,下面是例子代码,演示了怎样选中行,并且在行之间复制数据,和插入行:

[责任编辑:admin]


------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
用户名:
最新评论 进入详细评论页>>