C# 读写Excel的一些方法,Aspose.Cells.dll
需求：现有2个Excel，一个7000，一个20W，7000在20W是完全存在的。现要分离20W的，拆分成19W3和7000。
条件：两个Excel都有“登录名”，然后用“登录名”去关联2个Excel
public void Excel()
{
//获取第一个Excel,20W
string filePath = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试20W.xlsx";
System.Data.DataTable table = GetTableFromExcel("sheet1", filePath);
//克隆
System.Data.DataTable table20W_new = table.Clone();
System.Data.DataTable table7000_new = table.Clone();
//获取第二个Excel,7000
string filePath_7000 = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试7000.xls";
System.Data.DataTable table_7000 = GetTableFromExcel("sheet1", filePath_7000);
//循环20W人中的挑出来
for (int i = 0; i < table.Rows.Count; ++i)
{
//20W
DataRow dateRow = table.Rows[i];
string login_name = dateRow["登录名"].ToString();
//7000
DataRow[] drss = table_7000.Select("登录名 = '" + login_name + "'");
if (drss.Length > 0)
{
table7000_new.ImportRow(dateRow);
}
else
{
table20W_new.ImportRow(dateRow);
}
}
//导出Excel
DataTableExport(table7000_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/7000.xlsx");
DataTableExport(table20W_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/22W.xlsx");
}
获取Excel内容，转成DataTable。
/// <summary>
/// 获取Excel内容。
/// </summary>
/// <param name="sheetName">工作表名称，例：sheet1</param>
/// <param name="filePath">Excel路径</param>
/// <returns></returns>
private DataTable GetTableFromExcel(string sheetName, string filePath)
{
const string connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\"";
DataTable dt = null;
if (!System.IO.File.Exists(filePath))
{
// don't find file
return null;
}
OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
try
{
conn.Open();
if (sheetName == null || sheetName.Trim().Length == 0)
{
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
}
else
{
sheetName += "$";
}
string strSQL = "Select * From [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return dt;
}
将DataTable的数据写进Excel里(用的Aspose.Cells.dll)
/// <summary>
/// DataTable数据导出Excel
/// </summary>
/// <param name="data"></param>
/// <param name="filepath"></param>
public static void DataTableExport(DataTable data, string filepath)
{
try
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
int Colnum = data.Columns.Count;//表格列数
int Rownum = data.Rows.Count;//表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(data.Columns[i].ColumnName);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[1 + i, k].PutValue(data.Rows[i][k].ToString());
}
}
book.Save(filepath);
GC.Collect();
}
catch (Exception e)
{
logger.Error("生成excel出错：" + e.Message);
}
}
将List的数据写进Excel里(用的Aspose.Cells.dll)
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">Ilist集合</param>
/// <param name="filepath">保存的地址</param>
public static void Export<T>(IList<T> data, string filepath)
{
try
{
Workbook workbook = new Workbook();
Worksheet sheet = (Worksheet)workbook.Worksheets[0];
PropertyInfo[] ps = typeof(T).GetProperties();
var colIndex = "A";
foreach (var p in ps)
{
// sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称 要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称
int i = 1;
foreach (var d in data)
{
sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
i++;
}
colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头 A1/A2/
}
//workbook.Shared = true;
workbook.Save(filepath);
GC.Collect();
}
catch (Exception e)
{
logger.Error("生成excel出错：" + e.Message);
}
}
/// <summary>
/// 生成新的对应的列 A-Z AA-ZZ
/// </summary>
/// <param name="top">当前列</param>
/// <returns></returns>
private static string getxls_top(string top)
{
char[] toplist = top.ToArray();
var itemtop = top.Last();
string topstr = string.Empty;
if ((char)itemtop == 90)//最后一个是Z
{
if (toplist.Count() == 1)
{
topstr = "AA";
}
else
{
toplist[0] = (char)(toplist[0] + 1);
toplist[toplist.Count() - 1] = 'A';
foreach (var item in toplist)
{
topstr += item.ToString();
}
}
}
else//最后一个不是Z 包括top为两个字符
{
itemtop = (char)(itemtop + 1);
toplist[toplist.Count() - 1] = itemtop;
foreach (var item in toplist)
{
topstr += item.ToString();
}
}
return topstr;
}
将DataTable的数据写进Excel里(用的Microsoft.Office.Interop.Excel.dll)(此方法在大量数据的时候很慢，例如22W条数据，建议使用Aspose.Cells.dll，速度快很多)
/// <summary>
/// 将DataTable的数据写进Excel里
/// </summary>
/// <param name="tmpDataTable">DataTable数据</param>
/// <param name="strFileName">Excel路径</param>
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
//需要引用Microsoft.Office.Interop.Excel.dll
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
xlBook.SaveCopyAs(strFileName);
}
原生的DataTable生成Excel(无需引用第三方dll)
/// <summary>
/// 将DataTable的数据写进Excel里
/// </summary>
/// <param name="tdKeChengZhuanJiaTongJi">DataTable</param>
/// <param name="sheet">sheet自定义名称</param>
/// <param name="fileName">Excel路径</param>
public static void DataTabletoExcel(DataTable dt, string sheet, string fileName)
{
String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;\"";
//string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;'";
//String sConnectionString = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + fileName + ";Extended Properties=\"Excel 14.0 Xml;\"";
//String sConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" + fileName + ";Extended Properties=\"Excel 16.0 Xml;HDR=YES;\"";
OleDbConnection cn = new OleDbConnection(sConnectionString);
int rowNum = dt.Rows.Count;//获取行数
int colNum = dt.Columns.Count;//获取列数
string sqlText = "";//带类型的列名
string sqlValues = "";//值
string colCaption = "";//列名
for (int i = 0; i < colNum; i++)
{
if (i != 0)
{
sqlText += " , ";
colCaption += " , ";
}
sqlText += "[" + dt.Columns[i].Caption.ToString() + "] VarChar";//生成带VarChar列的标题
colCaption += "[" + dt.Columns[i].Caption.ToString() + "]";//生成列的标题
}
try
{
//打开连接
cn.Open();
string sqlCreate = "CREATE TABLE [" + sheet.ToString() + "] (" + sqlText + ")";
OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
//创建Excel文件
cmd.ExecuteNonQuery();
for (int srow = 0; srow < rowNum; srow++)
{
sqlValues = "";
for (int col = 0; col < colNum; col++)
{
if (col != 0)
{
sqlValues += " , ";
}
sqlValues += "'" + dt.Rows[srow][col].ToString() + "'";//拼接Value语句
}
String queryString = "INSERT INTO [" + sheet.ToString() + "] (" + colCaption + ") VALUES (" + sqlValues + ")";
cmd.CommandText = queryString;
cmd.ExecuteNonQuery();//插入数据
}
}
catch
{
//生成日志
}
finally
{
cn.Close();
}
}
