using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ExcelApp = Microsoft.Office.Interop.Excel;
namespace EmployeePerformanceForm
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 选择绩效模板
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "请选择绩效表模板";
ofd.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls|Excel 工作簿(*.xlsx)|*.xlsx";
ofd.FilterIndex = 2;
if (ofd.ShowDialog()==DialogResult.OK)
{
string filename = ofd.FileName;
textBox1.Text = filename;
}
}
/// <summary>
/// 选择各项目的绩效表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "请选择各项目负责人提交的绩效表<可以多选>";
ofd.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls|Excel 工作簿(*.xlsx)|*.xlsx";
ofd.FilterIndex = 2;
ofd.Multiselect = true;
if (ofd.ShowDialog() == DialogResult.OK)
{
string[] filename_arr = ofd.FileNames;
foreach (string item in filename_arr)
{
listBox1.Items.Add(item);
}
}
}
/// <summary>
/// 生成绩效汇总表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
#region//初始化
string mb_path = textBox1.Text;
string year_month = textBox3.Text;
List<string> files = new List<string>();
foreach (string item in listBox1.Items)
{
files.Add(item);
}
#endregion
if (files.Count > 0 && mb_path!="")
{
IWorkbook mb_workbook = null;
//FileStream filestream_mb = new FileStream(mb_path, FileMode.Open, FileAccess.Read);
using (FileStream fs = new FileStream(mb_path, FileMode.Open, FileAccess.Read))
{
if (mb_path.IndexOf(".xlsx") > 0) // 2007版本
{
mb_workbook = new XSSFWorkbook(fs); //xlsx数据读入workbook
}
else if (mb_path.IndexOf(".xls") > 0) // 2003版本
{
mb_workbook = new HSSFWorkbook(fs); //xls数据读入workbook
}
fs.Close();
}
ISheet mb_sheet = mb_workbook.GetSheetAt(0);
IRow rowm = mb_sheet.GetRow(1);
ICell cellm = rowm.GetCell(6);
cellm.SetCellValue(year_month);
foreach (string file in files)
{
//文件名
string filename = Path.GetFileNameWithoutExtension(file);
IWorkbook workbook = null;
//读取文件流
FileStream filestream = new FileStream(file, FileMode.Open, FileAccess.Read);
if (file.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(filestream);
}
else if (file.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(filestream);
}
ISheet sheet = workbook.GetSheetAt(0);
//获取行数
int rownum = sheet.LastRowNum;
int i = 3;
Dictionary<string, PersonDATA> dic_jxb = new Dictionary<string, PersonDATA>();
while (i < rownum+1)
{
int xh = 0;
IRow row = sheet.GetRow(i);
int.TryParse(row.GetCell(0).ToString(), out xh);
if (xh != 0)
{
try
{
PersonDATA data = new PersonDATA();
string name = row.GetCell(1).ToString();
string xmmc = row.GetCell(2).ToString();
string gznr = row.GetCell(3).ToString();
string gzlx = row.GetCell(4).ToString();
if (row.GetCell(5).ToString() != "")
{
double wyts = double.Parse(row.GetCell(5).ToString());//double
data.wyts = wyts;
}
if (row.GetCell(6).ToString() != "")
{
double dwcb = double.Parse(row.GetCell(6).ToString());//double
data.dwcb = dwcb;
}
string jldw = row.GetCell(7).ToString();//string
if (row.GetCell(8).ToString() != "")
{
double rwl = double.Parse(row.GetCell(8).ToString());//double
data.rwl = rwl;
}
if (row.GetCell(9).ToString() != "")
{
double jl = double.Parse(row.GetCell(9).ToString());//double
data.jl = jl;
}
if (row.GetCell(10).NumericCellValue !=0)
{
data.gt = row.GetCell(10).NumericCellValue;
}
if (row.GetCell(12)!=null)
{
string bz = row.GetCell(12).ToString();
data.bz = bz;
}
data.name = name;
data.xmmc = xmmc;
data.gznr = gznr;
data.gzlx = gzlx;
data.jldw = jldw;
if (name != "")
{
if (data.dwcb != 0 && data.rwl != 0)
{
dic_jxb.Add(name, data);
}
}
}
catch
{
}
}
else
{
break;
}
i++;
}