/// <summary>
/// 批量导出数据到excel
/// </summary>
/// <param name="dataSet"></param>
/// <param name="isShowExcle"></param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
{
System.Data.DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
return false;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}
try
{
// 写入Excel
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
}
catch (Exception ex)
{
}
finally
{
// 释放Workbook对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
workbook = null;
worksheet = null;
range = null;
excel = null;
GC.Collect();
}
return true;
}
/// <summary>
/// 批量导入sql sever
/// </summary>
/// <param name="dt"></param>
private static void DataTableToSQLServer(System.Data.DataTable dt)
{
using (SqlConnection destinationConnection = new SqlConnection(constr))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
bulkCopy.DestinationTableName = "zwb_test";//要插入的表的表明
//bulkCopy.ColumnMappings.Add("序号", "Email");//映射字段名 DataTable列名 ,数据库 对应的列名
bulkCopy.ColumnMappings.Add("项目", "xm_name");
bulkCopy.ColumnMappings.Add("PK代码", "dx");
bulkCopy.ColumnMappings.Add("电池档位", "mz_ch");
bulkCopy.ColumnMappings.Add("电池条码", "dctm");
bulkCopy.ColumnMappings.Add("PACK号", "demo");
bulkCopy.ColumnMappings.Add("对应工单", "gd_no");
bulkCopy.ColumnMappings.Add("rb", "rb");
bulkCopy.ColumnMappings.Add("建档日期", "jdrq");
bulkCopy.WriteToServer(dt);
MessageBox.Show("ok");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
destinationConnection.Close();
}
}
}
}
C#excel、sql sever批量数据的导入导出
最新推荐文章于 2021-05-25 20:16:26 发布