C#生成word报告
参考:http://blog.csdn.net/malei0311/article/details/6961082
http://blog.sina.com.cn/s/blog_6e2920850100m6mt.html
注:本文所用版本为VS2015/C# + SQL Server2014 + Word2013。
源码下载地址:http://download.csdn.net/detail/wjk343977868/8528695
1、建立word书签模板(格式:.docx)
如下图所示,新建书签的排列顺序为由上到下、由左至右。新建书签的快捷键为:ctrl+shift+F5。
2、数据库表结构和数据,如下图
3、建立C#窗体程序
添加引用:
C:\Program Files(x86)\Microsoft Visual Studio 14.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll
C:\ProgramFiles (x86)\Microsoft Visual Studio 14.0\Visual Studio Tools forOffice\PIA\Office15\Microsoft.Office.Interop.Word.dll
将报告模板放在程序的\bin\bug\目录下。
XML书签配置文件如下:
<?xml version="1.0"?>
<Configxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<TextList>
<Bookmark Name="sum" UseText="false"NewParagraph='false'>
<Sql>
SELECT COUNT(*) as sum FROMstudents;
</Sql>
<Text>
</Text>
</Bookmark>
<Bookmark Name="man" UseText="false"NewParagraph='false'>
<Sql>
SELECT COUNT(*) as man FROMstudents where sex=N'男';
</Sql>
<Text>
</Text>
</Bookmark>
<Bookmark Name="wo" UseText="false"NewParagraph='false'>
<Sql>
SELECT COUNT(*) as wo FROMstudents where sex=N'女';
</Sql>
<Text>
</Text>
</Bookmark>
<Bookmark Name="stu" UseText="true"NewParagraph='true'>
<Sql></Sql>
<Text>
中国科学院大学——2015
</Text>
</Bookmark>
</TextList>
<TableList>
<Bookmark Name="tab">
<Sql>
SELECT name=N'姓名',sex=N'性别',birthday=N'生日',nation=N'民族'
UNIONALL
SELECTname,sex,birthday,nation from students;
</Sql>
</Bookmark>
</TableList>
<ChartList>
<Bookmark Name="chart">
<Sql>
SELECTproductName,UnitPrice from Products;
</Sql>
</Bookmark>
</ChartList>
<ImageList>
<Bookmark Name="pic">
<FilePath>
pic2.jpg
</FilePath>
<Width>440</Width>
<Height>220</Height>
</Bookmark>
</ImageList>
</Config>
XML书签配置文件解析类见:
class XMLManager
{
private string _filepath;
public XMLManager(string filepath)
{
_filepath = filepath;
}
/// <summary>
/// 将XML字符串反序列化成指定类型的对象。
/// </summary>
/// <typeparam name="T">对象的类型。</typeparam>
/// <param name="stream">直接从文件流读取</param>
/// <returns>T</returns>
public static T DesrializeXml<T>(Stream stream)
{
var serializer = new XmlSerializer(typeof(T));
return (T)serializer.Deserialize(stream);
}
}
[XmlRoot(ElementName = "Config")]
public class Configs
{
[XmlArrayItem(ElementName = "Bookmark")]
public List<TextBookmark> TextList { get; set; } = new List<TextBookmark>();
[XmlArrayItem(ElementName = "Bookmark")]
public List<TableBookmark> TableList { get; set; } = new List<TableBookmark>();
[XmlArrayItem(ElementName = "Bookmark")]
public List<ChartBookmark> ChartList { get; set; } = new List<ChartBookmark>();
[XmlArrayItem(ElementName = "Bookmark")]
public List<ImageBookmark> ImageList { get; set; } = new List<ImageBookmark>();
}
public class TextBookmark
{
[XmlAttribute(AttributeName = "Name")]
public string Name { get; set; } = string.Empty;
[XmlAttribute(AttributeName = "UseText")]
public bool UseText { get; set; }
[XmlAttribute(AttributeName = "NewParagraph")]
public bool NewParagraph { get; set; }
[XmlElement(ElementName = "Text")]
public string Text { get; set; } = string.Empty;
[XmlElement(ElementName = "Sql")]
public string Sql { get; set; } = string.Empty;
}
public class TableBookmark
{
[XmlAttribute(AttributeName = "Name")]
public string Name { get; set; } = string.Empty;
[XmlElement(ElementName = "Sql")]
public string Sql { get; set; } = string.Empty;
}
public class ChartBookmark
{
[XmlAttribute(AttributeName = "Name")]
public string Name { get; set; } = string.Empty;
[XmlElement(ElementName = "Sql")]
public string Sql { get; set; } = string.Empty;
}
public class ImageBookmark
{
[XmlAttribute(AttributeName = "Name")]
public string Name { get; set; } = string.Empty;
[XmlElement(ElementName = "FilePath")]
public string FilePath { get; set; } = string.Empty;
[XmlElement(ElementName = "Width")]
public float Width { get; set; } = 440;
[XmlElement(ElementName = "Height")]
public float Height { get; set; } = 220;
}
生成报告类源码见:GenerateReport.cs
class GenerateReport
{
object oMissing = System.Reflection.Missing.Value;
Word._Application oWord;
Word._Document oDoc;
//通过模板创建文档
public void start(object filepath)
{
//Start Word and create a new document.
killWinWordProcess();
oWord = new Word.Application();
oWord.Visible = false;//测试看效果为true,后台执行false
oDoc = oWord.Documents.Add(ref filepath, ref oMissing, ref oMissing, ref oMissing);
}
/// <summary>
/// 在书签处插入值
/// </summary>
/// <param name="bookmark"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool InsertValue(string bookmark, string value)
{
object bkObj = bookmark;
if (oWord.ActiveDocument.Bookmarks.Exists(bookmark))
{
oWord.ActiveDocument.Bookmarks.get_Item(ref bkObj).Select();
oWord.Selection.TypeText(value);
return true;
}
return false;
}
// 杀掉winword.exe进程
public void killWinWordProcess()
{
System.Diagnostics.Process[] processes = System.Diagnostics.Process.GetProcessesByName("WINWORD");
foreach (System.Diagnostics.Process process in processes)
{
bool b = process.MainWindowTitle == "";
if (process.MainWindowTitle == "")
{
process.Kill();
}
}
}
//插入图片
public void InsertPicture(string bookmark, string picturePath, float width, float hight)
{
object miss = System.Reflection.Missing.Value;
object oStart = bookmark;
Object linkToFile = false; //图片是否为外部链接
Object saveWithDocument = true; //图片是否随文档一起保存
object range = oDoc.Bookmarks.get_Item(ref oStart).Range;//图片插入位置
oDoc.InlineShapes.AddPicture(picturePath, ref linkToFile, ref saveWithDocument, ref range);
oDoc.Application.ActiveDocument.InlineShapes[1].Width = width; //设置图片宽度
oDoc.Application.ActiveDocument.InlineShapes[1].Height = hight; //设置图片高度
}
//保存新文件
public void SaveDocument(string filePath)
{
object fileName = filePath;
object format = WdSaveFormat.wdFormatDocumentDefault;//保存格式
object miss = System.Reflection.Missing.Value;
oDoc.SaveAs(ref fileName, ref format, ref oMissing,
ref oMissing, ref oMissing);
//关闭wordDoc,wordApp对象
object SaveChanges = WdSaveOptions.wdSaveChanges;
object OriginalFormat = WdOriginalFormat.wdOriginalDocumentFormat;
object RouteDocument = false;
oDoc.Close(ref SaveChanges, ref OriginalFormat, ref RouteDocument);
oWord.Quit(ref SaveChanges, ref OriginalFormat, ref RouteDocument);
}
/// <summary>
/// 插入表格,bookmark书签
/// </summary>
/// <param name="bookmark"></param>
/// <param name="rows"></param>
/// <param name="columns"></param>
/// <param name="width"></param>
/// <returns></returns>
public Table InsertTable(string bookmark, int rows, int columns, float width)
{
object miss = System.Reflection.Missing.Value;
object oStart = bookmark;
Range range = oDoc.Bookmarks.get_Item(ref oStart).Range;//表格插入位置
Table newTable = oDoc.Tables.Add(range, rows, columns, ref miss, ref miss);
//设置表的格式
newTable.Borders.Enable = 1; //允许有边框,默认没有边框(为0时报错,1为实线边框,2、3为虚线边框,以后的数字没试过)
newTable.Borders.OutsideLineWidth = WdLineWidth.wdLineWidth050pt;//边框宽度
if (width != 0)
{
newTable.PreferredWidth = width;//表格宽度
}
newTable.AllowPageBreaks = false;
return newTable;
}
//给表格添加一行
public void AddRow(Microsoft.Office.Interop.Word.Table table)
{
object miss = System.Reflection.Missing.Value;
table.Rows.Add(ref miss);
}
//给表格中单元格插入元素,table所在表格,row行号,column列号,value插入的元素
public void InsertCell(Microsoft.Office.Interop.Word.Table table, int row, int column, string value)
{
table.Cell(row, column).Range.Text = value;
}
/// <summary>
/// 插入图表(Chart对象方式)
/// </summary>
/// <param name="bookMarkName"></param>
public void setChart(string bookMarkName,Dictionary<string,string> chartDic)
{
//获取内嵌图表
var inlineShape = oDoc.Bookmarks.Cast<Bookmark>().FirstOrDefault(o => o.Name == bookMarkName).Range.InlineShapes[1];
//Microsoft.Office.Interop.Word.Chart wdChart = oDoc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xlColumnClustered, ref oMissing).Chart;
Word.Chart wdChart = inlineShape.Chart;
Word.ChartData chartData = wdChart.ChartData;
Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;
dataWorkbook.Application.Visible = false;
Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1];
//设定范围
Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B"+chartDic.Count.ToString());
Excel.ListObject tbl1 = dataSheet.ListObjects[1];
tbl1.Resize(tRange);
int i = 0;
foreach(var item in chartDic)
{
i++;
((Excel.Range)dataSheet.Cells.get_Range("A" + (i + 1).ToString(), oMissing)).FormulaR1C1 = item.Key;
((Excel.Range)dataSheet.Cells.get_Range("B" + (i + 1).ToString(), oMissing)).FormulaR1C1 = item.Value;
}
//为图例赋值
//((Excel.Range)dataSheet.Cells.get_Range("A2", oMissing)).FormulaR1C1 = "Bikes";
//((Excel.Range)dataSheet.Cells.get_Range("A3", oMissing)).FormulaR1C1 = "Accessories";
//((Excel.Range)dataSheet.Cells.get_Range("A4", oMissing)).FormulaR1C1 = "Repairs";
//((Excel.Range)dataSheet.Cells.get_Range("A5", oMissing)).FormulaR1C1 = "Clothing";
//为图表赋值
//((Excel.Range)dataSheet.Cells.get_Range("B2", oMissing)).FormulaR1C1 = "100";
//可设置某一行数据不可见
//((Excel.Range)dataSheet.Cells.get_Range("B2", oMissing)).EntireRow.Hidden = true;
//((Excel.Range)dataSheet.Cells.get_Range("B3", oMissing)).FormulaR1C1 = "200";
//((Excel.Range)dataSheet.Cells.get_Range("B4", oMissing)).FormulaR1C1 = "900";
//((Excel.Range)dataSheet.Cells.get_Range("B5", oMissing)).FormulaR1C1 = "370";
//设置字体样式、大小、颜色
//wdChart.ChartTitle.Font.Italic = true;
//wdChart.ChartTitle.Font.Size = 18;
//wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();
//设置标题
//wdChart.ChartTitle.Text = "2007 Sales";
//设置图表样式
//wdChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//wdChart.ChartTitle.Format.Line.ForeColor.RGB = Color.Black.ToArgb();
//是否显示图表标签
//wdChart.ApplyDataLabels(Microsoft.Office.Interop.Word.XlDataLabelsType.xlDataLabelsShowLabel, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}
/// <summary>
/// 插入一段文字,text为文字内容
/// </summary>
/// <param name="bookmark"></param>
/// <param name="text"></param>
public void InsertText(string bookmark, string text)
{
object oStart = bookmark;
object range = oDoc.Bookmarks.get_Item(ref oStart).Range;
Paragraph wp = oDoc.Content.Paragraphs.Add(ref range);
wp.Format.SpaceBefore = 1;
wp.Range.Text = text;
wp.Format.SpaceAfter = 1;
wp.Range.InsertParagraphAfter();
oDoc.Paragraphs.Last.Range.Text = "\n";
}
}
生成的测试报告如下:
备注:其他生成word报告的资料可参考,
http://zhangbin647.blog.163.com/blog/static/5752413201152035750294/
http://www.360doc.com/content/11/1031/16/665991_160573635.shtml
http://blog.csdn.net/wudi626/article/details/2337857