private void btPrint_Click(object sender, EventArgs e)
{
this.lblMsg.Text = string.Empty;
if (string.IsNullOrWhiteSpace(tbId.Text))
{
this.lblMsg.Text = "身份证号不能为空。";
return;
}
if (string.IsNullOrWhiteSpace(tbYM.Text))
{
this.lblMsg.Text = "月份不能为空。";
return;
}
DateTime dtYM = new DateTime();
if (!DateTime.TryParseExact(tbYM.Text,
"yyyyMM",
System.Globalization.CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
out dtYM))
{
this.lblMsg.Text = "月份输入格式不正确。";
return;
}
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnectionString);
try
{
SalaryManagementDataSetTableAdapters.StaffTableAdapter stAdapter = new SalaryManagementDataSetTableAdapters.StaffTableAdapter();
SalaryManagementDataSet.StaffDataTable sDataTable = stAdapter.GetDataBy公民身份证号(tbId.Text);
if (sDataTable.Rows.Count == 0)
{
this.lblMsg.Text = "身份证号" + tbId.Text + "不存在。";
return;
}
SalaryManagementDataSetTableAdapters.SalarySettingTableAdapter sstAdapter = new SalaryManagementDataSetTableAdapters.SalarySettingTableAdapter();
SalaryManagementDataSet.SalarySettingDataTable ssDataTable = sstAdapter.GetData();
IList<SalarySetting> salarySettingList = new List<SalarySetting>();
foreach (DataRow dr in ssDataTable)
{
SalarySetting ss = new SalarySetting();
ss.Item = (string)dr[1];
ss.ItemType = (string)dr[2];
ss.Seq = (int)dr[4];
salarySettingList.Add(ss);
}
OleDbDataAdapter dbAdapter = new OleDbDataAdapter("select a.* from Salary as a inner join Staff as b on a.StaffId = b.Id where a.YM = '" + dtYM.ToString("yyyyMM") + "' and b.公民身份证号 = '" + tbId.Text + "'", conn);
DataSet dataSet = new DataSet();
conn.Open();
dbAdapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count == 0)
{
this.lblMsg.Text = "身份证号" + tbId.Text + "月份" + dtYM.ToString("yyyyMM") + "的工资数据不存在。";
return;
}
IList<PrintCell> printCellList = new List<PrintCell>();
for (int i = 3; i < dataSet.Tables[0].Columns.Count; i++)
{
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
if (dr[i] != null && (decimal)dr[i] != 0)
{
PrintCell pc = new PrintCell();
pc.StaffId = (int)sDataTable.Rows[0][0];
pc.YM = dtYM.ToString("yyyyMM");
pc.Item = ssDataTable.Where(ss => ss.列名 == dataSet.Tables[0].Columns[i].ColumnName).Single().项目;
pc.ItemType = ssDataTable.Where(ss => ss.列名 == dataSet.Tables[0].Columns[i].ColumnName).Single().类型;
pc.Amount = (decimal)dr[i];
printCellList.Add(pc);
}
}
}
string tempFileName = System.IO.Path.GetTempFileName();
FileStream fileStream = new FileStream(tempFileName, FileMode.Create);
int rowIndex = 0;
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
IRow rowHead = sheet.CreateRow(rowIndex);
rowHead.HeightInPoints = 30;//行高
ICell cell = rowHead.CreateCell(0);
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 8);
sheet.AddMergedRegion(cellRangeAddress);
cell.SetCellValue(dtYM.ToString("yyyy") + "年" + dtYM.ToString("MM") + "月份工资单");
ICellStyle style = hssfworkbook.CreateCellStyle();
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = (short)16;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
cell.CellStyle = style;
#region 打印人员基本信息
#region 头
rowIndex++;
rowHead = sheet.CreateRow(rowIndex);
rowHead.CreateCell(0).SetCellValue("编号");
rowHead.CreateCell(1).SetCellValue("人员类别");
rowHead.CreateCell(2).SetCellValue("姓 名");
rowHead.CreateCell(3).SetCellValue("结算部门");
rowHead.CreateCell(4).SetCellValue("职务等级");
rowHead.CreateCell(5).SetCellValue("军衔级别");
rowHead.CreateCell(6).SetCellValue("储蓄账号");
#endregion
#region 内容
rowIndex++;
IRow rowContent = sheet.CreateRow(rowIndex);
rowContent.CreateCell(0).SetCellValue("1");
rowContent.CreateCell(1).SetCellValue((string)sDataTable.Rows[0][2]);
rowContent.CreateCell(2).SetCellValue((string)sDataTable.Rows[0][3]);
rowContent.CreateCell(3).SetCellValue((string)sDataTable.Rows[0][4]);
rowContent.CreateCell(4).SetCellValue((string)sDataTable.Rows[0][14]);
rowContent.CreateCell(5).SetCellValue((string)sDataTable.Rows[0][15]);
rowContent.CreateCell(6).SetCellValue((string)sDataTable.Rows[0][36]);
cellRangeAddress = new CellRangeAddress(2, 2, 6, 8);
sheet.AddMergedRegion(cellRangeAddress);
#endregion
#endregion
#region 打印应发
IList<string> printHeadList = new List<string>();
foreach (var ss in (from pc in printCellList
join ss in salarySettingList on pc.Item equals ss.Item into result
from r in result
where r.ItemType == "应发"
select new
{
r.Item,
r.ItemType,
r.Seq,
}).Distinct().OrderByDescending(ss => ss.ItemType).ThenBy(ss => ss.Seq))
{
printHeadList.Add(ss.Item);
}
int skipIndex = 0;
IList<string> thisPrintHeadList = printHeadList.Skip(skipIndex).Take(7).ToList();
decimal subTotal = 0;
decimal grandTotal = 0;
while (true)
{
rowIndex++;
rowHead = sheet.CreateRow(rowIndex);
rowIndex++;
rowContent = sheet.CreateRow(rowIndex);
int i = 0;
for (; i < thisPrintHeadList.Count; i++)
{
PrintCell printCell = printCellList.Where(pc => pc.Item == thisPrintHeadList[i]).SingleOrDefault();
rowHead.CreateCell(i).SetCellValue(thisPrintHeadList[i]);
rowContent.CreateCell(i).SetCellValue(printCell != null ? printCell.Amount.ToString("#.##") : string.Empty);
subTotal += printCell != null ? printCell.Amount : 0;
grandTotal += printCell != null ? printCell.Amount : 0;
}
skipIndex += thisPrintHeadList.Count();
thisPrintHeadList = printHeadList.Skip(skipIndex).Take(7).ToList();
if (thisPrintHeadList == null || thisPrintHeadList.Count == 0)
{
style = hssfworkbook.CreateCellStyle();
font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = (short)12;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
cell = rowHead.CreateCell(i);
cell.SetCellValue("应发");
cell.CellStyle = style;
cell = rowContent.CreateCell(i);
cell.SetCellValue(subTotal.ToString("#.##"));
cell.CellStyle = style;
break;
}
}
#endregion
#region 打印扣发
printHeadList = new List<string>();
foreach (var ss in (from pc in printCellList
join ss in salarySettingList on pc.Item equals ss.Item into result
from r in result
where r.ItemType == "扣发"
select new
{
r.Item,
r.ItemType,
r.Seq,
}).Distinct().OrderByDescending(ss => ss.ItemType).ThenBy(ss => ss.Seq))
{
printHeadList.Add(ss.Item);
}
skipIndex = 0;
thisPrintHeadList = printHeadList.Skip(skipIndex).Take(7).ToList();
subTotal = 0;
while (true)
{
rowIndex++;
rowHead = sheet.CreateRow(rowIndex);
rowIndex++;
rowContent = sheet.CreateRow(rowIndex);
int i = 0;
for (; i < thisPrintHeadList.Count; i++)
{
PrintCell printCell = printCellList.Where(pc => pc.Item == thisPrintHeadList[i]).SingleOrDefault();
rowHead.CreateCell(i).SetCellValue(thisPrintHeadList[i]);
rowContent.CreateCell(i).SetCellValue(printCell != null ? printCell.Amount.ToString("#.##") : string.Empty);
subTotal += printCell != null ? printCell.Amount : 0;
grandTotal -= printCell != null ? printCell.Amount : 0;
}
skipIndex += thisPrintHeadList.Count();
thisPrintHeadList = printHeadList.Skip(skipIndex).Take(7).ToList();
if (thisPrintHeadList == null || thisPrintHeadList.Count == 0)
{
cell = rowHead.CreateCell(i);
cell.SetCellValue("扣发");
cell.CellStyle = style;
cell = rowContent.CreateCell(i);
cell.SetCellValue(subTotal.ToString("#.##"));
cell.CellStyle = style;
break;
}
}
#endregion
#region 打印汇总
rowIndex++;
rowHead = sheet.CreateRow(rowIndex);
rowIndex++;
rowContent = sheet.CreateRow(rowIndex);
cell = rowHead.CreateCell(0);
cell.SetCellValue("实发工资");
cell.CellStyle = style;
cell = rowContent.CreateCell(0);
cell.SetCellValue(grandTotal.ToString("#.##"));
cell.CellStyle = style;
#endregion
hssfworkbook.Write(fileStream);
fileStream.Close();
PrinterHelper.PrintOrder(tempFileName, null);
}
catch (Exception ex)
{
this.lblMsg.Text = ex.Message;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
private void doUpload(string fileName)
{
using (FileStream stream = File.Open(fileName, FileMode.Open))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();
SalaryManagementDataSetTableAdapters.StaffTableAdapter adapter = new SalaryManagementDataSetTableAdapters.StaffTableAdapter();
bool hasError = false;
try
{
int rowCount = 0;
while (rows.MoveNext())
{
rowCount++;
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 1, 42))
{
break;//边界
}
#region 读取数据
string cell1 = ImportHelper.GetCellStringValue(row.GetCell(0)); //档案号
string cell2 = ImportHelper.GetCellStringValue(row.GetCell(1)); //人员类别
string cell3 = ImportHelper.GetCellStringValue(row.GetCell(2)); //姓名
if (string.IsNullOrWhiteSpace(cell3))
{
this.lblMsg.Text = "第" + rowCount.ToString() + "行姓名不能为空。";
hasError = true;
break;
}
string cell4 = ImportHelper.GetCellStringValue(row.GetCell(3)); //结算部门
string cell5 = ImportHelper.GetCellStringValue(row.GetCell(4)); //职务等级
string cell6 = ImportHelper.GetCellStringValue(row.GetCell(5)); //军衔级别
string cell7 = ImportHelper.GetCellStringValue(row.GetCell(6)); //公民身份证号
string cell8 = ImportHelper.GetCellStringValue(row.GetCell(7)); //储蓄账号
#endregion
SalaryManagementDataSet.StaffDataTable dataTable = new SalaryManagementDataSet.StaffDataTable();
adapter.FillBy档案号(dataTable, cell1);
if (dataTable != null && dataTable.Rows.Count > 0)
{
dataTable.Rows[0][2] = cell2;
dataTable.Rows[0][3] = cell3;
dataTable.Rows[0][4] = cell4;
dataTable.Rows[0][5] = cell5;
dataTable.Rows[0][6] = cell6;
dataTable.Rows[0][7] = cell7;
dataTable.Rows[0][8] = cell8;
adapter.Update(dataTable);
}
else
{
int idCount = (int)adapter.SelectCountBy公民身份证号(cell7);
if (idCount > 0)
{
this.lblMsg.Text = "第" + rowCount.ToString() + "行公民身份证号已经存在。";
hasError = true;
break;
}
adapter.Insert(cell1,
cell2,
cell3,
cell4,
cell5,
cell6,
cell7,
cell8);
}
}
if (!hasError)
{
this.lblMsg.Text = "员工基本信息导入成功。";
}
}
catch (Exception)
{
this.lblMsg.Text = "连接数据库失败。";
}
finally
{
adapter.Dispose();
InitCBDepartment();
doSearch();
}
}
}
private void doUpload(string fileName, string sYM)
{
using (FileStream stream = File.Open(fileName, FileMode.Open))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnectionString);
try
{
OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = conn;
dbCommand.CommandText = "select count(1) from Salary where YM = '" + sYM + "'";
conn.Open();
if ((int)dbCommand.ExecuteScalar() > 0)
{
DialogResult dlgRes = MessageBox.Show(
sYM + "员工工资数据已经存在，点击确认替换存在的数据？",
"替换确认",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (dlgRes == DialogResult.Yes)
{
dbCommand.CommandText = "delete from Salary where YM = '" + sYM + "'";
dbCommand.ExecuteNonQuery();
}
else
{
return;
}
}
#region 新增列
SalaryManagementDataSetTableAdapters.SalarySettingTableAdapter ssAdapter = new SalaryManagementDataSetTableAdapters.SalarySettingTableAdapter();
SalaryManagementDataSet.SalarySettingDataTable ssDataTable = new SalaryManagementDataSet.SalarySettingDataTable();
ssAdapter.Fill(ssDataTable);
DataRowCollection ssRowCollection = ssDataTable.Rows;
int? maxSeq = ssAdapter.SelectMaxSeqBy类型("应发");
OleDbDataAdapter dbAdapter = new OleDbDataAdapter("select * from Salary", conn);
DataSet dataSet = new DataSet();
dbAdapter.FillSchema(dataSet, SchemaType.Mapped);
DataColumnCollection columnCollection = dataSet.Tables[0].Columns;
int columnIndex = 7;
bool hasError = false;
string headType = "应发";
StringBuilder insertField = new StringBuilder("insert into Salary (YM, StaffId");
IList<int> effColumnList = new List<int>();
IList<string> addHeadNameList = new List<string>();
IList<string> addHeadFieldList = new List<string>();
while (ImportHelper.GetCellStringValue(row.GetCell(columnIndex)) != null)
{
string headName = ImportHelper.GetCellStringValue(row.GetCell(columnIndex));
//if (headList.Contains(headName))
//{
// this.lblMsg.Text = "第" + ImportHelper.GetExcelColumnName(columnIndex) + "列项目" + headName + "重复。";
// hasError = true;
// break;
//}
//else
//{
columnIndex++;
if (headName == "应发")
{
headType = "扣发";
maxSeq = ssAdapter.SelectMaxSeqBy类型("扣发");
continue;
}
else if (headName == "扣发")
{
continue;
}
else if (headName == "实发工资")
{
continue;
}
effColumnList.Add(columnIndex - 1);
//}
bool hasHead = false;
string headFieldName = null;
foreach (DataRow ssRow in ssRowCollection)
{
if ((string)ssRow[1] == headName || addHeadNameList.Contains(headName))
{
hasHead = true;
headFieldName = (string)ssRow[3];
break;
}
}
if (!hasHead)
{
if (!maxSeq.HasValue)
{
maxSeq = 0;
}
maxSeq++;
headFieldName = (headType + maxSeq);
addHeadNameList.Add(headName);
ssAdapter.Insert(headName, headType, headFieldName, maxSeq);
}
if (!columnCollection.Contains(headFieldName) && !addHeadFieldList.Contains(headFieldName))
{
addHeadFieldList.Add(headFieldName);
dbCommand.CommandText = "alter table Salary add column " + headFieldName + " decimal(18,8)";
dbCommand.ExecuteNonQuery();
}
insertField.Append(", " + headFieldName);
}
insertField.Append(")");
#endregion
#region
if (!hasError)
{
SalaryManagementDataSetTableAdapters.StaffTableAdapter stAdapter = new SalaryManagementDataSetTableAdapters.StaffTableAdapter();
int rowIndex = 1;
StringBuilder insertValue = null;
while (rows.MoveNext())
{
if (hasError)
{
break;
}
rowIndex++;
row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 1, columnIndex))
{
break;//边界
}
#region 读取数据
string nameCell = ImportHelper.GetCellStringValue(row.GetCell(2));
int? pId = stAdapter.SelectIdBy姓名(nameCell);
if (!pId.HasValue)
{
this.lblMsg.Text = "第" + rowIndex + "行员工姓名不存在。";
hasError = true;
break;
}
insertValue = new StringBuilder(" values ('" + sYM + "', " + pId); ;
foreach (int effColumnIndex in effColumnList)
{
string cell = ImportHelper.GetCellStringValue(row.GetCell(effColumnIndex));
decimal dCell = 0;
if (!string.IsNullOrWhiteSpace(cell))
{
if (!decimal.TryParse(cell, out dCell))
{
this.lblMsg.Text = "第" + rowIndex + "行第" + ImportHelper.GetExcelColumnName(effColumnIndex) + "列不是有效的数字类型。";
hasError = true;
break;
}
}
insertValue.Append(", " + dCell.ToString());
}
insertValue.Append(")");
dbCommand.CommandText = insertField.ToString() + insertValue.ToString();
dbCommand.ExecuteNonQuery();
#endregion
}
this.lblMsg.Text = sYM + "员工工资数据导入成功。";
}
#endregion
}
catch (Exception ex)
{
this.lblMsg.Text = ex.Message;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}