So Tired !_! 逆水行舟, 不进则退!

15Jun/10

SQL Server与Access、Excel的数据转换(导入导出)

Posted by Nick Xu

熟悉SQL SERVER 2000的数据库管理员都知道,其DTS可以进行数据的导入导出,其实,我们也可以使用Transact-SQL语句进行导入导出操作。在 Transact-SQL语句中,我们主要使用OpenDataSource函数、OPENROWSET 函数,关于函数的详细说明,请参考SQL联机帮助。 利用下述方法,可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换,详细说明如下:
一、SQL SERVER 和ACCESS的数据导入导出
常规的数据导入导出:
使用DTS向导迁移你的Access数据到SQL Server,你可以使用这些步骤:
1在SQL SERVER企业管理器中的Tools(工具)菜单上,选择Data Transformation
2Services(数据转换服务),然后选择 czdImport Data(导入数据)。
3在Choose a Data Source(选择数据源)对话框中选择Microsoft Access as the Source,然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。
4在Choose a Destination(选择目标)对话框中,选择Microsoft OLE DB Prov ider for SQL Server,选择数据库服务器,然后单击必要的验证方式。
5在Specify Table Copy(指定表格复制)或Query(查询)对话框中,单击Copy tables(复制表格)。
6在Select Source Tables(选择源表格)对话框中,单击Select All(全部选定)。下一步,完成。
Transact-SQL语句进行导入导出:
1. 在SQL SERVER里查询access数据:
Select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
2. 将access导入SQL server
在SQL SERVER 里运行:
Select * INTO newtable FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名
3. 将SQL SERVER表里的数据插入到Access表中
在SQL SERVER 里运行:
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名
(列名1,列名2)
select 列名1,列名2 from sql表

实例:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\db.mdb';'admin';'', Test)select id,name from Test Insert INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)
Select * FROM sqltablename

二、 SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
Select * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\xiangqu\abc.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1;"')...[唯一料件$]

注:
1.这条语句是在SQLServer查询分析器中执行,并且要选择好数据库,否则会把要导入的数据往别的数据库中导了。
2.Table08是数据导入后在SQLServer中的表名,属于新建,所以请确认在导入数据前数据库中没有该表名,否则会提示已存在同一表名。
3.Data Source,不要连在一起写,中间有一空格。
4.E:\1.xls,为Excel所在的绝对路径和数据库名。
5.Excel 5.0,根据不同的Excel版本写5.0或8.0或其它。
6.IMEX=1,是转换成文本输入的意思,非常重要,如果没有,就跟你直接导入效果一样。
7.Sheet1是表名,千万别看到语句中有$就在表名后加上$,因为$是语句要加的,别画蛇添足。
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
Select * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
2、将Excel的数据导入SQL server :
Select * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
Select * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

3、将SQL SERVER中查询到的数据导成一个Excel文件
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "Select au_fname, au_lname FROM pubs..authors orDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "Select col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

4、在SQL SERVER里往Excel插入数据:
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)
T-SQL代码:
Insert INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')

总结:利用以上语句,我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换,为我们提供了极大方便!

PS:SQL Server 2005 会遇到的问题:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 异常,

--解決辦法:
--使用前: (開啟Ad Hoc Distributed Queries)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
Select * INTO usertable FROM
OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\DB.mdb";User ID=;Password=' )
...usertable
--使用後: (關閉Ad Hoc Distributed Queries)
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

25May/10

C#用 excel 作为模板打印的源码

Posted by Nick Xu

源码

/*
'   //-*****************************************************-//
'  //            如果您要献一份爱心,请参预希望工程!       //
' //                如果您还支持作者,请联系作者!         //
'//-*****************************************************-//
'
'    /// 作 者:长江支流(周方勇)
'    /// Email:MisGoldPrinter@163.com  QQ:150439795
'    /// 网 址:www.webmis.com.cn
'    /// ★★★★★您可以免费使用此程序,但是请您完整保留此说明,以维护知识产权★★★★★
'
*/

//打印操作,套打、打印、预览
enum PrintFlag
{
/// <summary>
/// 套打,只打印没有印刷的部分
/// </summary>
CasePrint,
/// <summary>
/// 打印全部
/// </summary>
PrintAll,
/// <summary>
/// 预览全部
/// </summary>
PreviewAll
}

//套打、打印、预览三个按钮关联些委托实例
private void Print_Click(object sender, System.EventArgs e)
{
Button btn = (Button)sender;
switch(btn.Tag.ToString())
{
case "套打":
Print(PrintFlag.CasePrint);
break;
case "打印":
Print(PrintFlag.PrintAll);
break;
case "预览":
Print(PrintFlag.PreviewAll);
break;
}
}

