C# 使用EPPlus处理Excel文件

EPPlus使用前需要先标明使用类型

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

读取Excel到DataTable

try
{
    ExcelPackage xlsx1 = new ExcelPackage(xls1path1);
    ExcelPackage xlsx2 = new ExcelPackage(xls2path2);

    ExcelWorksheet sheet1 = xlsx1.Workbook.Worksheets.FirstOrDefault();
    ExcelWorksheet sheet2 = xlsx2.Workbook.Worksheets.FirstOrDefault();

    if (sheet1 == null || sheet2 == null) throw new Exception("no sheet data in xlsx");
    dt_hotelbill = LoadXlsxToDataTable(sheet1);
    dt_allowance = LoadXlsxToDataTable(sheet2);
}
catch (Exception ex)
{
    Console.WriteLine($"error:{ex.Message}");
    MessageBox.Show($"提示：{ex.Message} \n错误：{ex.StackTrace}", "ERROR");
}

public DataTable LoadXlsxToDataTable(ExcelWorksheet ws)
{
    string lastcolumn = ws.Columns.Range.LocalAddress.Split(':')[1];
    int lastrowno = ws.Cells.Where(x => x.Start.Column == 1).LastOrDefault().Start.Row;
    DataTable dt = ws.Cells[$"A1:{lastcolumn}{lastrowno}"].ToDataTable();
    return dt;
}

使用sqlite存储数据，并对数据进行处理

SQLiteConnection dbconn = new SQLiteConnection("Data Source=local.db;version=3");
//从datatable读取数据插入到数据库中，列名需完全相同
ClearTable(dbconn, "ALLOWANCE");
ClearTable(dbconn, "HOTELBILL");
InsertDataTable(dbconn, "ALLOWANCE", dt_allowance);
InsertDataTable(dbconn, "HOTELBILL", dt_hotelbill);
//从ALLOWANCE和HOTELBILL中加载处理过的数据进summury表。
string resultQuery = LoadSqlFile("query.sql");
ClearTable(dbconn, "summury");
ExecuteSql(dbconn, resultQuery);
//从summry结果表中读取数据到DataTable
LoadDataToDT(dbconn, "summury", ref dt_summary);

public string LoadSqlFile(string filename)
{
    if (!File.Exists(filename))
        throw new Exception($"未找到SqlFile:{filename}");
    string ret = "";
    string[] lines = File.ReadAllLines(filename);
    foreach(string line in lines)
    {
        ret += line;
    }
    return ret;
}

public void ExecuteSql(SQLiteConnection conn, string sql)
{
	if (conn.State != System.Data.ConnectionState.Open)
		conn.Open();
	SQLiteCommand cmd = conn.CreateCommand();
	cmd.CommandText = sql;
	cmd.ExecuteNonQuery();
	conn.Close();
}

public void ClearTable(SQLiteConnection conn, string tablename)
{
	if (conn.State != System.Data.ConnectionState.Open)
		conn.Open();
	SQLiteCommand cmd = conn.CreateCommand();
	cmd.CommandText = $"delete from {tablename}";
	cmd.ExecuteNonQuery();
	conn.Close();
}

public void InsertDataTable(SQLiteConnection conn, string tablename, DataTable dt)
{
	if (conn.State != System.Data.ConnectionState.Open)
		conn.Open();
    List<string> colnames = new List<string>();
    List<string> sqlvarnames = new List<string>();
    foreach(DataColumn col in dt.Columns)
    {
        colnames.Add(col.ColumnName);
        sqlvarnames.Add("@" + col.ColumnName);
    }
    string sql = $"INSERT INTO {tablename}({string.Join(",", colnames.ToArray())}) ";
    sql += $"VALUES({string.Join(",", sqlvarnames.ToArray())});";

    SQLiteCommand cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    foreach(DataRow row in dt.Rows)
    {
        for(int i = 0; i < colnames.Count; i++)
        {
            string paraName = sqlvarnames[i];
            object o = row[colnames[i]];
            cmd.Parameters.AddWithValue(paraName, o);
        }
        cmd.ExecuteNonQuery();
    }
    conn.Close();
}

