Insert是T-sql中常用语句，Insert INTO table(field1,field2,…) values(value1,value2,…)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。 1.INSERT INTO SELECT语句 语句形式为：Insert into Table2(field1,field2,…) select value1,value2,… from Table1 要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下： —1.创建测试表 create TABLE Table1 ( a varchar(10), b varchar(10), c varchar(10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] create TABLE Table2 ( a varchar(10), c varchar(10), d int, CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] GO —2.创建测试数据 Insert into Table1 values('赵','asds','90') Insert into Table1 values('钱','asds','100') Insert into Table1 values('孙','asds','80') Insert into Table1 values('李','asds',null) GO select * from Table2 —3.INSERT INTO SELECT语句复制表数据 Insert into Table2(a, c, d) select a,c,5 from Table1 GO —4.显示更新后的结果 select * from Table2 GO —5.删除测试表 drop TABLE Table1 drop TABLE Table2 2.SELECT INTO FROM语句 语句形式为：SELECT vale1, value2 into Table2 from Table1 要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下： —1.创建测试表 create TABLE Table1 ( a varchar(10), b varchar(10), c varchar(10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] GO —2.创建测试数据 Insert into Table1 values('赵','asds','90') Insert into Table1 values('钱','asds','100') Insert into Table1 values('孙','asds','80') […]
View Details|
1 2 3 |
set identity_insert 表名 ON --允许对自增列Id插入指定数据 insert into table_name(Id,Name) values(1,'test') set identity_insert 表名 OFF --关闭对自增列Id插入指定数据 |
注意： 1.set identity_insert只对当前会话生效。 2.set identity_insert 表名 ON 设置后，必须显示指定Id，否则插入错误。如insert into table_name values('111')将报错。 向自增ID插入指定值。 报错：“当 IDENTITY_INSERT 设置为 OFF 时，不能为表 ' ' 中的标识列插入显式值”。 插入语句未显示指定ID。 报错：“仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表' '中的标识列指定显式值”。 from:http://www.cnblogs.com/wanghonghu/p/4093411.html
View Details1. 一个控制台例子，实现动态生成Word。 首先，添加引用：COM->Microsoft Word 11.0 Object Library。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 |
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using Microsoft.Office.Interop.Word; using System.Reflection; namespace TestWord { class Program { static void Main(string[] args) { object oMissing = System.Reflection.Missing.Value; object oEndOfDoc = "\\endofdoc"; //endofdoc是预定义的bookmark //创建一个document. Microsoft.Office.Interop.Word._Application oWord; Microsoft.Office.Interop.Word._Document oDoc; oWord = new Microsoft.Office.Interop.Word.Application(); oWord.Visible = true; oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing); //在document的开始部分添加一个paragraph. Microsoft.Office.Interop.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 行间距 oPara1.Range.InsertParagraphAfter(); //在当前document的最后添加一个paragraph Microsoft.Office.Interop.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(); //接着添加一个paragraph Microsoft.Office.Interop.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(); //添加一个3行5列的表格，填充数据，并且设定第一行的样式 Microsoft.Office.Interop.Word.Table oTable; Microsoft.Office.Interop.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; //接着添加一些文字 Microsoft.Office.Interop.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(); //添加一个5行2列的表，填充数据并且改变列宽 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); //设置列宽 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 (Microsoft.Office.Interop.Word.WdInformation.wdVerticalPositionRelativeToPage); } while (dPos >= Convert.ToDouble(oPos)); object oCollapseEnd = Microsoft.Office.Interop.Word.WdCollapseDirection.wdCollapseEnd; object oPageBreak = Microsoft.Office.Interop.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(); //添加一个chart Microsoft.Office.Interop.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."); Console.ReadLine(); } } } |
2. 介绍几篇牛人写的关于操作Word的文章 [分享]一段导出到word模版的代码 http://www.cnblogs.com/goody9807/archive/2005/08/25/222526.html 再谈word2003编程 http://www.cnblogs.com/Andmm/archive/2008/06/18/1224422.html 最近一直在做C#操作office方面的工作！总结一下！Word(二) http://www.cnblogs.com/wngwz/archive/2004/08/19/34678.html C#也能动态生成Word文档并填充数据 http://www.cnblogs.com/qyfan82/archive/2007/09/14/893293.html from:http://www.cnblogs.com/tianzhiliang/archive/2011/07/07/2099800.html
View Details引用http://blog.csdn.net/mengyao/archive/2007/09/13/1784079.aspx using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.IO; using Microsoft.Office.Interop.Word; namespace WindowsApplication4 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { CreateWordFile("c:\\dd.doc"); } public string CreateWordFile(string CheckedInfo) { string message = ""; try { Object Nothing = System.Reflection.Missing.Value; Directory.CreateDirectory("C:/CNSI"); //创建文件所在目录 string name = "CNSI_" + DateTime.Now.ToLongDateString()+".doc"; object filename = "C://CNSI//" + name; //文件保存路径 //创建Word文档 Microsoft.Office.Interop.Word.Application WordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); Microsoft.Office.Interop.Word.Document WordDoc = WordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing); //添加页眉 WordApp.ActiveWindow.View.Type = WdViewType.wdOutlineView; WordApp.ActiveWindow.View.SeekView = WdSeekView.wdSeekPrimaryHeader; WordApp.ActiveWindow.ActivePane.Selection.InsertAfter("[页眉内容]"); WordApp.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphRight;//设置右对齐 WordApp.ActiveWindow.View.SeekView = WdSeekView.wdSeekMainDocument;//跳出页眉设置 WordApp.Selection.ParagraphFormat.LineSpacing = 15f;//设置文档的行间距 //移动焦点并换行 object count = 14; object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdLine;//换一行; WordApp.Selection.MoveDown(ref WdLine, ref count, ref Nothing);//移动焦点 WordApp.Selection.TypeParagraph();//插入段落 //文档中创建表格 Microsoft.Office.Interop.Word.Table newTable = WordDoc.Tables.Add(WordApp.Selection.Range, 12, 3, ref Nothing, ref Nothing); //设置表格样式 newTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleThickThinLargeGap; newTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle; newTable.Columns[1].Width = 100f; newTable.Columns[2].Width = 220f; newTable.Columns[3].Width = 105f; //填充表格内容 newTable.Cell(1, 1).Range.Text = "产品详细信息表"; newTable.Cell(1, 1).Range.Bold = 2;//设置单元格中字体为粗体 //合并单元格 newTable.Cell(1, 1).Merge(newTable.Cell(1, 3)); WordApp.Selection.Cells.VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//垂直居中 WordApp.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中 //填充表格内容 newTable.Cell(2, 1).Range.Text = "产品基本信息"; newTable.Cell(2, 1).Range.Font.Color = Microsoft.Office.Interop.Word.WdColor.wdColorDarkBlue;//设置单元格内字体颜色 //合并单元格 newTable.Cell(2, 1).Merge(newTable.Cell(2, 3)); WordApp.Selection.Cells.VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; //填充表格内容 newTable.Cell(3, 1).Range.Text = "品牌名称："; newTable.Cell(3, 2).Range.Text = "BrandName"; //纵向合并单元格 newTable.Cell(3, 3).Select();//选中一行 object moveUnit = Microsoft.Office.Interop.Word.WdUnits.wdLine; object moveCount = 5; object moveExtend = Microsoft.Office.Interop.Word.WdMovementType.wdExtend; WordApp.Selection.MoveDown(ref moveUnit, ref moveCount, ref moveExtend); WordApp.Selection.Cells.Merge(); //插入图片 string FileName = @"c:\picture.jpg";//图片所在路径 object LinkToFile = false; object SaveWithDocument = true; object Anchor = WordDoc.Application.Selection.Range; WordDoc.Application.ActiveDocument.InlineShapes.AddPicture(FileName, ref LinkToFile, ref SaveWithDocument, ref Anchor); WordDoc.Application.ActiveDocument.InlineShapes[1].Width = 100f;//图片宽度 WordDoc.Application.ActiveDocument.InlineShapes[1].Height = 100f;//图片高度 //将图片设置为四周环绕型 Microsoft.Office.Interop.Word.Shape s = WordDoc.Application.ActiveDocument.InlineShapes[1].ConvertToShape(); […]
View Details一. 二进制转换成图片 MemoryStream ms = new MemoryStream(bytes); ms.Position = 0; Image img = Image.FromStream(ms); ms.Close(); this.pictureBox1.Image 二. C#中byte[]与string的转换代码 1、System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding(); byte[] inputBytes =converter.GetBytes(inputString); string inputString = converter.GetString(inputBytes); 2、string inputString = System.Convert.ToBase64String(inputBytes); byte[] inputBytes = System.Convert.FromBase64String(inputString); FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read); 三. C# Stream 和 byte[] 之间的转换 /// 将 Stream 转成 byte[] public byte[] StreamToBytes(Stream stream) { byte[] bytes = new byte[stream.Length]; stream.Read(bytes, 0, bytes.Length); // 设置当前流的位置为流的开始 stream.Seek(0, SeekOrigin.Begin); return bytes; } /// 将 byte[] 转成 Stream public Stream BytesToStream(byte[] bytes) { Stream stream = new […]
View Details