2019-05-08 18:34:39 weixin_44548643 阅读数 3765
  • C#Winform窗体与界面设计

    本课程是针对C#Winform开发人员的基础性课程,详尽的介绍C#Winform窗体与界面的设计,通过提供实例,让C#Winform开发人员掌握相关程序设计技巧,为后面学习完整的实战课程打下较好的基础。

    6047 人正在学习 去看看 武占文

C#NPOI导出Excel详解

开发工具与技术:工具:Visual Studio/C#.Net,技术:C#
作者:刘胜
撰写时间:2019-05-08


NPOI简介

NOP是一个开源的C#读写Excel、WORD等微软OLE2组件的项目,使用NPOI你就可以在没有安装Office或者相应的机器上对WORD/EXCEL文件档进行读写。

实例
首先创建HSSWorkbook的对象用来调用NPOI文件,如果你导入的数据时没有调用ICellStyle类的对象设置的样式话,那么出来的数据在Excel是不加任何的样式。也就是说数据过多的话,数据会挤在一排,而且超出会隐藏。所以要设置ICellStyle的属性才行。

//二:代码创建一个Excel表格(这里称为工作簿)
//创建Excel文件的对象 工作簿(调用NPOI文件)
HSSFWorkbook excelBook = new HSSFWorkbook();
ICellStyle style = excelBook.CreateCellStyle();

关于ICellStyle怎么调用下面有详细的,先创建Excel工作表Sheet,再创建Excel表格的第一行是头部的标题。

//创建Excel工作表 Sheet=故障码信息
ISheet sheet1 = excelBook.CreateSheet("某某");
IRow row1 = sheet1.CreateRow(0);
//给标题的每一个单元格赋值
row1.CreateCell(0).SetCellValue("故障码DTC");//0

如果要添加每行数据,可以用for()循环先填充Sheet1对象的CreateRow()行索引,给每一个单元格添加值。

//查询数据
var listFaultInfo = listFaultCodePush(FaultCodeID, DTC);
//添加数据行:将表格数据逐步写入sheet1各个行中(也就是给每一个单元格赋值)
            for (int i = 0; i < listExaminee.Count; i++)
            {
                //sheet1.CreateRow(i).
                //创建行
                IRow rowTemp = sheet1.CreateRow(i + 1);
                rowTemp.Height = 62 * 20;
                //故障码DTC
                rowTemp.CreateCell(0).SetCellValue(listFaultInfo[i].DTC);
            }

这是关于设置NPOI导出Excel的样式设置。
在这里插入图片描述
还可以设置字体的颜色是通过IFont去设置

IFont font = excelBook.CreateFont();
font.Color = IndexedColors.RED.Index;
style1.SetFont(font);

最后输出文件名称,将工作薄写入文件流

//输出的文件名称
            string fileName = "故障码信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            //把Excel转为流,输出
            //创建文件流
            System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
            //将工作薄写入文件流
            excelBook.Write(bookStream);

            //输出之前调用Seek(偏移量,游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, System.IO.SeekOrigin.Begin);
            //Stream对象,文件类型,文件名称
            return File(bookStream, "application/vnd.ms-excel", fileName);

下面我用一个实例来举例,见下面代码

