在开发中经常需要将grid中的数据导出成Excel或Word格式。下面我们来说说如何实现它:
1 先用一个方法,筛选出有效的数据。
因为grid中可能会有隐藏列,而这些列中的数据我们一般都是不需要的,所以我们先将grid中的数据进行过滤,保存在一个DataTable中。
private DataTable exporeDataToTable(DataGridView dataGridView) { //将datagridview中的数据导入到表中 DataTable tempTable = new DataTable("tempTable"); //定义一个模板表,专门用来获取列名 DataTable modelTable = new DataTable("ModelTable"); //创建列 for (int column = 0; column < dataGridView.Columns.Count; column++) { //可见的列才显示出来 if (dataGridView.Columns[column].Visible == true) { DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string)); tempTable.Columns.Add(tempColumn); DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string)); modelTable.Columns.Add(modelColumn); } } //添加datagridview中行的数据到表 for (int row = 0; row < dataGridView.Rows.Count; row++) { if (dataGridView.Rows[row].Visible == false) { continue; } DataRow tempRow = tempTable.NewRow(); for (int i = 0; i < tempTable.Columns.Count; i++) { tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value; } tempTable.Rows.Add(tempRow); } return tempTable; }
2 导出为Excel。
导出Excel会用到Microsoft.Office.Interop.Excel.dll,所以必须先在项目中引用这个DLL。
private void OutputAsExcelFile(DataGridView dataGridView) { //将datagridView中的数据导出到一张表中 DataTable tempTable = this.exporeDataToTable(dataGridView); //导出信息到Excel表 Microsoft.Office.Interop.Excel.ApplicationClass myExcel; Microsoft.Office.Interop.Excel.Workbooks myWorkBooks; Microsoft.Office.Interop.Excel.Workbook myWorkBook; Microsoft.Office.Interop.Excel.Worksheet myWorkSheet; char myColumns; Microsoft.Office.Interop.Excel.Range myRange; object[,] myData = new object[500, 35]; int i, j;//j代表行,i代表列 myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //显示EXCEL myExcel.Visible = true; if (myExcel == null) { MessageBox.Show("本地Excel程序无法启动!请检查您的Microsoft Office正确安装并能正常使用", "提示"); return; } myWorkBooks = myExcel.Workbooks; myWorkBook = myWorkBooks.Add(System.Reflection.Missing.Value); myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets[1]; myColumns = (char)(tempTable.Columns.Count + 64);//设置列 myRange = myWorkSheet.get_Range("A4", myColumns.ToString() + "5");//设置列宽 int count = 0; //设置列名 foreach (DataColumn myNewColumn in tempTable.Columns) { myData[0, count] = myNewColumn.ColumnName; count = count + 1; } //输出datagridview中的数据记录并放在一个二维数组中 j = 1; foreach (DataRow myRow in tempTable.Rows)//循环行 { for (i = 0; i < tempTable.Columns.Count; i++)//循环列 { myData[j, i] = myRow[i].ToString(); } j++; } //将二维数组中的数据写到Excel中 myRange = myRange.get_Resize(tempTable.Rows.Count + 1, tempTable.Columns.Count);//创建列和行 myRange.Value2 = myData; myRange.EntireColumn.AutoFit(); }
3 导出为Word格式
同样的,导出为Word要添加对Microsoft.Office.Interop.Word.Dll的引用。
private void OutPutAsWordFile(DataGridView dataGridView) { //转换后的表 DataTable table = exporeDataToTable(this.dataGridView1); Microsoft.Office.Interop.Word.ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); Microsoft.Office.Interop.Word.Document document; Microsoft.Office.Interop.Word.Table wordTable; Microsoft.Office.Interop.Word.Selection wordSelection; object wordObj = System.Reflection.Missing.Value; document = wordApp.Documents.Add(ref wordObj, ref wordObj, ref wordObj, ref wordObj); wordSelection = wordApp.Selection; //显示word文档 wordApp.Visible = true; if (wordApp == null) { MessageBox.Show("本地Word程序无法启动!请检查您的Microsoft Office正确安装并能正常使用", "提示"); return; } document.Select(); wordTable = document.Tables.Add(wordSelection.Range, dataGridView.Rows.Count+1, dataGridView.Columns.Count, ref wordObj, ref wordObj); //设置列宽 wordTable.Columns.SetWidth(50.0F, Microsoft.Office.Interop.Word.WdRulerStyle.wdAdjustSameWidth); //标题数据 for (int i = 0; i < table.Columns.Count; i++) { wordTable.Cell(1, i + 1).Range.InsertAfter(table.Columns[i].ColumnName); } //输出表中数据 for (int i = 0; i <= table.Rows.Count - 1; i++) { for (int j = 0; j < table.Columns.Count; j++) { if (table.Rows[i][j] != null) { wordTable.Cell(i + 2, j + 1).Range.InsertAfter(table.Rows[i][j].ToString()); } } }
//画表格的行
wordTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
//画表格的列
wordTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
}
其他相关文章介绍:
一:
http://wwwblogs/macroxu-1982/archive/2006/12/26/573856.html
二:
http://wenku.baidu/view/3963372c7375a417866f8f52.html
三:
http://wwwblogs/liguanghui/archive/2011/11/14/2248130.html
四:
http://wenku.baidu/view/80ec0a6c1eb91a37f1115cab.html
五:
http://wwwblogs/knowledgesea/archive/2013/05/24/3095376.html
还有几篇很好的讲生成word的牛人的随笔:
c# 生成 word_大杂烩
http://hi.baidu/metaza/blog/item/b277ccc4183b73ab8226ac8b.html
对word的操作
http://wwwblogs/niuyy/archive/2008/06/25/1229755.html
[分享]一段导出到word模版的代码
http://wwwblogs/goody9807/archive/2005/08/25/222526.html
再谈word2003编程
http://wwwblogs/Andmm/archive/2008/06/18/1224422.html
最近一直在做C#操作office方面的工作!总结一下!Word(二)
http://wwwblogs/wngwz/archive/2004/08/19/34678.html
C#也能动态生成Word文档并填充数据
http://wwwblogs/qyfan82/archive/2007/09/14/893293.html
使用 Open XML SDK 提高处理大量数据时的 Word 自动化性能
http://msdn.microsoft/zh-cn/library/ff191178.aspx
十一、上下左右移动光标位
private void moveLeft()
{
object moveUnit = Microsoft.Office.Interop.Word.WdUnits.wdWord;
object moveCount = 1;
object moveExtend = Microsoft.Office.Interop.Word.WdMovementType.wdExtend;
thisDocument.Application.Selection.MoveLeft(ref moveUnit, ref moveCount, ref MissingValue);
}
private void moveRight()
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
object dummy=System.Reflection.Missing.Value;
object count=1;
object Unit=Word.WdUnits.wdCharacter;
selection.MoveRight(ref Unit,ref count,ref dummy);
}
十二、取得当前光标位
public void GetCursor()
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
object a=selection.get_Information(Word.WdInformation.wdFirstCharacterLineNumber);
object b=selection.get_Information(Word.WdInformation.wdFirstCharacterColumnNumber);
object c=selection.get_Information(Word.WdInformation.wdActiveEndAdjustedPageNumber);
MessageBox.Show(a.ToString()+”行,”+b.ToString()+”列,”+c.ToString()+”页”);
}
十三、定位到指定行或相对行
/// <summary>
/// 定位到指定行
/// </summary>
/// <param name=”lineNum”>行号</param>
private void gotoAbsolutLine(int lineNum)
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
object dummy=System.Reflection.Missing.Value;
object what=Word.WdGoToItem.wdGoToLine;
object which=Word.WdGoToDirection.wdGoToFirst;
object count=lineNum;
selection.GoTo(ref what,ref which,ref count,ref dummy);
}
/// <summary>
/// 定位到相对行,例如+4
/// </summary>
/// <param name=”lineNum”>行数</param>
private void gotoOppositeLine(int lineNum)
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
object dummy=System.Reflection.Missing.Value;
object what=Word.WdGoToItem.wdGoToLine;
object which;
if(lineNum<0)
which=Word.WdGoToDirection.wdGoToPrevious;
else
which=Word.WdGoToDirection.wdGoToNext;
object count=Math.Abs(lineNum);
selection.GoTo(ref what,ref which,ref count,ref dummy);
}
十四、定位到文档最后一行
private void gotoLastLine(Document thisDocument)
{
object dummy = System.Reflection.Missing.Value;
object what = WdGoToItem.wdGoToLine;
object which = WdGoToDirection.wdGoToLast;
object count = 99999999;
thisDocument.Application.Selection.GoTo(ref what, ref which, ref count, ref dummy);
}
十五、定位到第一个字符
private void gotoFirstCharacter()
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
int oldLine=0;
gotoAbsolutLine(1);
object a=selection.get_Information(Word.WdInformation.wdFirstCharacterLineNumber);//得到当前行号
while(oldLine!=int.Parse(a.ToString()))//一直按右键,直到光标不再往下了为止
{
oldLine++;
moveRight();
a=selection.get_Information(Word.WdInformation.wdFirstCharacterLineNumber);
}
gotoAbsolutLine(int.Parse(a.ToString()));
}
十六、定位到最后一个字符
public void gotoLastCharacter()
{
if(selection==null||selection!=document.Application.Selection)
selection=document.Application.Selection;
gotoLastLine();
object dummy=System.Reflection.Missing.Value;
object count=99999999;
object Unit=Word.WdUnits.wdCharacter;
selection.MoveRight(ref Unit,ref count,ref dummy);
}
二十一、 取得行、列、页信息
public string WordGetRCP()
{
selection=document.Application.Selection;//wd.Selection;
object a=selection.get_Information(Word.WdInformation.wdFirstCharacterLineNumber);
object b=selection.get_Information(Word.WdInformation.wdFirstCharacterColumnNumber);
object c=selection.get_Information(Word.WdInformation.wdActiveEndAdjustedPageNumber);
return a.ToString()+”,”+b.ToString()+”,”+c.ToString();
}
更多推荐
导出数据库查询数据至Word和Excel格式
发布评论