Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use SSIS Package to import database data". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to call SSIS Package to import database data".

(1) background

We often encounter how to import the data from the database into the EXCEL file. This article will compare several commonly used methods, and will explain in detail the usage based on SSIS. The author believes that the method based on SSIS should be the most efficient method for massive data. Personally, I think this is a recommended method, so I decided to write what I know and sum up completely, one is to improve my writing and expression ability, the other is to let more readers solve the efficiency problem of importing massive data into Excel in specific applications.

(2) comparison of methods

Plan 1: SSIS (SQL Server data Integration Service), the pursuit of efficiency, Package production process is a little more complex (error-prone).

Scheme 2: using COM.Excel components. In general, it can be basically satisfied with the operation, but it may be slower when the amount of data is large. The following code, I slightly modified, as follows: this method is mainly to write a cycle of cells, the basic method is excel.WriteValue (ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat). When the amount of data is large, there must be an impact on efficiency.

Public string DataExcels (System.Data.DataTable [] dts, string strTitle, string FilePath, Hashtable nameList,string [] titles)

{

COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile ()

/ / clear all files when the file is greater than 10!

ClearFile (FilePath)

/ / File name

String filename = strTitle+ DateTime.Now.ToString ("yyyyMMddHHmmssff") + ".xls"

/ / generate the corresponding file

Excel.CreateFile (FilePath + filename)

/ / set margin

COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin

COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin

COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin

COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin

Double height = 2.2

Excel.SetMargin (ref mt1, ref height)

Excel.SetMargin (ref mt2, ref height)

Excel.SetMargin (ref mt3, ref height)

Excel.SetMargin (ref mt4, ref height)

/ / set font!

COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat

String font = "Song style"

Short fontsize = 14

Excel.SetFont (ref font, ref fontsize, ref ff)

Byte b1 = 1, b2 = 12

Short S3 = 12

Excel.SetColumnWidth (ref b1, ref b2, ref S3)

String header = "header"

String footer = "footer"

Excel.SetHeader (ref header)

Excel.SetFooter (ref footer)

COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText

COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0

COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign

COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal

/ / report title

Int cellformat = 1

Int rowIndex = 1 position / start line

Int colIndex = 0

Foreach (System.Data.DataTable dt in dts)

{

ColIndex = 0

/ / get column headers

Foreach (DataColumn colhead in dt.Columns)

{

ColIndex++

String name = colhead.ColumnName.Trim ()

Object namestr = (object) name

Excel.WriteValue (ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat)

}

/ / get the data in the table

Foreach (DataRow row in dt.Rows)

{

At this point, I believe you have a deeper understanding of "how to use SSIS Package to import database data". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.