private Excel.Range GetSortedRange(ExcelInterface excel)
{
Excel.Range range = excel.GetWorksheet().UsedRange;
range.Rows[range.Rows.Count].Delete(); // 删除最后的合计行
range.Sort(range.Columns[1], Header: Excel.XlYesNoGuess.xlYes); // 对除标题行外的其他行排序
return(range);
}
/* 把公司信息表格中的内容写入数据库
* private void Company_Button_Click(object sender, RoutedEventArgs e)
* {
* if (filenameCompanyInfo_ == null)
* {
* textBoxMessage.Text = "请先用浏览键选择一个包含公司信息的Excel文件";
* return;
* }
*
* ExcelInterface excel = new ExcelInterface(filenameCompanyInfo_, 'r');
* Excel.Range range = excel.GetWorksheet().UsedRange;
* int row = range.Rows.Count;
* SqlCeConnection connection = database_.GetConnection();
* using (var cmd = new SqlCeCommand("DELETE CompanyInfo", connection))
* {
* cmd.ExecuteNonQuery();
* }
*
* using (var cmd = new SqlCeCommand("INSERT INTO CompanyInfo VALUES (?, ?, ?, ?, ?, ?)", connection))
* {
* cmd.Parameters.Add("@id", SqlDbType.NVarChar);
* cmd.Parameters.Add("@name", SqlDbType.NVarChar);
* cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
* cmd.Parameters.Add("@union", SqlDbType.NVarChar);
* cmd.Parameters.Add("@system", SqlDbType.NVarChar);
* cmd.Parameters.Add("@industry", SqlDbType.NVarChar);
*
* SqlCeTransaction transaction = connection.BeginTransaction();
* cmd.Transaction = transaction;
* try
* {
* for (int rCnt = 2; rCnt <= row; rCnt++)
* {
* for (int cCnt = 1; cCnt <= 6; cCnt++)
* {
* // 如果excel cell为空，则把dbnull.value写入数据库
* cmd.Parameters[cCnt - 1].Value =
* (range.Cells[rCnt, cCnt] as Excel.Range).Value ?? DBNull.Value;
* }
* cmd.ExecuteNonQuery();
* }
* transaction.Commit();
* UpdateCategories();
* textBoxMessage.Text = "更新成功，数据库现有" + (row - 1) + "条公司信息";
* }
* catch (Exception ex)
* {
* transaction.Rollback();
* textBoxMessage.Text = "更新失败，提供的Excel文件存在问题。详细信息：" + ex.Message;
* }
* }
* excel.Close();
* }*/
// 把经费信息表格中的内容写入数据库
private void Add_Funds_Button_Click(object sender, RoutedEventArgs e)
{
if (filenameFunds_ == null)
{
textBoxMessage.Text = "请先用浏览键选择一个包含经费信息的Excel文件";
return;
}
ExcelInterface excel = new ExcelInterface(filenameFunds_, 'r');
Excel.Range range = GetSortedRange(excel);
int row = range.Rows.Count;
SqlCeConnection connection = database_.GetConnection();
DateTime time = GetTimeFromComboBox(comboBoxInputYear, comboBoxInputMonth);
using (var cmd = new SqlCeCommand("DELETE FROM Funds WHERE Time = @time", connection))
{
cmd.Parameters.AddWithValue("@time", time);
cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCeCommand("INSERT INTO Funds VALUES (?, ?, ?)", connection))
{
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
cmd.Parameters.Add("@time", SqlDbType.DateTime);
cmd.Parameters.Add("@received", SqlDbType.Float);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
float totalReceived = 0; // 相邻行如果是同一公司则把几个经费数字加起来
for (int rCnt = 2; rCnt <= row; rCnt++)
{
string id = (string)(range.Cells[rCnt, 1] as Excel.Range).Value;
totalReceived += (float)(range.Cells[rCnt, 5] as Excel.Range).Value;
if (rCnt == row || id != (string)(range.Cells[rCnt + 1, 1] as Excel.Range).Value)
{
cmd.Parameters[0].Value = id;
cmd.Parameters[1].Value = time;
cmd.Parameters[2].Value = totalReceived;
cmd.ExecuteNonQuery();
totalReceived = 0;
}
}
transaction.Commit();
textBoxMessage.Text = "成功添加" + time.ToString(timeFormat_) + (row - 1) + "条经费信息";
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.Text = "添加失败，提供的Excel文件存在问题，请确保文件最后的合计行之后没有多余的内容。详细信息：" + ex.Message;
}
}
excel.Close();
}
// 生成全部公司按工会分组的报表
private void GroupByUnion(DateTime time)
{
ExcelInterface excel = new ExcelInterface(null, 'w');
GroupByUnionFillCells(excel.GetWorksheet().Cells, time);
GroupByUnionStyleCells(excel.GetWorksheet().Cells);
string filename = Path.Combine(Directory.GetCurrentDirectory(),
time.ToString(timeFormat_) + "各县（市）区总工会经费收解返计算表.xls");
excel.Save(filename);
excel.Close();
}
// 生成总工会所属公司按税务局分组的报表
private void GroupByTaxAuthority(DateTime time)
{
ExcelInterface excel = new ExcelInterface(null, 'w');
FillCells(excel.GetWorksheet().Cells, time, "三门峡市总工会", "TaxAuthority");
StyleCells(excel.GetWorksheet().Cells);
string filename = Path.Combine(Directory.GetCurrentDirectory(),
time.ToString(timeFormat_) + "工会经费征收明细.xls");
excel.Save(filename);
excel.Close();
}
// 根据经费信息表格自动更新公司信息
private void Auto_Update_Company_Button_Click(object sender, RoutedEventArgs e)
{
if (filenameFunds_ == null)
{
textBoxMessage.Text = "请先用浏览键选择一个包含经费信息的Excel文件";
return;
}
ExcelInterface excel = new ExcelInterface(filenameFunds_, 'r');
Excel.Range range = GetSortedRange(excel);
int row = range.Rows.Count;
var toInsert = new List <Company>();
var toUpdate = new List <Company>();
using (var cmd = new SqlCeCommand("SELECT TaxAuthority FROM CompanyInfo WHERE CompanyId = @id", database_.GetConnection()))
{
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
for (int rCnt = 2; rCnt <= row; rCnt++)
{
var item = new Company();
item.id = (string)(range.Cells[rCnt, 1] as Excel.Range).Value;
item.name = (string)(range.Cells[rCnt, 2] as Excel.Range).Value;
item.tax = (string)(range.Cells[rCnt, 6] as Excel.Range).Value;
if (rCnt < row && item.id == (string)(range.Cells[rCnt + 1, 1] as Excel.Range).Value)
{
continue;
}
DecideInsertOrUpdate(cmd, item, toInsert, toUpdate);
}
}
excel.Close();
InsertNewCompanies(toInsert);
UpdateExistingCompanies(toUpdate);
UpdateCategories();
}