private void Print(PrintFlag p_printFlag)
{

//制作步骤:
/*    1、用Excel作出与要打印的样式一样的电子表格存为模板;
*     技巧:最好把第一行与第一列作为空行,以利于调整边距(虽然Excel、打印机可调整页边距), 尽量的在需要调整的地方多空几行与几列,以利于调整套打对准
*
*  2、如同本程序一样,将Excel作为套打的模板,直接将要打印的数据写入;
*
*  3、打印,根据实际的效果调整Excel模板行高列宽及空出的行列, 直到能够准确的套上。将模板拷贝一份,清除模板上的文字与网格线,做成套打的模板。
*/

#region 套打、打印预览

//用Excel打印,步骤为:打开、写数据、打印预览、关闭
GoldPrinter.ExcelExpert.ExcelBase excel = new GoldPrinter.ExcelExpert.ExcelBase();
string strFileName = "invoice.xlt";            //模板文件名

if (p_printFlag == PrintFlag.CasePrint)
{
strFileName = "invoiceCase.xlt";        //套打模板文件名
}

string strExcelTemplateFile = System.IO.Path.GetFullPath(@"....ExcelTemplate" + strFileName);

excel.Open(strExcelTemplateFile);                            // 用模板文件
excel.Visible = false;                                        //建议:如果excel不可见且在编程情况下写数据特 别是大量数据时
excel.ScreenUpdating = false;                                //设置此开关能大大提高效率。写完后如要可见,再设置此属性为真刷新屏 幕。
excel.Caption = "税 务 机 关 代 开 统 一 发 票(国 税)";    //"MIS金质打印通  通打天下报表";

//在模板中写入要打印的数据

//***发票抬头***

//年月日
excel.SetCellText(7,"B",txtYear.Text + "年" + txtMonth.Text + "月" + txtDay.Text + "日" );

//付款方名称
excel.SetCellText(8,"D",txtPayer.Text);
//收款方名称
excel.SetCellText(9,"D",txtCollecter.Text);
//及地址、电话
excel.SetCellText(11,"D",txtCollecterAddTel.Text);

// 代开普通发票    申 请 表 号 码
excel.SetCellText(8,"J",txtInvoiceApplicationNo.Text);
//收款方识别号或 证 件 号 码
excel.SetCellText(9,"J",txtCollecterID.Text);

//***品名及金额、备注***
//B14到B23是品名   F14到F23为金额

excel.SetCellText("B14",txtP1.Text);
excel.SetCellText("F14",txtJ1.Text);

excel.SetCellText("B15",txtP2.Text);
excel.SetCellText("F15",txtJ2.Text);

excel.SetCellText("B16",txtP3.Text);
excel.SetCellText("F16",txtJ3.Text);

excel.SetCellText("B17",txtP4.Text);
excel.SetCellText("F17",txtJ4.Text);

excel.SetCellText("B18",txtP5.Text);
excel.SetCellText("F18",txtJ5.Text);

excel.SetCellText("B19",txtP6.Text);
excel.SetCellText("F19",txtJ6.Text);

excel.SetCellText("B20",txtP7.Text);
excel.SetCellText("F20",txtJ7.Text);

excel.SetCellText("B21",txtP8.Text);
excel.SetCellText("F21",txtJ8.Text);

excel.SetCellText("B22",txtP9.Text);
excel.SetCellText("F22",txtJ9.Text);

excel.SetCellText("B23",txtP10.Text);
excel.SetCellText("F23",txtJ10.Text);

//备注
//excel.SetCellText(14,"I",txtMemo.Imag.);

//***发票总金额***

//合计人民币   (大写)
excel.SetCellText(24,"D",txtTotalUpper.Text);
//合计人民币   (小写)
excel.SetCellText(24,"K",txtTotalLower.Text);

//税额   (大写)
excel.SetCellText(25,"D",txtTaxUpper.Text);
//税额   (小写)
excel.SetCellText(25,"L",txtTaxLower.Text);

//***发票尾***
//税控码
excel.SetCellText(26,"C",txtTaxControlCode.Text);
//开票人:
excel.SetCellText(26,"H",txtWriter.Text);

//刷新Excel屏幕
excel.ScreenUpdating = true;

if (p_printFlag == PrintFlag.CasePrint || p_printFlag == PrintFlag.PrintAll)
{
excel.Print();                //打印
}
else
{
excel.PrintPreview();        //预览
}

excel.Close();                    //关闭并释放

#endregion

}

private void frmInvoice_Load(object sender, System.EventArgs e)
{
//初始当日日期
System.DateTime dt = System.DateTime.Now;
SetToday(dt);
}

private void btnExit_Click(object sender, System.EventArgs e)
{
this.Close();
}

//回车
private void frmInvoice_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
if (e.KeyChar == (char)13)
{
SendKeys.Send("{TAB}");
}
}

//金额小写转人民币大写
private void txtTotalLower_KeyUp(object sender, System.Windows.Forms.KeyEventArgs e)
{
SetUpperMoney();
}

