Export data from data warehouse to worddeskexcel.txt

Export data from data warehouse to word‑excel‑.txt using system;
Using system. Data;
Using system. drawing;
Using system. Data. sqlclient;
Using Excel;
Using word;
Using system. IO;

Namespace common
{
/// <Summary>
/// Import data to the .doc).txt).xls File
/// </Summary>
Public class export
{
Private const string datawordpath = @ "C: \ Folder \ doc \ datadoc \";
Private const string imagewordpath = @ "C: \ Folder \ doc \ imagedoc \";
Private const string ImagePath = @ "C: \ Folder \ image \";
Private const string excelpath = @ "C: \ Folder \ Excel \";
Private const string txtpath = @ "C: \ Folder \ TXT \";
Private const string imagepostfix = ". BMP ";
Private const string wordpostfix = ". Doc ";
Private const string excelpostfix = ". xls ";
Private const string txtpostfix = ". txt ";
Private const int datadistance = 5;
Private const int tabdistance = 8;

Public Export ()
{
//
// Todo: add the constructor logic here
//
}

/// <Summary>
/// Obtain the dataset -------------------------------- for debugging
/// </Summary>
/// <Returns> dataset </returns>
Public dataset getdata ()
{
Try
{
String sconnectionstring;
Sconnectionstring = "workstation id = Guofu; packet size = 4096; user id = sa; Data Source = Guofu; persist Security info = true; initial catalog = YC; Password = SC ";
Sqlconnection objconn = new sqlconnection (sconnectionstring );
Objconn. open ();
Sqldataadapter dapoint = new sqldataadapter ("select * from point", objconn );
Dataset dsyc = new dataset ("YC ");
Dapoint. fillschema (dsyc, schematype. mapped, "point ");
Dapoint. Fill (dsyc, "point ");
Dapoint = new sqldataadapter ("select * from employee", objconn );
Dapoint. fillschema (dsyc, schematype. mapped, "employee ");
Dapoint. Fill (dsyc, "employee ");
Return dsyc;
}
Catch (exception ex)
{
Throw new exception (ex. Message );
}

}

/// <Summary>
/// Import the data file to the. xls file
/// </Summary>
/// <Param name = "ds"> </param>
Public void exporttoexcel (Dataset DS)
{

If (Ds. Tables. Count! = 0)
{< br> // complete path name of the. xls file
string tempfilename = gettempfilename ();
Object filename = excelpath + tempfilename + excelpostfix;
Object nothing = system. reflection. missing. value;
// create an Excel file. The file name is generated in milliseconds.
excel. application myexcel = new excel. applicationclass ();
myexcel. application. workbooks. add (nothing);

try
{
// insert data in dataset into an Excel file
int totalcount = 0;
for (int K = 0; k {< br> int ROW = Ds. tables [K]. rows. count;
int column = Ds. tables [K]. columns. count;
for (INT I = 0; I {< br> myexcel. cells [totalcount + 2, 1 + I] = Ds. tables [K]. columns [I]. columnname;
}

For (INT I = 0; I <row; I ++)
{
For (Int J = 0; j <column; j ++)
{
Myexcel. cells [totalcount + 3 + I, 1 + J] = "'" + Ds. Tables [K]. Rows [I] [J]. tostring ();
}
}
Totalcount = totalcount + row + 4;
}

Try
{
// Save the Excel file to the specified directory. The file name is generated in milliseconds.
Myexcel. activeworkbook. _ saveas (filename, nothing, xlsaveasaccessmode. xlexclusive, nothing );
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + excelpath + tempfilename + excelpostfix );
Return;
}
// Make the generated Excel file visible
Myexcel. Visible = true;
}
Catch (exception E)
{
System. Windows. Forms. MessageBox. Show ("An error occurred while writing data to an Excel file:" + E. Message );
}
}
Else
{
System. Windows. Forms. MessageBox. Show ("no data ");
}
}

/// <Summary>
/// Import data to the. DOC file
/// </Summary>
/// <Param name = "ds"> </param>
Public void exporttoword (Dataset DS)
{
If (Ds. Tables. Count! = 0)
{
String tempfilename = NULL;
Object filename = NULL;

Object tablebehavior = word. wddefaulttablebehavior. wdword9tablebehavior;
Object autofitbehavior = word. wdautofitbehavior. wdautofitfixed;

Object Unit = word. wdunits. wdstory;
Object extend = system. reflection. Missing. value;
Object breaktype = (INT) Word. wdbreaktype. wdsectionbreaknextpage;

Object COUNT = 1;
Object character = word. wdunits. wdcharacter;

Object nothing = system. reflection. Missing. value;

Try
{
Tempfilename = gettempfilename ();

// Generate. DOC file full path name
Filename = datawordpath + tempfilename + wordpostfix;

// Create a Word file. The file name is generated in milliseconds.
Word. Application myword = new word. applicationclass ();
Word. _ document mydoc = new word. documentclass ();
Mydoc = myword. Documents. Add (ref nothing, ref nothing );
Mydoc. Activate ();

// Insert the table in dataset to the Word file

For (INT totaltable = 0; totaltable <Ds. Tables. Count; totaltable ++)
{
Myword. application. selection. typetext (Ds. Tables [totaltable]. tablename + "the table data is as follows ");
Myword. application. selection. typeparagraph ();
Myword. application. selection. typeparagraph ();
Word. Range para = myword. application. selection. range;
Mydoc. Tables. Add (para, DS. Tables [totaltable]. Rows. Count + 1, DS. Tables [totaltable]. Columns. Count, ref tablebehavior, ref autofitbehavior );
For (INT column = 0; column <Ds. Tables [totaltable]. Columns. Count; column ++)
{
Mydoc. tables. item (totaltable + 1 ). cell (1, column + 1 ). range. insertbefore (Ds. tables [0]. columns [column]. columnname. trim ());
}
For (int row = 0; row <Ds. Tables [totaltable]. Rows. Count; row ++)
{
For (INT column = 0; column <Ds. Tables [totaltable]. Columns. Count; column ++)
{
Mydoc. tables. item (totaltable + 1 ). cell (row + 2, column + 1 ). range. insertbefore (Ds. tables [totaltable]. rows [row] [column]. tostring (). trim ());
}
}
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 );

// Save the Word file to the specified directory
Try
{
Mydoc. saveas (ref filename, ref nothing );
Myword. Visible = true;
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + datawordpath + tempfilename + wordpostfix );
Return;
}
// Make the generated Excel file visible
Myword. Visible = true;
}
Catch (exception ex)
{
System. Windows. Forms. MessageBox. Show ("An error occurred while writing data to the Word file:" + ex. Message );
}
}
Else
{
System. Windows. Forms. MessageBox. Show ("no data ");
}
}
/// <Summary>
/// Import the image file to the .doc File
/// </Summary>
/// <Param name = "BP"> </param>
Public void exporttoword (Bitmap bp)
{
String tempfilename = NULL;
String BMP Path = NULL;
Object filename = NULL;
Object nothing = NULL;
Tempfilename = gettempfilename ();

 
// Generate the complete path name of the. BMP file
BMP Path = ImagePath + tempfilename + imagepostfix;

// Generate. DOC file full path name
Filename = imagewordpath + tempfilename + wordpostfix;
Nothing = system. reflection. Missing. value;
 
// Create a Word file. The file name is generated in milliseconds.
Word. Application myword = new word. applicationclass ();
Word. _ document mydoc = new word. documentclass ();
Mydoc = myword. Documents. Add (ref nothing, ref nothing );

Try
{
// Save the bitmap object to the complete path of the file generated by the system
BP. Save (BMP path );
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + BMP path );
Return;
}

Try
{
// Insert an image into the Word file
Mydoc. inlineshapes. addpicture (BMP path, ref nothing );
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + BMP path );
Return;
}

Try
{
// Save the Word file to the specified directory
Mydoc. saveas (ref filename, ref nothing );
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + imagewordpath + tempfilename + wordpostfix );
Return;
}

