private void button1_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
if (openFileDialog1.FileName.Contains(".xlsx"))
{
txtPath.Text = openFileDialog1.FileName;
if (txtPath.Text.Trim() != null)
{
sheets = ExcelDataTableUtil.GetAllSheets(txtPath.Text.Trim());
foreach (var item in sheets)
{
sheetnames.Add(item.SheetName);
}
cboxSheet.Items.AddRange(sheetnames.ToArray());
}
}
else
{
MessageBox.Show("文件格式错误！！");
}
}
}
public void GetJob(Job job)
{
//显示面板
groupBox1.Visible = true;
//清空下拉列表
comboBox1.Items.Clear();
comboBox2.Items.Clear();
//获取传过来的job
this.job = job;
//把当前index赋值给状态栏
toolStripStatusLabel1.Text = job.CurrentIndex.ToString();
//获取当前job对应的Excel的sheet,并转换成datatable
list = ExcelDataTableUtil.ExceltoDataTable(job.JobPath);
dt = list[0];
for (int i = 1; i <= dt.Columns.Count; i++)
{
comboBox1.Items.Add(i);
comboBox2.Items.Add(i);
}
dt.Columns.Add();
dt.Columns.Add();
this.job.CurrentIndex = 0;
this.job.HasFinished = 0;
comboBox1.SelectedItem = comboBox1.Items[0];
comboBox2.SelectedItem = comboBox2.Items[0];
ExcelDataTableUtil.WriteExcel(ExcelDataTableUtil.DataTabletoExcel(dt), job.JobPath);
}
private void button5_Click(object sender, EventArgs e)
{
if (job.CurrentIndex != job.Count)
{
Comment com = new Comment();
com.ShowDialog();
string txt = com.CommentText;
XSSFWorkbook workbook = ExcelDataTableUtil.LoadExcel(job.JobPath);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(job.CurrentIndex);
row.Cells[dt.Columns.Count - 2].SetCellValue("U");
XSSFColor XlColour = new XSSFColor(button5.BackColor);
XSSFCellStyle cellstyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
row.Cells[dt.Columns.Count - 2].CellStyle = cellstyle;
row.Cells[dt.Columns.Count - 1].SetCellValue(txt);
ExcelDataTableUtil.WriteExcel(workbook, job.JobPath);
job.CurrentIndex++;
job.HasFinished++;
Judge();
}
else
{
MessageBox.Show("已经是最后一条了");
}
}
private void button2_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cboxSheet.Text.Trim()))
{
label7.Text = "请选择工作簿";
}
else if (string.IsNullOrEmpty(cboxSheet.Text.Trim()))
{
label4.Text = "请选择sheet";
}
else if (string.IsNullOrEmpty(txtFirstrow.Text.Trim()))
{
label5.Text = "请输入起始行号";
}
else if (string.IsNullOrEmpty(txtLastrow.Text.Trim()))
{
label6.Text = "请输入截至行号";
}
else if (!Regex.IsMatch(txtLastrow.Text.Trim(), @"\d+"))
{
label6.Text = "必须是整数";
}
else if (!Regex.IsMatch(txtFirstrow.Text.Trim(), @"\d+"))
{
label5.Text = "必须是整数";
}
else if (Convert.ToInt32(txtFirstrow.Text.Trim()) > Convert.ToInt32(txtLastrow.Text.Trim()))
{
label5.Text = "起始行号不能大于截至行号";
}
else
{
string target = Path.GetFileNameWithoutExtension(txtPath.Text.Trim()) + txtFirstrow.Text.Trim() + "_" + txtLastrow.Text.Trim();
string path = Path.Combine(Directory.GetCurrentDirectory(), "tasks", target + ".xlsx");
XSSFSheet sheet = ExcelDataTableUtil.GetSheetbyName(txtPath.Text.Trim(), cboxSheet.Text.Trim());
int lastnum = Convert.ToInt32(txtLastrow.Text.Trim());
int firstnum = Convert.ToInt32(txtFirstrow.Text.Trim());
DataTable dt = ExcelDataTableUtil.SheetToDataTable(sheet, firstnum - 1, lastnum - 1);
XSSFWorkbook workbook = ExcelDataTableUtil.DataTabletoExcel(dt);
ExcelDataTableUtil.WriteExcel(workbook, path);
Job job = new Job(new Guid(), target, DateTime.Now.ToString(), path);
job.JobPath = path;
job.Count = (lastnum - firstnum + 1);
mainform.GetJob(job);
DialogResult result = MessageBox.Show("Success");
if (result == DialogResult.OK)
{
Close();
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (job.CurrentIndex != job.Count)
{
Comment com = new Comment();
com.ShowDialog();
string txt = com.CommentText;
XSSFWorkbook workbook = ExcelDataTableUtil.LoadExcel(job.JobPath);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(job.CurrentIndex);
row.Cells[dt.Columns.Count - 2].SetCellValue("Y");
row.Cells[dt.Columns.Count - 1].SetCellValue(txt);
ExcelDataTableUtil.WriteExcel(workbook, job.JobPath);
job.CurrentIndex++;
job.HasFinished++;
Judge();
}
else
{
MessageBox.Show("已经是最后一条了");
}
}