//重新总计
private void txtJX_TextChanged(object sender, System.EventArgs e)
{
double dblMoney = 0;

dblMoney += GetInputMoney(txtJ1.Text);
dblMoney += GetInputMoney(txtJ2.Text);
dblMoney += GetInputMoney(txtJ3.Text);
dblMoney += GetInputMoney(txtJ4.Text);
dblMoney += GetInputMoney(txtJ5.Text);
dblMoney += GetInputMoney(txtJ6.Text);
dblMoney += GetInputMoney(txtJ7.Text);
dblMoney += GetInputMoney(txtJ8.Text);
dblMoney += GetInputMoney(txtJ9.Text);
dblMoney += GetInputMoney(txtJ10.Text);

txtTotalLower.Text = dblMoney.ToString();
SetUpperMoney();

}

//改变税率重算
private void cboTaxRate_TextChanged(object sender, System.EventArgs e)
{
SetUpperMoney();
}

private void btnRefDate_Click(object sender, System.EventArgs e)
{
cldSelect.Visible = true;
cldSelect.SetDate(new DateTime(int.Parse(txtYear.Text),int.Parse(txtMonth.Text),int.Parse(txtDay.Text)));
cldSelect.Focus();
}

private void cldSelect_DateSelected(object sender, System.Windows.Forms.DateRangeEventArgs e)
{
SetToday(e.End);
cldSelect.Visible = false;
}

//大写合计人民币、税额
private void SetUpperMoney()
{
try
{
//                string strUpper = GoldPrinter.ExcelExpert.ChineseNum.GetUpperMoney(Double.Parse(txtTotalLower.Text));
//                //合计人民币
//                txtTotalUpper.Text = strUpper;
//
//                strUpper = GoldPrinter.ExcelExpert.ChineseNum.GetUpperMoney(Double.Parse(txtTotalLower.Text) * Double.Parse(cboTaxRate.Text) / 100);
//                //税额 = 合计人民币 * 税率
//                txtTaxUpper.Text = strUpper;

}
catch{}
}

private double GetInputMoney(string p_text)
{
double dblReturn = 0;
try
{
dblReturn = double.Parse(p_text);
}
catch{}

return dblReturn;
}

private void SetToday(System.DateTime dt)
{
txtYear.Text = dt.Year.ToString();

txtMonth.Text = GetLengthTwoDate(dt.Month.ToString());
txtDay.Text = GetLengthTwoDate(dt.Day.ToString());
}

private string GetLengthTwoDate(string p_MonthOrDay)
{
string strReturn = p_MonthOrDay;
if (strReturn.Length == 1)
{
strReturn = "0" + strReturn;
}

return strReturn;
}

}//End Class
}//End Namespace

Tagged as: , , , No Comments
25May/10

通过Excel自带的查询分析器快速完成从SQL Server中导出数据的例子(通用类)

Posted by Nick Xu

这是一个使用Excel中查询分析器(Ms通用查询分析器)完成从 SQL Server 7.0以上版本(已通过测试)的数据快速导出到Excel中的示例。它由两个参数完成,其中的第一个是你所要进行查询分析时使用的Select查询语句。 为了好看,我们给我们导出的数据加上一个名称。名称,由第二个参数传递进来:)好了,不说什么废话了。大家看代码吧。

using System;
using Excel;
namespace 类库
{
public class Excel导出
{
public Excel导出(string 查询语句,string 标题)
{
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
Excel._QueryTable xQt;
string Conn = "ODBC;DRIVER=SQL Server;SERVER=[服务器地址或者名称];UID=sa;PWD=[密码];APP=[应用程序名称(一般为操作系统名)];WSID=[工 作站名称(客户端)];DATABASE=[数据库名称]";
string Select = 查询语句;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
excel.Cells[2,2] = 标题;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Name = "黑体";
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
xQt = xSt.QueryTables.Add(Conn,xSt.get_Range(excel.Cells[4,2],excel.Cells[4,2]),Select);
xQt.Name = "导出示例";
xQt.FieldNames = true;
xQt.RowNumbers = false;
xQt.FillAdjacentFormulas = false;
xQt.PreserveFormatting = false;
xQt.BackgroundQuery = true;
xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
xQt.AdjustColumnWidth = true;
xQt.RefreshPeriod = 0;
xQt.PreserveColumnInfo = true;
xQt.Refresh(xQt.BackgroundQuery);
excel.Visible = true;
}
}
}

全中文的,不用进行解释了吧?

原来进行数据导出操作(相关连接http://www.csdn.net/Develop/Read_Article.asp?Id=21391), 三百条记录,用时十分钟以上。如果使用Excel自带的这一个查询工具,导出一万条记录,只需十秒钟以内的时间,而且,可以完成格式自动排版的功能。

可能有人会问:Excel里面的查询语句与SQL Server里面的查询语句是不是一样的?这里说明一点。使用这个类,可以直接使用SQL Server里面的查询语句,包括直接传递SQL Server的存储过程。

   
site
site