Excelhelper (Excel and C #, Asp.net import and export, general class) (1)

Http://blog.163.com/it_software/blog/static/1238582962010518113421381/

Using system;
Using system. IO;
Using system. text;
Using system. Data;
Using system. reflection;
Using system. diagnostics;
Using system. collections;

// Using CFG = system. configuration;

Namespace gdbi. Common
{
/// <Summary>
// Description: Excel output module
/// Currently, the EXCEL object style operation method is not provided. You can set the style in the Excel template.
/// OPERATOR: lingyun_k
/// Creation date: 2005-7-12
/// </Summary>
Public class excelhelper
{
# Region member variables
Private string templetfile = NULL;
Private string outputfile = NULL;
Private object missing = missing. value;
Private datetime Beforetime; // time before Excel start
Private datetime aftertime; // time after Excel startup
Excel. Application app;
Excel. Workbook workbook;
Excel. worksheet;
Excel. Range range;
Excel. Range range1;
Excel. Range range2;
Excel. textbox;
Private int sheetcount = 1; // Number of worksheet instances
Private string sheetprefixname = "page ";
# Endregion

# Region Public attributes
/// <Summary>
/// Worksheet prefix name. For example, if the prefix is "page", the names of worksheet are "Page-1, page-2 ..."
/// </Summary>
Public String sheetprefixname
{
Set {This. sheetprefixname = value ;}
}

/// <Summary>
/// Number of worksheet instances
/// </Summary>
Public int worksheetcount
{
Get {return Workbook. Sheets. Count ;}
}

/// <Summary>
/// Excel template file path
/// </Summary>
Public String templetfilepath
{
Set {This. templetfile = value ;}
}

/// <Summary>
/// Output the Excel file path
/// </Summary>
Public String outputfilepath
{
Set {This. outputfile = value ;}
}
# Endregion

# Region Public Method

# Region excelhelper
/// <Summary>
/// Constructor. Use an existing Excel Workbook as a template and specify the output path.
/// </Summary>
/// <Param name = "templetfilepath"> excel template file path </param>
/// <Param name = "outputfilepath"> output Excel file path </param>
Public excelhelper (string templetfilepath, string outputfilepath)
{
If (templetfilepath = NULL)
Throw new exception ("the Excel template file path cannot be blank! ");

If (outputfilepath = NULL)
Throw new exception ("the output Excel file path cannot be blank! ");

If (! File. exists (templetfilepath ))
Throw new exception ("the Excel template file in the specified path does not exist! ");

This. templetfile = templetfilepath;
This. outputfile = outputfilepath;

// Create an application object and make it visible
Beforetime = datetime. now;
APP = new excel. applicationclass ();
App. Visible = true;
Aftertime = datetime. now;

// Open the template file to obtain the workbook object.
Workbook = app. workbooks. Open (templetfile, missing,
Missing, missing, and missing );

// Obtain the worksheet object
Worksheet = (Excel. worksheet) Workbook. Sheets. get_item (1 );

}

/// <Summary>
/// Constructor to open an existing workbook
/// </Summary>
/// <Param name = "FILENAME"> Excel file name </param>
Public excelhelper (string filename)
{
If (! File. exists (filename ))
Throw new exception ("the Excel file in the specified path does not exist! ");

// Create an application object and make it visible
Beforetime = datetime. now;
APP = new excel. applicationclass ();
App. Visible = true;
Aftertime = datetime. now;

// Open a workbook
Workbook = app. workbooks. Open (filename,
Type. Missing,
Type. Missing,
Type. Missing, type. Missing );

// Obtain the worksheet object
Worksheet = (Excel. worksheet) Workbook. Sheets. get_item (1 );

}

/// <Summary>
/// Constructor to create a new workbook
/// </Summary>
Public excelhelper ()
{
// Create an application object and make it visible
Beforetime = datetime. now;
APP = new excel. applicationclass ();
App. Visible = true;
Aftertime = datetime. now;

// Create a workbook
Workbook = app. workbooks. Add (type. Missing );

// Obtain the worksheet object
Worksheet = (Excel. worksheet) Workbook. Sheets. get_item (1 );

}
# Endregion

# Region data export Methods

/// <Summary>
/// Write datatable data to an Excel file (automatic paging)
/// </Summary>
/// <Param name = "DT"> datatable </param>
/// <Param name = "rows"> Number of rows of data written to each worksheet </param>
/// <Param name = "TOP"> index of the Start row of table data </param>
/// <Param name = "Left"> index of the Start column of table data </param>
Public void datatabletoexcel (datatable DT, int rows, int top, int left)
{
Int rowcount = DT. Rows. Count; // Number of datatable rows
Int colcount = DT. Columns. Count; // Number of datatable Columns
Sheetcount = This. getsheetcount (rowcount, rows); // Number of Worksheet
// Stringbuilder Sb;

// Copy sheetCount-1 worksheet objects
For (INT I = 1; I <sheetcount; I ++)
{
Worksheet = (Excel. worksheet) Workbook. worksheets. get_item (I );
Worksheet. Copy (missing, Workbook. worksheets [I]);
}

For (INT I = 1; I <= sheetcount; I ++)
{
Int startrow = (I-1) * Rows; // record the index of the starting row
Int endrow = I * Rows; // record end row Index

// If it is the last worksheet, the index of the end row of the record is the number of rows of the source datatable.
If (I = sheetcount)
Endrow = rowcount;

// Obtain the worksheet object to write data and rename it
Worksheet = (Excel. worksheet) Workbook. worksheets. get_item (I );
Worksheet. Name = sheetprefixname + "-" + I. tostring ();

// Write data in DT to Worksheet
// For (Int J = 0; j <endrow-startrow; j ++)
//{
// For (int K = 0; k <colcount; k ++)
//{
// Worksheet. cells [top + J, left + k] = DT. Rows [startrow + J] [K]. tostring ();
//}
//}

// Batch write using two-dimensional array
Int ROW = endrow-startrow;
String [,] Ss = new string [row, colcount];

For (Int J = 0; j <row; j ++)
{
For (int K = 0; k <colcount; k ++)
{
Ss [j, k] = DT. Rows [startrow + J] [K]. tostring ();
}
}

Range = (Excel. Range) worksheet. cells [top, left];
Range = range. get_resize (row, colcount );
Range. value = SS;

# Region uses windwo clipboard to copy data in batches (it won't work in the web environment)
/* Sb = new stringbuilder ();

For (Int J = 0; j <endrow-startrow; j ++)
{
For (int K = 0; k <colcount; k ++)
{
SB. append (Dt. Rows [startrow + J] [K]. tostring ());
SB. append ("\ t ");
}

SB. append ("\ n ");
}

System. Windows. Forms. clipboard. setdataobject (sb. tostring ());

Range = (Excel. Range) worksheet. cells [top, left];
Worksheet. paste (range, false );*/
# Endregion

}
}