public void LoadDataToDT(SQLiteConnection conn, string tablename, ref DataTable dt)
{
	if (conn.State != System.Data.ConnectionState.Open)
		conn.Open();
    SQLiteCommand cmd = conn.CreateCommand();
    cmd.CommandText = $"select * from {tablename}";
    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
    dt.Clear();
    adapter.Fill(dt);
    conn.Close();
}

写入Excel

try
{
    //将DataTable数据加载并保存为新excel
    ExcelPackage newpack = new ExcelPackage();
    ExcelWorksheet newsheet = newpack.Workbook.Worksheets.Add("sheet1");
    newsheet.Cells["A1"].LoadFromDataTable(dt_summary, true); //从DataTable读取数据到sheet1,第二个PrintHeaders参数指定是否包含标题
    newsheet.Cells.AutoFitColumns();
    int endcol = newsheet.Cells.LastOrDefault().Start.Column;
    newsheet.Cells[1,1,1,endcol].Style.Font.Bold = true;
    newpack.SaveAs(m_Workpath + m_OutputFilename);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    MessageBox.Show($"提示：{ex.Message} \n错误：{ex.StackTrace}", "ERROR");
}

附:query.sql中的数据整理语句

insert into summury 
select 
	姓名, 
	出差日期 as 日期, 
	case 
		when 费用归属 = 'MT00002' then 差补金额
		else 差补金额*2.5 
	end as 差旅补助,
	房间间夜金额 as 酒店费用,
	case 
		when 费用归属 = 'MT00002' then 差补金额
		else 差补金额*2.5 - 房间间夜金额
	end	as 应付差补,
	费用归属 as 项目号 
from (
		select 
			a.结算编号       as 结算编号      ,
			a.结算状态       as 结算状态      ,
			a.结算月份       as 结算月份      ,
			a.结算总额       as 结算总额      ,
			a.提交结算日期   as 提交结算日期  ,
			a.确认结算日期   as 确认结算日期  ,
			a.姓名           as 姓名          ,
			a.工号           as 工号          ,
			a.法人实体       as 法人实体      ,
			a.职级           as 职级          ,
			a.部门           as 部门          ,
			a.结算天数       as 结算天数      ,
			a.差补总额       as 差补总额      ,
			replace(a.出差日期,'/','-')       as 出差日期      ,
			a.日期属性       as 日期属性      ,
			a.出差状态       as 出差状态      ,
			a.差补城市       as 差补城市      ,
			a.差补类型       as 差补类型      ,
			a.差补方案       as 差补方案      ,
			a.差补金额       as 差补金额      ,
			TRIM(a.费用归属)       as 费用归属      ,
			a.差标金额       as 差标金额      ,
			a.额外扣减项总额 as 额外扣减项总额,
			a.额外补贴项总额 as 额外补贴项总额,
			a.报销单编号     as 报销单编号    ,
			a.系统计算金额   as 系统计算金额  ,
			a.员工确认状态   as 员工确认状态  ,
			a.修改事由       as 修改事由      ,
			a.统计来源 		 as 统计来源      ,
			
			
			b.出行人         as 出行人        ,
			b.入住日期       as 入住日期      ,
			b.订单金额       as 订单金额      ,
			ROUND(COALESCE(b.房间间夜金额,0.0), 2)   as 房间间夜金额  ,
			b.入住事由       as 入住事由      					
		from ALLOWANCE a
		left join (
					select 
						b.出行人 as 出行人,
						replace(b.入住日期,'/','-') as 入住日期,
						sum(b.订单金额) as 订单金额,
						sum(b.房间间夜金额) as 房间间夜金额,
						(
						select 
							rz.入住事由 
						from HOTELBILL rz 
						where rz.出行人 = b.出行人 and rz.入住日期 = b.入住日期 
						limit 1
						)  as 入住事由
					from HOTELBILL b 
					group by b.出行人, b.入住日期
				) b
		on a.姓名 = b.出行人 and date(replace(a.出差日期,'/','-')) = date(replace(b.入住日期,'/','-'))
	)
order by 姓名, 日期;