Asp.net uses the component koogra to read Excel Data and upload and import data in batches

 

Used in this article. net open-source component koogra allows you to read Excel content and upload it in batches. In enterprise applications, you often need to upload data in batches. you need to use an Excel table to input a batch of data and then upload the Excel table on the webpage, batch import data. In fact, this requirement is mainly to process an Excel table, read the content in the Excel table, perform some necessary verification, and then upload the data that has been verified successfully, returns the row number of the failed data and the verification failure information.

 

Koogra project component:

 

Http://sourceforge.net/projects/koogra/

 

Next we start the encapsulation process. For the sake of ease of use, there are four possible methods, and the returned results are all able objects.

 

1: the input parameters include the workbook address and worksheet name;

 

2: The input parameters include the workbook address and the worksheet index (0 indicates the first worksheet );

 

3: the input parameters include the memory stream and worksheet name;

 

4: the input parameters include memory stream and worksheet index (0 indicates the first worksheet ).

 

Start the code. Remember that the project references ionic. utils. Zip. dll and net. SourceForge. koogra. dll.

 

Code [http://www.oeedu.com]

Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;

Using net. SourceForge. koogra. Excel;

Namespace gren. Framework. Office
{
/// <Summary>
/// Excel Tool
/// </Summary>
Public class excelutils
{
Private workbook;

Public excelutils (string path)
{
This. Book = new Workbook (PATH );
}

Public excelutils (system. Io. Stream)
{
This. Book = new Workbook (Stream );
}

Protected datatable saveasdatatable (worksheet sheet)
{
Datatable dt = new datatable ();

Uint minrow = sheet. Rows. minrow;
Uint maxrow = sheet. Rows. maxrow;

Row firstrow = sheet. Rows [minrow];

Uint mincol = firstrow. cells. mincol;
Uint maxcol = firstrow. cells. maxcol;

For (uint I = mincol; I <= maxcol; I)
{
DT. Columns. Add (firstrow. cells [I]. formattedvalue ());
}

For (uint I = minrow 1; I <= maxrow; I)
{
Row row = sheet. Rows [I];

If (row! = NULL)
{
Datarow DR = DT. newrow ();

For (uint J = mincol; j <= maxcol; j)
{
Cell cell = row. cells [J];

If (cell! = NULL)
{
Dr [convert. toint32 (j)] = cell. value! = NULL? Cell. value. tostring (): String. empty;
}
}

DT. Rows. Add (DR );
}

}

Return DT;
}

Public datatable todatatable (INT index)
{
Worksheet sheet = This. Book. Sheets [Index];

If (sheet = NULL)
{
Throw new applicationexception (string. Format ("the specified workbook for index [{0}] does not exist! ", Index ));
}

Return this. saveasdatatable (sheet );
}

Public datatable todatatable (string sheetname)
{
Worksheet sheet = This. Book. Sheets. getbyname (sheetname );

If (sheet = NULL)
{
Throw new applicationexception (string. Format ("the specified workbook name [{0}] does not exist! ", Sheetname ));
}

Return this. saveasdatatable (sheet );
}

# Region static method

/// <Summary>
/// The cell format is datetime. Use this method to convert it to datetime type. If the parsing fails, '2017-01-01 'is returned'
/// </Summary>
Public static datetime parsedatetime (string cellvalue)
{
Datetime date = default (datetime );

Double value = default (double );

If (double. tryparse (cellvalue, out value ))
{
Date = datetime. fromoadate (value );
}
Else
{
Datetime. tryparse (cellvalue, out date );
}

Return date;
}

/// <Summary>
/// Convert to able (file path table name)
/// </Summary>
Public static datatable translatetotable (string path, string sheetname)
{
Excelutils utils = new excelutils (PATH );
Return utils. todatatable (sheetname );
}

/// <Summary>
/// Convert to datatable (file path table index)
/// </Summary>
Public static datatable translatetotable (string path, int sheetindex)
{
Excelutils utils = new excelutils (PATH );
Return utils. todatatable (sheetindex );
}

/// <Summary>
/// Convert to datatable (file path)
/// </Summary>
Public static datatable translatetotable (string path)
{
Excelutils utils = new excelutils (PATH );
Return utils. todatatable (0 );
}

/// <Summary>
/// Convert to able (memory stream table name)
/// </Summary>
Public static datatable translatetotable (system. Io. Stream stream, string sheetname)
{
Excelutils utils = new excelutils (Stream );
Return utils. todatatable (sheetname );
}

/// <Summary>
/// Convert to datatable (memory stream table index)
/// </Summary>
Public static datatable translatetotable (system. Io. Stream stream, int sheetindex)
{
Excelutils utils = new excelutils (Stream );
Return utils. todatatable (sheetindex );
}

/// <Summary>
/// Convert to datatable (memory stream)
/// </Summary>
Public static datatable translatetotable (system. Io. Stream)
{
Excelutils utils = new excelutils (Stream );
Return utils. todatatable (0 );
}

# Endregion
}
}

 

The parsedatetime static method is used to process the date and time type cells. In an Excel table, the date and time type is read as a double type data, use this method to obtain the correct value of the datatime type. You can find that this method not only converts data of the double type to datetime type, but also tries to convert the content of this cell as a string to datetime when the conversion fails, if the cell type is text, the date and time entered by the user is not double.

 

Well, the class for reading Excel is encapsulated. How can I use it on the Asp.net page? Let's look at the following code: fileupload1 is the file upload control.

 

 

 

Code [http://www.oeedu.com]

If (fileupload1.hasfile)
{
Datatable dt = NULL;

Try
{
Using (system. Io. memorystream stream = new system. Io. memorystream (fileupload1.filebytes ))
{
Dt = gren. Framework. Office. excelutils. translatetotable (stream, "sheet1 ");
}

// After obtaining the datatable object, perform your own Processing
}
Catch (exception ex)
{
Lblmessage. Text = "<p> <SPAN class =/" c_red ft_bold/"> handle data file errors: </span> </P> ";
Lblmessage. Text = "<Div class =/" c_red/">" server. htmlencode (ex. Message) "</div> ";
}
}
Else
{
Lblmessage. Text = "<p> <SPAN class =/" c_red ft_bold/"> select a data file </span> </P> ";
}

 

Using the memory stream directly to process uploaded files is still relatively fast. Please try it.