C # Operate Excel files and C # Merge multiple consecutive identical data items in Excel

C # Operate Excel files (read and write Excel files)

I have been asking questions about reading and importing Excel files on the forum. In my spare time, I will summarize what I know about Excel operations and share them with you. I hope to help you.
In addition, we also need to pay attention to some simple problems 1. the Excel file can only store 65535 rows of data. If your data is larger than 65535 rows, you need to separate the Excel file. 2. garbled characters.

1. Loading Excel (reading Excel content) the returned value is a dataset

// Load the Excel file
Public static dataset loaddatafromexcel (string filepath)
{
Try
{
String strconn;
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = 'excel 8.0; HDR = false; IMEX = 1 '";
Oledbconnection oleconn = new oledbconnection (strconn );
Oleconn. open ();
String SQL = "select * from [sheet1 $]"; // However, you can change the sheet name, such as sheet2.

Oledbdataadapter oledaexcel = new oledbdataadapter (SQL, oleconn );
Dataset oledsexcle = new dataset ();
Oledaexcel. Fill (oledsexcle, "sheet1 ");
Oleconn. Close ();
Return oledsexcle;
}
Catch (exception ERR)
{
MessageBox. Show ("An error occurred while binding data to excel! Cause of failure: "+ err. message," prompt message ",
Messageboxbuttons. OK, messageboxicon. information );
Return NULL;
}
}
2. Write the Excel content. Parameter: exceltable is a table to be imported into excel.

Public static bool savedatatabletoexcel (system. Data. datatable exceltable, string filepath)
{
Microsoft. Office. InterOP. Excel. Application APP =
New Microsoft. Office. InterOP. Excel. applicationclass ();
Try
{
App. Visible = false;
Workbook wbook = app. workbooks. Add (true );
Worksheet wsheet = wbook. worksheets [1] As worksheet;
If (exceltable. Rows. Count> 0)
{
Int ROW = 0;
Row = exceltable. Rows. count;
Int Col = exceltable. Columns. count;
For (INT I = 0; I <row; I ++)
{
For (Int J = 0; j <Col; j ++)
{
String STR = exceltable. Rows [I] [J]. tostring ();
Wsheet. cells [I + 2, J + 1] = STR;
}
}
}

Int size = exceltable. Columns. count;
For (INT I = 0; I <size; I ++)
{
Wsheet. cells [1, 1 + I] = exceltable. Columns [I]. columnname;
}
// Set to prohibit the pop-up prompt box for saving and overwriting
App. displayalerts = false;
App. alertbeforeoverwriting = false;
// Save the workbook
Wbook. Save ();
// Save the Excel file
App. Save (filepath );
App. saveworkspace (filepath );
App. Quit ();
APP = NULL;
Return true;
}
Catch (exception ERR)
{
MessageBox. Show ("An error occurred while exporting excel! Error cause: "+ err. message," prompt message ",
Messageboxbuttons. OK, messageboxicon. information );
Return false;
}
Finally
{
}
}

C # Merge multiple consecutive columns of the same data items in Excel

As follows:

CodeAs follows:

/** //

// merge the specified number of rows in the worksheet with the same number of columns.
///
/// worksheet index
/// Start row index
/// Start column index
/// Number of rows to be merged
// Number of columns to be merged
Public void mergeworksheet (INT sheetindex, int beginrowindex, int begincolumnindex, int rowcount, int columncount)
{

// Check parameters
If (columncount <1 | rowcount <1)
Return;

For (INT Col = 0; Col <columncount; Col ++)
{
Int mark = 0; // mark the position of the first record in the comparison data
Int mergecount = 1; // the same number of records, that is, the number of rows to be merged
String text = "";

For (int row = 0; row <rowcount; row ++)
{
String prvname = "";
String nextname = "";

// The last row does not need to be compared.
If (row + 1 <rowcount)
{
For (INT n = 0; n <= Col; n ++)
{
Range = (Excel. Range) worksheet. cells [row + beginrowindex, N + begincolumnindex];
Range = (Excel. Range) range. mergearea. get_item (1, 1 );
TEXT = range. Text. tostring ();
Prvname = prvname + text;

range = (Excel. range) worksheet. cells [row + 1 + beginrowindex, N + begincolumnindex];
range = (Excel. range) range. mergearea. get_item (1, 1);
nextname = nextname + range. text. tostring ();

}< br>
If (prvname = nextname)
{< br> mergecount ++;

If (ROW = rowcount-2)
{
This. mergecells (sheetindex, beginrowindex + mark, begincolumnindex + Col, beginrowindex + Mark + mergecount-1, begincolumnindex + Col, text );
}
}
Else
{
This. mergecells (sheetindex, beginrowindex + mark, begincolumnindex + Col, beginrowindex + Mark + mergecount-1, begincolumnindex + Col, text );
Mergecount = 1;
Mark = row + 1;
}

}
}
}
}