#region NPOI导出到Excel 
        //NPOI导出到Excel(故障码)
        public ActionResult ExportToExcel(string DTC)
        {
            int FaultCodeID = 0;//故障码ID

            //查询数据
            var listFaultInfo = listFaultCodePush(FaultCodeID, DTC);
            List<PlatformClass> listExaminee = listFaultInfo;
            //二:代码创建一个Excel表格(这里称为工作簿)
            //创建Excel文件的对象 工作簿(调用NPOI文件)
            HSSFWorkbook excelBook = new HSSFWorkbook();
            ICellStyle style1 = excelBook.CreateCellStyle();//声明style1对象,设置Excel表格的样式
            ICellStyle style2 = excelBook.CreateCellStyle();
            ICellStyle style3 = excelBook.CreateCellStyle();
            IFont font = excelBook.CreateFont();
            font.Color = IndexedColors.RED.Index;
            style3.SetFont(font);
            style1.Alignment = HorizontalAlignment.JUSTIFY;//两端自动对齐(自动换行)
            style1.VerticalAlignment = VerticalAlignment.CENTER;
            style2.Alignment = HorizontalAlignment.CENTER;
            style2.VerticalAlignment = VerticalAlignment.CENTER;
            style3.Alignment = HorizontalAlignment.CENTER;
            style3.VerticalAlignment = VerticalAlignment.CENTER;
            //创建Excel工作表 Sheet=故障码信息
            ISheet sheet1 = excelBook.CreateSheet("故障码信息");
            //给Sheet(故障码信息)添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            //给标题的每一个单元格赋值
            row1.CreateCell(0).SetCellValue("故障码DTC");//0
            row1.CreateCell(1).SetCellValue("故障码(hex)");//1
            row1.CreateCell(2).SetCellValue("故障码英文描述");//2
            row1.CreateCell(3).SetCellValue("故障码中文描述");//3
            row1.CreateCell(4).SetCellValue("故障码运行条件");//4
            row1.CreateCell(5).SetCellValue("故障码设置条件");//5
            row1.CreateCell(6).SetCellValue("故障码设置时发生的操作");//6
            row1.CreateCell(7).SetCellValue("故障恢复条件");//7
            row1.CreateCell(8).SetCellValue("激近故障灯原则");//8
            row1.CreateCell(9).SetCellValue("熄灭故障灯原则");//9
            row1.CreateCell(10).SetCellValue("清除故障码条件");//10
            row1.GetCell(0).CellStyle = style2;//初始化设置样式
            row1.GetCell(1).CellStyle = style2;//初始化设置样式
            row1.GetCell(2).CellStyle = style2;//初始化设置样式
            row1.GetCell(3).CellStyle = style2;//初始化设置样式
            row1.GetCell(4).CellStyle = style2;//初始化设置样式
            row1.GetCell(5).CellStyle = style2;//初始化设置样式
            row1.GetCell(6).CellStyle = style2;//初始化设置样式
            row1.GetCell(7).CellStyle = style2;//初始化设置样式
            row1.GetCell(8).CellStyle = style2;//初始化设置样式
            row1.GetCell(9).CellStyle = style2;//初始化设置样式
            row1.GetCell(10).CellStyle = style2;//初始化设置样式
            sheet1.SetColumnWidth(0, 10 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(0, 10 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(0, 10 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(0, 10 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(1, 10 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(0, 10 * 256);//初始化设置样式
            sheet1.SetColumnWidth(2, 17 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(3, 30 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(4, 34 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(5, 20 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(6, 26 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(7, 29 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(8, 14 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(9, 14 * 256);//初始化设置宽度
            sheet1.SetColumnWidth(10, 99 * 256);//初始化设置宽度
            //添加数据行:将表格数据逐步写入sheet1各个行中(也就是给每一个单元格赋值)
            for (int i = 0; i < listExaminee.Count; i++)
            {
                //sheet1.CreateRow(i).
                //创建行
                IRow rowTemp = sheet1.CreateRow(i + 1);
                rowTemp.Height = 62 * 20;
                //故障码DTC
                rowTemp.CreateCell(0).SetCellValue(listFaultInfo[i].DTC);
                //故障码(hex)
                rowTemp.CreateCell(1).SetCellValue(listFaultInfo[i].HEX);
                //故障码英文描述
                rowTemp.CreateCell(2).SetCellValue(listFaultInfo[i].EfaultCode);
                //故障码中文描述
                rowTemp.CreateCell(3).SetCellValue(listFaultInfo[i].CfaultCode);
                ///故障码运行条件
                rowTemp.CreateCell(4).SetCellValue(listFaultInfo[i].OperatingCondition.Replace("<br>", " "));
                //故障码设置条件
                rowTemp.CreateCell(5).SetCellValue(listFaultInfo[i].SetCriteria.Replace("<br>", " "));
                //故障码设置时发生的操作
                rowTemp.CreateCell(6).SetCellValue(listFaultInfo[i].SetingOperation);
                //故障恢复条件
                rowTemp.CreateCell(7).SetCellValue(listFaultInfo[i].RestoreConditions);
                //激近故障灯原则
                rowTemp.CreateCell(8).SetCellValue(listFaultInfo[i].ActivateRule);
                //熄灭故障灯原则
                rowTemp.CreateCell(9).SetCellValue(listFaultInfo[i].OutRule);
                //清除故障码条件
                rowTemp.CreateCell(10).SetCellValue(listFaultInfo[i].CleanCondition.Replace("<br>"," "));
                rowTemp.GetCell(0).CellStyle = style3;
                rowTemp.GetCell(1).CellStyle = style3;
                rowTemp.GetCell(2).CellStyle = style2;
                rowTemp.GetCell(3).CellStyle = style2;
                rowTemp.GetCell(4).CellStyle = style1;
                rowTemp.GetCell(5).CellStyle = style1;
                rowTemp.GetCell(6).CellStyle = style1;
                rowTemp.GetCell(7).CellStyle = style1;
                rowTemp.GetCell(8).CellStyle = style2;
                rowTemp.GetCell(9).CellStyle = style2;
                rowTemp.GetCell(10).CellStyle = style1;
            }
            //输出的文件名称
            string fileName = "故障码信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            //把Excel转为流,输出
            //创建文件流
            System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
            //将工作薄写入文件流
            excelBook.Write(bookStream);

            //输出之前调用Seek(偏移量,游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, System.IO.SeekOrigin.Begin);
            //Stream对象,文件类型,文件名称
            return File(bookStream, "application/vnd.ms-excel", fileName);
        }
        #endregion

上面的代码运行的结果如下所示。
在这里插入图片描述

2013-07-29 13:58:11 fq1357762342 阅读数 568
  • C#Winform窗体与界面设计

    本课程是针对C#Winform开发人员的基础性课程,详尽的介绍C#Winform窗体与界面的设计,通过提供实例,让C#Winform开发人员掌握相关程序设计技巧,为后面学习完整的实战课程打下较好的基础。

    6047 人正在学习 去看看 武占文

环境: VS2010  office2010;

首先:新建一个项目,添加引用:Microsoft Word 1.4 Object Library 

其次添加命名空间: 1. using Word = Microsoft.Office.Interop.Word;      2. using System.Reflection;

然后就是代码了(我随便加了一个按钮,然后它的事件代码如下):

 private void button1_Click(object sender, EventArgs e)
        {
            object oMissing = System.Reflection.Missing.Value;
            object oEndOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */


            //Start Word and create a new document.
            Word._Application oWord;
            Word._Document oDoc;
            oWord = new Word.Application();
            oWord.Visible = true;
            oDoc = oWord.Documents.Add(ref oMissing, ref oMissing,ref oMissing, ref oMissing);


            //Insert a paragraph at the beginning of the document.
            Word.Paragraph oPara1;
            oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing);


            oPara1.Range.Text = "Heading 1";
            oPara1.Range.Font.Bold = 1;
            oPara1.Format.SpaceAfter = 24;    //24 pt spacing after paragraph.
            oPara1.Range.InsertParagraphAfter();


            //Insert a paragraph at the end of the document.
            Word.Paragraph oPara2;
            object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara2 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara2.Range.Text = "Heading 2";
            oPara2.Format.SpaceAfter = 6;
            oPara2.Range.InsertParagraphAfter();


            //Insert another paragraph.
            Word.Paragraph oPara3;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:";
            oPara3.Range.Font.Bold = 0;
            oPara3.Format.SpaceAfter = 24;
            oPara3.Range.InsertParagraphAfter();


            //Insert a 3 x 5 table, fill it with data, and make the first row
            //bold and italic.
            Word.Table oTable;
            Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oTable = oDoc.Tables.Add(wrdRng, 3, 5, ref oMissing, ref oMissing);
            oTable.Range.ParagraphFormat.SpaceAfter = 6;
            int r, c;
            string strText;
            for (r = 1; r <= 3; r++)
                for (c = 1; c <= 5; c++)
                {
                    strText = "r" + r + "c" + c;
                    oTable.Cell(r, c).Range.Text = strText;
                }
            oTable.Rows[1].Range.Font.Bold = 1;
            oTable.Rows[1].Range.Font.Italic = 1;


            //Add some text after the table.
            Word.Paragraph oPara4;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara4 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara4.Range.InsertParagraphBefore();
            oPara4.Range.Text = "And here's another table:";
            oPara4.Format.SpaceAfter = 24;
            oPara4.Range.InsertParagraphAfter();


            //Insert a 5 x 2 table, fill it with data, and change the column widths.
            wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oTable = oDoc.Tables.Add(wrdRng, 5, 2, ref oMissing, ref oMissing);
            oTable.Range.ParagraphFormat.SpaceAfter = 6;
            for (r = 1; r <= 5; r++)
                for (c = 1; c <= 2; c++)
                {
                    strText = "r" + r + "c" + c;
                    oTable.Cell(r, c).Range.Text = strText;
                }
            oTable.Columns[1].Width = oWord.InchesToPoints(2); //Change width of columns 1 & 2
            oTable.Columns[2].Width = oWord.InchesToPoints(3);




            //Keep inserting text. When you get to 7 inches from top of the
            //document, insert a hard page break.
            object oPos;
            double dPos = oWord.InchesToPoints(7);
            oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertParagraphAfter();
            do
            {
                wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                wrdRng.ParagraphFormat.SpaceAfter = 6;
                wrdRng.InsertAfter("A line of text");
                wrdRng.InsertParagraphAfter();
                oPos = wrdRng.get_Information(Word.WdInformation.wdVerticalPositionRelativeToPage);
            }
            while (dPos >= Convert.ToDouble(oPos));
            object oCollapseEnd = Word.WdCollapseDirection.wdCollapseEnd;
            object oPageBreak = Word.WdBreakType.wdPageBreak;
            wrdRng.Collapse(ref oCollapseEnd);
            wrdRng.InsertBreak(ref oPageBreak);
            wrdRng.Collapse(ref oCollapseEnd);
            wrdRng.InsertAfter("We're now on page 2. Here's my chart:");
            wrdRng.InsertParagraphAfter();


            //Insert a chart.插入表格
            Word.InlineShape oShape;
            object oClassType = "MSGraph.Chart.8";
            wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oShape = wrdRng.InlineShapes.AddOLEObject(ref oClassType, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);


            //Demonstrate use of late bound oChart and oChartApp objects to
            //manipulate the chart object with MSGraph.
            object oChart;
            object oChartApp;
            oChart = oShape.OLEFormat.Object;
            oChartApp = oChart.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, oChart, null);


            //Change the chart type to Line.
            object[] Parameters = new Object[1];
            Parameters[0] = 4; //xlLine = 4
            oChart.GetType().InvokeMember("ChartType", BindingFlags.SetProperty,null, oChart, Parameters);


            //Update the chart image and quit MSGraph.
            oChartApp.GetType().InvokeMember("Update",BindingFlags.InvokeMethod, null, oChartApp, null);
            oChartApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oChartApp, null);


            //... If desired, you can proceed from here using the Microsoft Graph 
            //Object model on the oChart and oChartApp objects to make additional
            //changes to the chart.


            //Set the width of the chart.
            oShape.Width = oWord.InchesToPoints(6.25f);
            oShape.Height = oWord.InchesToPoints(3.57f);


            //Add text after the chart.
            wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            wrdRng.InsertParagraphAfter();
            wrdRng.InsertAfter("THE END.");


            //Close this form.
            this.Close();

        }

在本机测试通过;产生一个Word文档,



2010-02-06 21:57:00 gxj022 阅读数 1874
  • C#Winform窗体与界面设计

    本课程是针对C#Winform开发人员的基础性课程,详尽的介绍C#Winform窗体与界面的设计,通过提供实例,让C#Winform开发人员掌握相关程序设计技巧,为后面学习完整的实战课程打下较好的基础。

    6047 人正在学习 去看看 武占文

C# winform 导出导入Excel/Doc 完整实例教程 使用Aspose.Cells.dll——第二篇


http://hi.baidu.com/%BA%CE%B3%B1/blog/item/6e6eae2f56ed70331e308966.html

 

Word导出

            //设置文件类型

            // saveFileDialog为一个对话框控件

            //如果没有人工具栏中拉,

//可以:SaveFileDialog saveFileDialog1=new SaveFileDialog();

            saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";

            saveFileDialog1.FilterIndex = 1;

            saveFileDialog1.RestoreDirectory = true;

            saveFileDialog1.CreatePrompt = true;

            saveFileDialog1.Title = "导出文件保存路径";

            //saveFileDialog1.ShowDialog();

            //string strName = saveFileDialog1.FileName;

 

            //设置默认文件类型显示顺序

            //saveFileDialog1.FilterIndex = 2;  

 

            //保存对话框是否记忆上次打开的目录

            saveFileDialog1.RestoreDirectory = true;

 

            //点了保存按钮进入

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)

            {

                //获得文件路径

                string localFilePath = saveFileDialog1.FileName.ToString();

 

                //获取文件名,不带路径

                string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("//") + 1);

 

                //获取文件路径,不带文件名

                string FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("//"));

 

                //给文件名前加上时间

                string newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;

 

                //在文件名里加字符

                //saveFileDialog1.FileName.Insert(1,"dameng");  

                saveFileDialog1.FileName = FilePath + "//" + newFileName;

                System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();//输出文件

                StreamWriter writer = new StreamWriter(fs);

                writer.Write("tttt");//这里就是你要导出到word的内容,内容是你什么你自已DIY

                writer.Flush();

                writer.Close();

                fs.Close();

}

导出datatableexcel

DataTable dt = null;

            if (ds_all.Tables[0] != null)

            {

                dt = ds_all.Tables[0];

            }

            else {

                MessageBox.Show("没有数据记录", "*^_^* 温馨提示信息", MessageBoxButtons.OK);

                return;

            }

//上面只是取datatable,你自己diy

            AsposeExcel tt = new AsposeExcel(saveFileDialog1.FileName, "");//不用模板, saveFileDialog1是什么?上面已经说过

            bool OK_NO = tt.DatatableToExcel(dt);

            if (OK_NO)

            {

                MessageBox.Show("导出成功", "*^_^* 温馨提示信息", MessageBoxButtons.OK);

 

            }

            else

            {

         }

 

下一篇,说导入。

2014-12-07 20:41:13 starhuhu 阅读数 449
  • C#Winform窗体与界面设计

    本课程是针对C#Winform开发人员的基础性课程,详尽的介绍C#Winform窗体与界面的设计,通过提供实例,让C#Winform开发人员掌握相关程序设计技巧,为后面学习完整的实战课程打下较好的基础。

    6047 人正在学习 去看看 武占文

        using Microsoft.Office.Interop.Word;
using Microsoft.Office.Interop.Excel;


using Word = Microsoft.Office.Interop.Word;

private void buttonX1_Click(object sender, EventArgs e)

        {
            object Nothing = System.Reflection.Missing.Value;
            //创建word文档
            Word.Application wordApp = new Word.ApplicationClass();
            Word.Document wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
           
            //设置文档宽度
            wordApp.Selection.PageSetup.LeftMargin = wordApp.CentimetersToPoints(float.Parse("2"));
            wordApp.ActiveWindow.ActivePane.HorizontalPercentScrolled = 11;
            wordApp.Selection.PageSetup.RightMargin = wordApp.CentimetersToPoints(float.Parse("2"));
            Object start = Type.Missing;
            Object end = Type.Missing;
            wordDoc.Range(ref start, ref end).Font.Name = "Verdana";    //设置字体
            wordDoc.Range(ref start, ref end).Font.Size = 10;   //设置字体大小
            wordDoc.Range(ref start, ref end).ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter; //设置字体局中


            //文档中创建表格
            Word.Table newTable = wordDoc.Tables.Add(wordApp.Selection.Range, 15, 5, ref Nothing, ref Nothing);
            //设置表格样式
            newTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble; //设置外边样式
            newTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; //设置内边样式
            newTable.Columns[1].Width = 80f;   // 设置表格宽度
            newTable.Columns[2].Width = 90f;
            newTable.Columns[3].Width = 80f;
            newTable.Columns[4].Width = 90f;
            newTable.Columns[5].Width = 90f;
            newTable.Rows.Height = 20;       // 设置表格高度
            for (int i = 0; i < 2/*MyDs.Tables[0].Rows.Count*/; i++)
            {
                //填充表格内容
                newTable.Cell(1, 1).Range.Text = "职工编号:";
                newTable.Cell(1, 2).Range.Text = "职工编号:";
                newTable.Cell(1, 3).Range.Text = "职工姓名:";
                newTable.Cell(1, 4).Range.Text = "职工姓名:";
                newTable.Cell(2, 1).Range.Text = "民族类别:";//cbox民族
                newTable.Cell(2, 2).Range.Text = "民族类别:";
                newTable.Cell(2, 3).Range.Text = "出生日期:";
                newTable.Cell(2, 4).Range.Text = "出生日期:";
                newTable.Cell(3, 1).Range.Text = "年龄:";
                newTable.Cell(3, 2).Range.Text = "年龄:";
                newTable.Cell(3, 3).Range.Text = "文化程度:";
                newTable.Cell(3, 4).Range.Text = "文化程度:";
                newTable.Cell(4, 1).Range.Text = "婚姻:";
                newTable.Cell(4, 2).Range.Text = "测试";
                newTable.Cell(4, 3).Range.Text = "性别:";
                newTable.Cell(4, 4).Range.Text = "测试";
                newTable.Cell(5, 1).Range.Text = "政治面貌:";
                newTable.Cell(5, 2).Range.Text = "测试";
                newTable.Cell(5, 3).Range.Text = "工作时间:";
                newTable.Cell(5, 4).Range.Text = "测试";
                newTable.Cell(6, 1).Range.Text = "籍贯:";
                newTable.Cell(6, 2).Range.Text = "测试";
                newTable.Cell(6, 3).Range.Text = "测试";
                newTable.Cell(6, 4).Range.Text = "身份证号:";
                newTable.Cell(6, 5).Range.Text = "测试";
                newTable.Cell(7, 1).Range.Text = "职称类别:";
                newTable.Cell(7, 2).Range.Text = "测试";
                newTable.Cell(7, 3).Range.Text = "所在部门:";
                newTable.Cell(7, 4).Range.Text = "测试";
                newTable.Cell(8, 1).Range.Text = "月工资:";
                newTable.Cell(8, 2).Range.Text = "测试";
                newTable.Cell(8, 3).Range.Text = "银行账号:";
                newTable.Cell(8, 4).Range.Text = "测试";
                newTable.Cell(9, 1).Range.Text = "合同起始日期:";
                newTable.Cell(9, 2).Range.Text = "测试";
                newTable.Cell(9, 3).Range.Text = "合同结束日期:";
                newTable.Cell(9, 4).Range.Text = "测试";
                newTable.Cell(10, 1).Range.Text = "合同年限:";
                newTable.Cell(10, 2).Range.Text = "测试";
                newTable.Cell(10, 3).Range.Text = "工龄:";
                newTable.Cell(10, 4).Range.Text = "测试";
                newTable.Cell(11, 1).Range.Text = "手机:";
                newTable.Cell(11, 2).Range.Text = "测试";
                newTable.Cell(11, 3).Range.Text = "毕业时间:";
                newTable.Cell(11, 4).Range.Text = "测试";
                newTable.Cell(12, 1).Range.Text = "电话:";
                newTable.Cell(12, 2).Range.Text = "测试";
                newTable.Cell(13, 1).Range.Text = "毕业学院:";
                newTable.Cell(13, 2).Range.Text = "测试";
                newTable.Cell(14, 1).Range.Text = "主修专业:";
                newTable.Cell(14, 2).Range.Text = "测试";
                newTable.Cell(15, 1).Range.Text = "家庭住址:";
                newTable.Cell(15, 2).Range.Text = "测试";


                try
                {
                    //合并单元格
                    newTable.Cell(1, 5).Merge(newTable.Cell(5, 5));
                    newTable.Cell(7, 4).Merge(newTable.Cell(7, 5));
                    newTable.Cell(8, 4).Merge(newTable.Cell(8, 5));
                    newTable.Cell(9, 4).Merge(newTable.Cell(9, 5));
                    newTable.Cell(10, 4).Merge(newTable.Cell(10, 5));
                    newTable.Cell(11, 2).Merge(newTable.Cell(11, 3));
                    newTable.Cell(12, 2).Merge(newTable.Cell(12, 5));
                    newTable.Cell(13, 2).Merge(newTable.Cell(13, 5));
                    newTable.Cell(14, 2).Merge(newTable.Cell(14, 5));
                    newTable.Cell(15, 2).Merge(newTable.Cell(15, 5));
                    wordApp.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//垂直居中
                    wordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
                    wordApp.Visible = true;   // 显示word文档
                }
                catch
                {
                }
            }
2007-04-02 08:40:00 juejue1984 阅读数 1663
  • C#Winform窗体与界面设计

    本课程是针对C#Winform开发人员的基础性课程,详尽的介绍C#Winform窗体与界面的设计,通过提供实例,让C#Winform开发人员掌握相关程序设计技巧,为后面学习完整的实战课程打下较好的基础。

    6047 人正在学习 去看看 武占文

      看到网上不少介绍有关数据导出的方法,但是总显得凌乱、琐碎,用起来也很不爽。索性一起收集起来,整合到一块。 下面介绍一个整合导出到word、excel、text的导出控件,具体做法是用一个扩展的按钮固化上一个菜单及其事件,程序员需要做的仅仅是赋予此控件一个DataTable类型的属性实例。

    以下初始资源均来自网上,本人代为整合和优化,如有错误,欢迎来信批评。

  

 

using System;

using System.Windows.Forms;

using System.ComponentModel;

using System.Data;

using System.Drawing;

namespace JueJue2007

{

       /**

        * Author: Liu Juejue

        * Date:     2007-3-9

        * Description: Export the data to word or excel or txt file.

        * Copyleft: Liu Juejue

        **/

       /// <summary>

       /// 导出数据到WordExcelText

       /// </summary>

       public class ExporterButton : MyXPButton

       {     

              public ExporterButton()

              {

                     this.InitializeComponent();

              }

 

              #region 字段

              //菜单

              private ContextMenu contextMenu1;

              private MenuItem menuItem2;

              private MenuItem menuItem3;

              private MenuItem menuItem4;

              private YuanFeiSoftware.YuanFeiXPControls.DNetMenu dNetMenu1;

 

              string category = string.Empty;

 

              /// <summary>

              /// 数据源

              /// </summary>

              private DataTable dataSource = null;

 

              /// <summary>

              /// 存放路径

              /// </summary>

              private string path = string.Empty;

 

              /// <summary>

              /// 标题

              /// </summary>

              private string caption = "朗远软件";

 

              #endregion

 

              #region 属性

 

              /// <summary>

              /// 存放路径

              /// </summary>

              public string Path

              {

                     get

                     {

                            return this.path;

                     }

                     set

                     {

                            path = value;

                     }

              }

 

              /// <summary>

              /// 要导出的数据

              /// </summary>

              public DataTable DataSource

              {

                     get

                     {

                            return this.dataSource;

                     }

                     set

                     {

                            this.dataSource = value;

                     }

              }

 

              [ Category( "导出项" ), Description( "指定导出内容的标题" ), DefaultValue( "朗远软件" ) ]

              public string Caption

              {

                     get

                     {

                            return this.caption;

                     }

                     set

                     {

                            this.caption = value;

                     }

              }

 

              #endregion

 

              #region 实例化控件变量

              private void InitializeComponent()

              {

                     this.contextMenu1 = new System.Windows.Forms.ContextMenu();

                     this.menuItem2 = new System.Windows.Forms.MenuItem();

                     this.menuItem3 = new System.Windows.Forms.MenuItem();

                     this.menuItem4 = new System.Windows.Forms.MenuItem();

                     this.dNetMenu1 = new YuanFeiSoftware.YuanFeiXPControls.DNetMenu();

 

                     //

                     // contextMenu1

                     //

                     this.contextMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {

       this.menuItem2,

       this.menuItem3,

       this.menuItem4});

                     //

                     // menuItem2

                     //

                     this.menuItem2.Index = 0;

                     this.dNetMenu1.SetSideBarForeColor(this.menuItem2, System.Drawing.Color.Cornsilk);

                     this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem2, System.Drawing.SystemColors.InactiveCaption);

                     this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem2, System.Drawing.SystemColors.ActiveCaption);

                     this.dNetMenu1.SetSideBarText(this.menuItem2, null);

                     this.menuItem2.Text = "Word文档";

                     this.menuItem2.Click += new EventHandler( ExportData );

                     //

                     // menuItem3

                     //

                     this.menuItem3.Index = 1;

                     this.dNetMenu1.SetSideBarForeColor(this.menuItem3, System.Drawing.Color.Cornsilk);

                     this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem3, System.Drawing.SystemColors.InactiveCaption);

                     this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem3, System.Drawing.SystemColors.ActiveCaption);

                     this.dNetMenu1.SetSideBarText(this.menuItem3, null);

                     this.menuItem3.Text = "文本文件";

                     this.menuItem3.Click += new EventHandler( ExportData );

                     //

                     // menuItem4

                     //

                     this.menuItem4.Index = 2;

                     this.dNetMenu1.SetSideBarForeColor(this.menuItem4, System.Drawing.Color.Cornsilk);

                     this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem4, System.Drawing.SystemColors.InactiveCaption);

                     this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem4, System.Drawing.SystemColors.ActiveCaption);

                     this.dNetMenu1.SetSideBarText(this.menuItem4, null);

                     this.menuItem4.Text = "Excel文件";

                     this.menuItem4.Click += new EventHandler( ExportData );

                     //

                     // dNetMenu1

                     //

                     this.dNetMenu1.CheckBorderColor = System.Drawing.SystemColors.Highlight;

                     this.dNetMenu1.CheckBoxColor = System.Drawing.Color.White;

                     this.dNetMenu1.GradientColorOne = System.Drawing.Color.White;

                     this.dNetMenu1.GradientColorTwo = System.Drawing.Color.LightBlue;

                     this.dNetMenu1.Location = new System.Drawing.Point(46, 78);

                     this.dNetMenu1.MenuBorderColor = System.Drawing.SystemColors.ControlDark;

                     this.dNetMenu1.Name = "dNetMenu1";

                     this.dNetMenu1.SelectionBorderColor = System.Drawing.Color.FromArgb(((System.Byte)(10)), ((System.Byte)(36)), ((System.Byte)(106)));

                     this.dNetMenu1.SelectionColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

                     this.dNetMenu1.Size = new System.Drawing.Size(32, 32);

                     this.dNetMenu1.StripeColor = System.Drawing.Color.FromArgb(((System.Byte)(219)), ((System.Byte)(216)), ((System.Byte)(209)));

                     this.dNetMenu1.TabIndex = 3;

                     this.dNetMenu1.TopMenuBackColor = System.Drawing.SystemColors.Control;

                     this.dNetMenu1.TopMenuHotBorder = System.Drawing.Color.FromArgb(((System.Byte)(10)), ((System.Byte)(36)), ((System.Byte)(106)));

                     this.dNetMenu1.TopMenuHotColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

                    this.dNetMenu1.TopMenuSelectedColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

 

                     this.Controls.Add(this.dNetMenu1);

                     this.Name = "JueJue2007";

                     this.Text = "导出到……";

                     this.Size = new Size( 138, 23);

                     this.ContextMenu = this.contextMenu1;

                     this.SuspendLayout();

              }

              #endregion

 

              /// <summary>

              /// 覆盖单击事件

              /// </summary>

              /// <param name="e">数据</param>

              protected override void OnClick(EventArgs e)

              {

                     this.contextMenu1.Show( this, new Point( 0, this.Height + 1) );

              }

 

              private void ExportData( object sender, System.EventArgs e )

              {

                     MenuItem m = sender as MenuItem;

                     category = m.Text;

 

                     //多线程处理

                     System.Threading.Thread t = new System.Threading.Thread( new System.Threading.ThreadStart( this.ExportDataThread ) );

                     t.Start();

              }

 

              private void ExportDataThread( )

              {

                     if( this.DataSource == null || this.DataSource.Rows.Count == 0 )

                     {

                            MessageBox.Show( "没有找到需要导出的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation );

                            return;

                     }

                    

                     // 1 WORD

                     if( category == "Word文档" )

                     {

                            SaveFileDialog of = new SaveFileDialog();

                            of.Filter = "Word文档|*.doc";

                            if( of.ShowDialog() == DialogResult.OK )

                            {

                                   string path = of.FileName;

                                   //DataTable dt = Oracle.GetDataTable( "select  t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注   from base_zykj_xm t " );

                                   Manipulate.ExportToWord( this,  DataSource, path, Caption );

                            }

                     }

                     // 2 EXCEL

                     else if( category == "Excel文件" )

                     {

                            SaveFileDialog of = new SaveFileDialog();

                            of.Filter = "Excel文件|*.xls";

                            if( of.ShowDialog() == DialogResult.OK )

                            {

                                   string path = of.FileName;

                                   //DataTable dt = Oracle.GetDataTable( "select  t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注   from base_zykj_xm t " );

                                   Manipulate.ExportToExcel( this, DataSource, path, Caption );

                            }

                     }

                     // 3 文本文件

                     else

                     {

                            SaveFileDialog of = new SaveFileDialog();

                            of.Filter = "文本文件|*.txt";

                            if( of.ShowDialog() == DialogResult.OK )

                            {

                                   string path = of.FileName;

                                   //DataTable dt = Oracle.GetDataTable( "select  t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注   from base_zykj_xm t " );

                                   Manipulate.ExportToText( DataSource, path, Caption );

                            }

                     }

       }

 

       }

}

// 具体的导出方法

using System;

using System.IO;

using System.Data;

using System.Windows.Forms;

using e = Microsoft.Office.Interop.Excel;

using w = Microsoft.Office.Interop.Word;

 

 

namespace JueJue2007

{

       /// <summary>

       /// 整合一组导出方法

       /// </summary>

       public class Manipulate

       {

              #region EXCEL

              /// <summary>

              /// 导出文件到EXCEL

              /// </summary>

              /// <param name="dt">要导出的数据</param>

              /// <param name="path">存放路径</param>

              public static void ExportToExcel( Button b, DataTable dt, string path, string caption )

              {

                     object Nothing = System.Reflection.Missing.Value;

                     e.Application app = new e.ApplicationClass();

                     app.Workbooks.Add( Nothing );

 

                     try

                     {

                            int ColumnLen = dt.Columns.Count;

                            int RowLen = dt.Rows.Count;

 

                            app.Cells[1, 1 ] = caption;

 

                            //写列名

                            for( int m = 0; m < ColumnLen; m ++ )

                            {

                                   app.Cells[ 2, 1 + m ] = dt.Columns[ m ].ColumnName;

                            }

 

                            //写行

                            int ii = dt.Rows.Count;

                            b.Text = "剩余" + ii.ToString() + "条数据";

                            for( int n = 0; n < RowLen; n ++ )

                            {

                                   for( int j = 0; j < ColumnLen; j ++ )

                                   {

                                          app.Cells[ 3 + n, 1 + j ] = dt.Rows[ n ][ j ].ToString();

                                   }

                                   ii -= 1;

                                   b.ForeColor = System.Drawing.Color.Blue;

                                   b.Text = "剩余" + ii.ToString() + "条数据";

                                   b.Refresh();

                            }

                            b.Text = "导出到……";

                            b.Refresh();

 

                            try

                            {

                                   //保存

                                   app.ActiveWorkbook._SaveAs( path, Nothing, Nothing, Nothing, Nothing, Nothing, e.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing );

                            }

                            catch

                            {

                                   System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件:  " + path );

                                   return;

                            }

 

                            //app.Visible = true; //让其打开

                     }

                     catch(Exception e)

                     {

                            System.Windows.Forms.MessageBox.Show("excel文件中写入数据出错:  " + e.Message + e.StackTrace + e.Source );

                     }

 

                     app.Workbooks.Close();

 

                     MessageBox.Show( "已经成功导出到:/n" + path + ".", "操作成功",  MessageBoxButtons.OK, MessageBoxIcon.Information  );

              }

 

              #endregion

 

      

             

              #region WORD

 

              /// <summary>

              /// 导出数据到WORD文件中

              /// </summary>

              /// <param name="dt">数据</param>

              /// <param name="path">存放路径</param>

              public static void ExportToWord( Button b, DataTable dt, string path, string caption )

              {

                     object tableBehavior = w.WdDefaultTableBehavior.wdWord9TableBehavior;

                     object autoFitBehavior = w.WdAutoFitBehavior.wdAutoFitFixed;

 

                     object unit = w.WdUnits.wdStory;

                     object extend = System.Reflection.Missing.Value;

                     object breakType = (int)w.WdBreakType.wdSectionBreakNextPage;

 

                     object count = 1;

                     object character = w.WdUnits.wdCharacter;

 

                     object Nothing =  System.Reflection.Missing.Value;

   

                     try

                     {

                            b.Text = dt.Rows.Count.ToString();

    

                            //创建一个word文件,文件名用系统时间生成精确到毫秒

                            w.Application myWord= new w.ApplicationClass();

                            w._Document myDoc = new w.DocumentClass();

                            myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing);

                            myDoc.Activate();

 

                            //向把dataset中的表插入到word的文件中   

                            myWord.Application.Selection.TypeText( caption );

                            myWord.Application.Selection.TypeParagraph();    

                            myWord.Application.Selection.TypeParagraph();

                            w.Range para = myWord.Application.Selection.Range;

                            myDoc.Tables.Add(para, dt.Rows.Count+1,dt.Columns.Count,ref tableBehavior,ref autoFitBehavior);

 

 

                            int ii = dt.Rows.Count;

                            b.Text = "剩余" + ii.ToString() + "条数据";

                            for(int column = 0; column< dt.Columns.Count;column++)

                            {

                                   myDoc.Tables[ 1 ].Cell(1,column+1).Range.InsertBefore( dt.Columns[column].ColumnName.Trim() );

                            }   

                            for( int row = 0; row < dt.Rows.Count; row ++ )

                            {

                                   for( int column = 0; column<dt.Columns.Count; column++ )

                                   {

                                          myDoc.Tables[ 1 ].Cell( row+2, column+1 ).Range.InsertBefore(dt.Rows[row][column].ToString().Trim() );

                                   }

                                   ii -= 1;

                                   b.ForeColor = System.Drawing.Color.Blue;

                                   b.Text = "剩余" + ii.ToString() + "条数据";

                                   b.Refresh();

                            }

                            b.Text = "导出到……";

                            b.Refresh();

 

                            myWord.Application.Selection.EndKey(ref unit,ref extend);

                            myWord.Application.Selection.TypeParagraph();

                            myWord.Application.Selection.TypeParagraph();

                            myWord.Application.Selection.InsertBreak(ref breakType);   

                            myWord.Application.Selection.TypeBackspace();

                            myWord.Application.Selection.Delete(ref character,ref count);

                            myWord.Application.Selection.HomeKey(ref unit,ref extend);

   

                            //保存word文件到指定的目录下

                            try

                            {

                                   object PATH = path;

                                   myDoc.SaveAs( ref PATH,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing, ref Nothing , ref Nothing , ref Nothing , ref Nothing , ref Nothing  );

                                   //myWord.Visible = true; //打开WORD文件

                            }

                            catch

                            {

                                   System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件:  " + path );

                                   return;

                            }

                            //

                            //关闭WordDoc文档对象

                            myDoc.Close(ref Nothing, ref Nothing, ref Nothing);

                            //关闭WordApp组件对象

                            myWord.Quit(ref Nothing, ref Nothing, ref Nothing);

                     }

                     catch(Exception ex)

                     {

                            System.Windows.Forms.MessageBox.Show("word文件中写入数据出错:  " + ex.Message);

                     }

 

                     MessageBox.Show( "已经成功导出到:/n" + path + ".", "操作成功",  MessageBoxButtons.OK, MessageBoxIcon.Information  );

              }

 

              #endregion

 

              #region Text

              public static void ExportToText( DataTable dt, string path, string caption )

              {

                     int DATADISTANCE = 5;

                     int TABDISTANCE = 8;

 

                     //创建一个.txt文件,文件名用系统时间生成精确到毫秒

                     FileInfo file = new FileInfo( path );

                     StreamWriter textFile = null;

                     try

                     {

                            textFile = file.CreateText();

                     }

                     catch

                     {

                            System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件:  " + path );

                            return;

                     }

 

 

                     //统计当前表的行数

                     int row = dt.Rows.Count;

 

                     //统计当前表的列数

                     int column = dt.Columns.Count;

 

                     //用于统计当前表中每列记录中字符数最长的字符串的长度之和

                     int totalLength = 0;

 

                     //用于统计标题的长度(dataset中的表名的length+"表的数据如下"length

                     int titleLength = 0;

 

                     //统计每列记录中字符数最长的字符串的长度

                     int[] columnLength = new int[column];

                     for( int i = 0; i < column; i ++ )

                     {

                            columnLength[i] = dt.Columns[i].ColumnName.ToString().Length;

                     }

                     for(int i = 0;i<row;i++)

                     {

                            for(int j = 0;j<column;j++)

                            {

                                   if(dt.Rows[i][j].ToString().Length > columnLength[j] )

                                   {

                                          columnLength[j] = dt.Rows[i][j].ToString().Length;

                                   }

                            }

                     }

 

 

                     //统计当前表中每列记录中字符数最长的字符串的长度之和

                     for(int i = 0;i<column;i++)

                     {

                            totalLength = totalLength+columnLength[i]+DATADISTANCE;

                     }

 

                     totalLength = totalLength+2 * TABDISTANCE - DATADISTANCE;

 

                     //统计标题的长度(dataset中的当前表名的length+"表的数据如下"length

                     titleLength = dt.TableName.ToString().Length+"表的数据如下".Length*2;

 

                     //把标题写入.txt文件中

                     for(int i = 0;i<(int)((totalLength-titleLength)/2);i++)

                     {

                            textFile.Write(' ');

                     }

                     textFile.Write( caption );

                     textFile.WriteLine();

                     for(int i = 0;i<totalLength;i++)

                     {

                            textFile.Write('*');

                     }

                     textFile.WriteLine();

                     textFile.Write("/t");

 

                     //dataset中当前表的字段名写入.txt文件中

                     for(int i = 0;i<column;i++)

                     {

                            textFile.Write(dt.Columns[i].ColumnName.ToString());

                            for(int k = 0;k<columnLength[i]-dt.Columns[i].ColumnName.ToString().Length+DATADISTANCE;k++)

                            {

                                   textFile.Write(' ');

                            }

                     }

                     textFile.WriteLine();

                     for(int i = 0;i<totalLength;i++)

                     {

                            textFile.Write('-');

                     }

                     textFile.WriteLine();

                     textFile.Write("/t");

 

                     //dataset中当前表的数据写入.txt文件中

                     for(int i = 0;i<row;i++)

                     {

                            for(int j = 0;j<column;j++)

                            {

                                   textFile.Write(dt.Rows[i][j].ToString());

                                   for(int k = 0;k<columnLength[j]-dt.Rows[i][j].ToString().Length+DATADISTANCE;k++)

                                   {

                                          textFile.Write(' ');

                                   }

                            }

                            textFile.WriteLine();

                            textFile.Write("/t");

                     }

                     textFile.WriteLine();

                     for(int i = 0;i<totalLength;i++)

                     {

                            textFile.Write('-');

                     }

                     textFile.WriteLine();

                     textFile.WriteLine();

                     textFile.WriteLine();

 

                     //关闭当前的StreamWriter

                     textFile.Close();

                     MessageBox.Show( "已经成功导出到:/n" + path + ".", "操作成功",  MessageBoxButtons.OK, MessageBoxIcon.Information  );

              }

             

              #endregion

       }

}

 

没有更多推荐了,返回首页