When we are doing excel data, we usually have the following methods.
1. Common methods for importing and exporting excel:
1. Query and show on the dataset control using the query table.
public static string strCon = " Provider = .4.0 ; Data Source =C:\\;Extended Properties=Excel 8.0";
public static DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(strCon);
string sql = "select * from [Sheet1$]";
();
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
ds = new DataSet();
(ds, "[Sheet1$]");
();
= "[Sheet1$]";
= ds;
();
// excel = new ();
//(true);
// = true;
}
2. Insert cells one by one
string str = @"Data Source=IT-428E4EA4B0C7\SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True";
SqlConnection conn = new SqlConnection(str);
();
int n = 0;
for (int i = 1; i < 20; i++)
{
if (n > 3)
break;
else
if (.get_Range("A" + i, ).() == "" && n <= 3)
{ n++; }
else
{
//Loop to get the value of the excel cell and insert it again and again. ExcutSql is the stored procedure executed excuteSql(.get_Range("B" + i, ).(),
.get_Range("B" + (i + 1), ).(),
.get_Range("B" + (i + 2), ).(),
conn);
i = i + 3;
}
}
();
2. Quick import and export
1. We all know that when we batch insert data into db, we will choose SqlBulkCopy
if (dataTable!=null && !=0)
{
(dataTable);
}
This can be read a new article published by the old forest in the mountainsPerformance comparison of batch data insertion method in SQLServer
The following is the SqlBulkCopy method. One disadvantage of this method is that when a certain column of Excel has text and dates, a null value will appear. I searched some information online and said that when the connection string is added; HDR=YES; IMEX=1', it will be treated as characters, but there will still be some bugs. Therefore, it is recommended that you analyze the excel data into the datatable first and then pour it into the database with SqlBulkCopy.
// block copy to DB from Excel
//By xijun,
//step create an excel file C:\Inetpub\wwwroot\ , fill cell(1,1) with "Data",cell(1,2) with "name"
//step create table named "Data" with column ("data","name") in your DB
//there the code below:
DateTime t1 = ;
("<br>start time:" + ());
string ExcelFile = @"C:\\_Hub_Report.xls";
string excelConnectionString = @"Provider=.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel.0;HDR=YES;IMEX=1'";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
();
//Getting source data
//Non-empty data OleDbCommand command = new OleDbCommand("Select [Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull] FROM [_Hub_Report$] ", excelConnection);
// Initialize SqlBulkCopy object
using (OleDbDataReader dr = ())
{
// Copy data to destination
string sqlConnectionString = @"Data Source=MININT-GPHNA\SQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True";
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
= "GDS_Hub_data";
//If you add only one column, then the other database columns will default to empty. ("Region", "region");
("CustomerPN", "customer_item_number");
("RMA", "Rma");
("Date", "date");
("QTY", "Qty_1");
("Return/Pull", "return_pull");
// =;//Number of rows per transmission // =;// Number of rows for progress prompts =;
=;
((IDataReader)dr);
}
}
//Closing connection
();
}
DateTime t2 = ;
("<br>End time:" + ());
("<br>use time:" + ((TimeSpan)(t2 - t1)).() + " Milliseconds");
("<br>inser record count :");
2. Quickly export db data to excel
This method is to use
Querytable quickly imports data into excel. When we make complex reports, we use this a lot, but it is useless just because this is not useful. It just quickly puts the data in the db into Excel. When we make a lot of data and need to set an excel style, we will choose to use this method to import the data into Excel for a temporary sheet, and then use sheet copying, sheet movement, some excel style settings, and a powerful automatic filling function of Excel. Then these can allow us to quickly make various excel reports. Of course, this requires us to be more proficient in office operations, including macro operations.
public string query_table_getdata(string sourpath)
{
string str_path = (0, - 22);
str_path = str_path + "";
m_objQryTables = null;
m_objQryTable = null;
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
m_objSheets = null;
m_objSheet = null;
m_objRange = null;
m_objExcel = new ();
//try
//{
m_objBooks = m_objExcel.Workbooks;
m_objBooks.Open(sourpath, , , , ,
, , , , , , , , , );
m_objBook = ()m_objBooks.get_Item(1);
m_objSheets = ()m_objBook.Worksheets;
m_objSheet = ()m_objSheets.get_Item(1);
m_objRange = m_objSheet.get_Range("A2", );
m_objQryTables = m_objSheet.QueryTables;
string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
//Can be placed in the configuration file string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA\SQLEXPRESS;uid=xijun_ke;Password=12345678;
Initial Catalog=GDS_Service;Persist Security Info=False;";
m_objQryTable = ()m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);
m_objQryTable.RefreshStyle = ;
m_objQryTable.Refresh(false);
m_objBook.SaveAs(str_path, , , , , , , , , , , );
m_objBook.Close(false, , );
//}
//catch (Exception ee)
//{
// (());
//}
//finally
//{
m_objExcel.Quit();
();
//}
return str_path;
}
Some features of com operation excel:
= "@"; //Set the cell format to textrange = (Range)worksheet.get_Range("A1", "E1"); //Get multiple cell areas of Excel: This example is used as the Excel table header(0); //Cell merge action[1, 1] = "Excel cell assignment"; //Excel cell assignment = 15; //Set font size=true; //Set whether the font has underline="Bold"; Set the type of font
=; //Set the way the font is used in the cell=15; //Set the width of the cell=(255,204,153).ToArgb(); //Set the background color of the cell=1; //Set the thickness of the cell border(,,,()); //Add a border to the cell(); //Automatically adjust column width= xlCenter; // Text horizontal centering method= xlCenter //The vertical centering method of text=true; // Text wrapping automatically=39; //Fill color is lilac=clBlue; //Font color=false; // When saving Excel, save it directly without popping up a window of whether to save it (temp);/**////After filling in the information, save it to the path and file name
Excel macro operations, sheet and cell operations:
/// <summary>
/// Read excel data and insert formula /// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
DateTime t1 = ;
("<br>start time:" + ());
excelkk = new ();
(true);
int row = 2;
DataTable myTable = ["[Sheet1$]"];
for (int i = 0; i < ; i++)
{
[1, 1 + i] = [i].();
}
for (int i = 0; i < ; i++)
{
for (int j = 0; j < ; j++)
{
[row, j + 1] = [i][j].ToString();
}
row++;
}
//Get the value of a specific cell = true;
= excelkk.get_Range("A2", ).();
//Combination of single cells of table range1 = excelkk.get_Range("A2", "D4");
();
//I want to insert sum value in the table range2 = excelkk.get_Range("B25", );
= "=SUM(B2:B24)";
();
//Change the macros and delete and add single cells, and multiple single cells default to macros automatically. range3 = excelkk.get_Range("B25","E25");
(range3,);
//Delete the specified row count operation of the table range7 = null;
range7 = excelkk.get_Range([2, 2], [4, 4]);
();
();
//Get the maximum number of used lines wsheet1 = ().get_Item(1);
int n =;
(() + "<br>");
//(());
n = ;
(() + "<br>");
//(());
//Copy data range4 = excelkk.get_Range("A2", "B25");
range5 = excelkk.get_Range("E3", "F25");
//range4.get_Offset(1,4).Select();
(range5);
//Disable warning message = false;
();
}
SinglesheetRicohn：
range2 = excelkk.get_Range("B25", );
= "=SUM(B2:B24)";
();
CrosssheetAsk for sum：
wsheet1 = ().get_Item(1);
range3 =wsheet1.get_Range("A23", );
= "=Sheet3!B8+Sheet3!B12";
();
Although we have a strong NPOI, I still write it down, hoping that it will be of some use to everyone's understanding of office.
The above is a comparison of C#'s various methods of operating excel introduced to you. I hope it will be helpful to you. Thank you very much for your support for my website!