During the project development process and office process, you often encounter operations such as creating, reading and writing Excel. There are too many experts in Excel, and sorting it out has become a big problem. Organizing Excel through programs can quickly provide development and office efficiency.
Several ways to operate Excel
Using OleDb (Obsolete)
Using COM components (compatibility issues)
Use the open source library NPOI (commonly used, rich in operation)
Using OpenXml (high efficiency)
Using OleDb
Operation of excel through OleDb requires the installation of AccessDatabaseEngine component, which has certain restrictions on the format of Excel file and does not support some specific Excel functions. The performance may not be as good as other methods, it is relatively old and basically not used.
string excelFilePath = "D:\\Test\\";
private void OpenExcelByOleDb()
{
string connectionString = $"Provider=.12.0;Data Source={excelFilePath};Extended Properties='Excel 12.0;HDR=YES;'";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
try
{
();
("The link is successful!");
// Get the data of the first worksheet in the Excel file string query = "SELECT * FROM [Sheet1$]";
using (OleDbCommand command = new OleDbCommand(query, connection))
using (OleDbDataReader reader = ())
{
while (())
{
// Assume that the first column is a string and the second column is an integer string stringValue = (0);
int intValue = reader.GetInt32(1);
($"String value: {stringValue}, Integer value: {intValue}");
}
}
}
catch (Exception ex)
{
($"mistake: {}");
}
}
}Operation of Excel through COM components can achieve high control of Excel files and support complex Excel operations. However, resource management needs to be handled with caution, which can easily cause resource leakage and low compatibility.
string excelFilePath = "D:\\Test\\";
private void OpenExcelByInteropExcel()
{
// Create Excel application object excelApp = new ();
= true; // The Excel application interface can be seen
// Open Excel file workbook = (excelFilePath);
worksheet = [1] as ;
// Read or write data range = ;
for (int row = 1; row <= ; row++)
{
for (int column = 1; column <= ; column++)
{
// Process cell data var cellValue = [row, column].Value;
(());
//[row, column].Value = 1;
}
}
#region Save template //object Nothing = ;
// workbook1 = (Nothing);
// worksheet1 = ()["Sheet2"];//Open Sheet2 //(["Sheet1"], );//Copy the template Sheet1 content //(false, , );
//("modelpath", , , , , , , , , , , );
//(false, , );
#endregion
// Free up resources (false);
();
(excelApp);
}Using the open source library NPOI
Use the open source library NPOI to operate Excel cross-platform, does not rely on Microsoft Office, and is suitable for operating Excel files in server-side and other environments. Supports reading and writing Excel files and provides rich APIs. However, for complex Excel operations, it is not as flexible as COM components and cannot implement some advanced functions. Nuget references NPOI components.
string excelFilePath = "D:\\Test\\";
private void OpenExcelByNPOI()
{
// Read Excel file using (FileStream fs = new FileStream(excelFilePath, , ))
{
IWorkbook workbook = new XSSFWorkbook(fs);
ISheet sheet = (0);
// traverse each line for (int row = 0; row <= ; row++)
{
IRow currentRow = (row);
if (currentRow != null) // Make sure that the behavior is not empty {
// traverse each column for (int column = 0; column < ; column++)
{
ICell currentCell = (column);
if (currentCell != null) // Make sure the cell is not empty {
// Process cell data var cellValue = ();
(cellValue);
}
}
}
}
}
}Using OpenXml
Operate Excel with OpenXml, which operates directly on file streams without loading the entire document into memory and installing Microsoft Office, is perfect for server-side applications and batch processing. Nuget reference component.
string excelFilePath = "D:\\Test\\";
private void OpenExcelByOpenXml()
{
//Stream files, high performance using (SpreadsheetDocument doc = (excelFilePath, false))
{
WorkbookPart workbookPart = ;
Sheet sheet = <Sheet>();
WorksheetPart worksheetPart = (WorksheetPart)();
OpenXmlReader reader = (worksheetPart);
while (())
{
if ( == typeof())
{
row = ()();
foreach ( cell in <>())
{
string cellValue = GetCellValue(doc, cell);
(cellValue + " ");
}
();
}
}
}
}
/// <summary>
/// OpenXml gets cell value /// Streaming /// </summary>
/// <param name="doc"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(SpreadsheetDocument doc, cell)
{
SharedStringTablePart stringTablePart = ;
string value = ;
if ( != null && == )
{
return [(value)].InnerText;
}
else
{
return value;
}
}Operation between Excel and DatagridView
Import Excel file into DateGridView
/// <summary>
/// Excel imports DataGridView /// </summary>
/// <param name="excelFilePath"></param>
/// <param name="dataGridView"></param>
public static void ImportExcelToDataGridView(string excelFilePath, DataGridView dataGridView)
{
excelApp = new ();
workbook = (excelFilePath);
worksheet = [1];
Range usedRange = ;
for (int i = 1; i <= ; i++)
{
DataGridViewColumn column = new DataGridViewColumn();
= "Column" + i;
= "Column" + i;
= new DataGridViewTextBoxCell();
(column);
}
();
for (int i = 1; i <= ; i++)
{
DataGridViewRow row = new DataGridViewRow();
for (int j = 1; j <= ; j++)
{
(new DataGridViewTextBoxCell());
[j - 1].Value = [i, j].Value;
}
(row);
}
();
();
(worksheet);
(workbook);
(excelApp);
worksheet = null;
workbook = null;
excelApp = null;
();
}DateGridView Export Excel Files
/// <summary>
/// Datagridview export excel /// </summary>
/// <param name="fileName"></param>
/// <param name="myDGV"></param>
private void DatagridViewToExcel( DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
= "xls";
= "Excel Files|*.xls";
if(()==)
{
return;
}
saveFileName = ;
if ((":") < 0) return; //Cancel was clicked xlApp = new ();
if (xlApp == null)
{
("Unable to create Excel object, maybe your machine does not have Excel installed");
return;
}
workbooks = ;
workbook = ();
worksheet = ()[1];//Get sheet1 //Write the title for (int i = 0; i < ; i++)
{
[1, i + 1] = [i].HeaderText;
}
//Write the value for (int r = 0; r < ; r++)
{
for (int i = 0; i < ; i++)
{
[r + 2, i + 1] = [r].Cells[i].Value;
}
();
}
();//Column width adaptation if (saveFileName != "")
{
try
{
= true;
(saveFileName);
}
catch (Exception ex)
{
("An error occurred while exporting the file, the file may be being opened!\n" + );
}
}
();
();//Forcibly destroyed ("document: " + saveFileName + ".xls saved successfully", "Information prompt",
, );
}Operation between CSV and DatagridView
Import csv file into DateGridView
/// <summary>
/// Import csv file data into datagridview /// </summary>
/// <param name="csvPath"></param>
/// <returns></returns>
public static void ImportCSV(DataGridView dgv)
{
string filePath;
OpenFileDialog openFileDialog = new OpenFileDialog();
= "CSV files (*.csv)|*.csv";
= 0;
if (() == )
{
filePath = ;
}
else
{
return;
}
dt = new ();
using (StreamReader sr = new StreamReader(filePath))
{
string[] headers = ().Split(',');
string headerValue = null;
foreach (string header in headers)
{
headerValue = ("\"", "");
(headerValue);
}
while (!)
{
string[] rows = ().Split(',');
DataRow dr = ();
for (int i = 0; i < ; i++)
{
dr[i] = rows[i].Replace("\"", "");
}
(dr);
}
}
= dt;
}DateGridView exports csv file
/// <summary>
/// Export DateGridView to Excel in csv format, general /// </summary>
/// <param name="dgv"></param>
public static void ExportToCSV(DataGridView dgv)
{
string filePath;
SaveFileDialog saveFileDialog = new SaveFileDialog();
= "CSV files (*.csv)|*.csv";
= 0;
if (() == )
{
filePath = ;
}
else
{
return;
}
using (StreamWriter sw = new StreamWriter(filePath))
{
// Write column title string headers = (",", <DataGridViewColumn>().Select(column => "\"" + + "\"").ToArray());
(headers);
// Write data rows foreach (DataGridViewRow row in )
{
string line = (",", <DataGridViewCell>().Select(cell => "\"" + ?.ToString().Replace("\"", "\"\"") + "\"").ToArray());
(line);
}
}
}This is the end of this article about the various ways to operate Excel in C#. For more related content on C#, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!