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

Source: Internet
Author: User

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

}
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.