// Make the generated Word file visible
Myword. Visible = true;
}

/// <Summary>
/// Import the data file to the. txt file
/// </Summary>
/// <Param name = "ds"> </param>
Public void exporttotxt (Dataset DS)
{

If (Ds. Tables. Count! = 0)
{
String tempfilename = NULL;
Tempfilename = gettempfilename ();

// Create a. txt file. The file name is generated in milliseconds.
Fileinfo file = new fileinfo (txtpath + tempfilename + txtpostfix );
Streamwriter textfile = NULL;
Try
{
Textfile = file. createtext ();
}
Catch
{
System. Windows. Forms. MessageBox. Show ("the file in the specified directory cannot be found:" + txtpath + tempfilename + txtpostfix );
Return;
}

// Write the data in datasetto The. txt file.
For (INT totaltable = 0; totaltable <Ds. Tables. Count; totaltable ++)
{
// Count the number of rows in the current table in Dataset
Int ROW = Ds. Tables [totaltable]. Rows. count;

// Count the number of columns in the current table in Dataset
Int column = Ds. Tables [totaltable]. Columns. count;

// Used to count the sum of the longest character strings in each column of records in the current table
Int totallength = 0;

// Used to calculate the length of the title (the length of the table name in dataset + the length of "The table data is as follows)
Int titlelength = 0;

// count the length of the string with the longest number of characters in each column record
int [] columnlength = new int [column];
for (INT I = 0; I {< br> columnlength [I] = Ds. tables [totaltable]. columns [I]. columnname. tostring (). length;
}< br> for (INT I = 0; I {< br> for (Int J = 0; j {< br> If (Ds. tables [totaltable]. rows [I] [J]. tostring (). length> columnlength [J])
{< br> columnlength [J] = Ds. tables [totaltable]. rows [I] [J]. tostring (). length;
}< BR >}

// Count the total length of the string with the longest Character Count in each column in the current table
For (INT I = 0; I <column; I ++)
{
Totallength = totallength + columnlength [I] + datadistance;
}
Totallength = totallength + 2 * TABDISTANCE-DATADISTANCE;

// The length of the statistical title (the length of the current table name in dataset + the length of "The table data is as follows)
Titlelength = Ds. Tables [totaltable]. tablename. tostring (). Length + "the table data is as follows". length * 2;

// write the token to The. txt file
for (INT I = 0; I <(INT) (totallength-titlelength)/2); I ++)
{< br> textfile. write ('');
}< br> textfile. write (Ds. tables [totaltable]. tablename + "the table data is as follows");
textfile. writeline ();
for (INT I = 0; I {< br> textfile. write ('*');
}< br> textfile. writeline ();
textfile. write ("\ t");

// write the field name of the current table in datasetto the. txt file
for (INT I = 0; I {< br> textfile. write (Ds. tables [totaltable]. columns [I]. columnname. tostring ();
for (int K = 0; k {< br> textfile. write ('');
}< BR >}< br> textfile. writeline ();
for (INT I = 0; I {< br> textfile. write ('-');
}< br> textfile. writeline ();
textfile. write ("\ t");

// Write the data of the current table in datasetto The. txt file.
For (INT I = 0; I <row; I ++)
{
For (Int J = 0; j <column; j ++)
{
Textfile. Write (Ds. Tables [totaltable]. Rows [I] [J]. tostring ());
For (int K = 0; k <columnlength [J]-Ds. tables [totaltable]. 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 ();
}

// Close the current streamwriter stream
Textfile. Close ();
System. Windows. Forms. MessageBox. Show ("the data file has been saved to" + "" + file. fullname );
}
Else
{
System. Windows. Forms. MessageBox. Show ("no data ");
}
}

Public String gettempfilename ()
{
Return datetime. Now. tostring ("yyyymmddhhmmssfff ");
}
}
}

Supplement: You must configure DCOM to grant you the permission to use the office.
The configuration method is as follows:
1: Install the Excel software for office on the server.
2: Enter dcomcnfg.exe in "start"-> "run" to start "Component Service"
3: Double-click "component service"> "computer"> "my computer"> "DCOM configuration"
4: Find the Microsoft Excel application in "DCOM configuration ". Program ", Right-click it, and then click" properties ". The" Microsoft Excel application properties "dialog box is displayed.
5. Click the "ID" tab and select "Interactive User"
6. Click the "Security" tab, click "Custom" on "Start and activate Permissions", and then click the corresponding "edit" button, in the pop-up "security" dialog box, add a "Network Service" user (note that you must select the computer name) and grant it the "local start" and "Local activation" permissions.
7: the "Security" label remains. Click "Custom" on "Access Permissions", and then click "edit ", in the pop-up "security" dialog box, add a "Network Service" user and grant "local access" permission.
In this way, we have configured the corresponding Excel DCOM permissions.
Note: I configured it on win2003 and configured the ASPNET user on 2000.

If the configuration is not performed, an error occurs.
the following error occurs when retrieving components whose CLSID is {00024500-0000-0000-c000-000000000046} in the com class factory.
the reason is that you are not authorized to use Excel.
to export data to word, you also need to configure the permission to use word. I used the above method to export to TXT.
try
{
textfile = file. createtext ();
}< br> catch
{< br> system. windows. forms. messageBox. show ("the system cannot find the file in the specified directory:" + txtpath + tempfilename + txtpostfix);
return;
}< br> always jump to catch here. Export to Word, Excel can be used, continue to study the use of TXT.