c# - 在多个文件中解析 excel 文件时覆盖最后一个文件（C# windows 窗体）

Question

我的任务是以编程方式将给定的 excel 文件解析为给定位置的多个 excel 文件。每个文件都有一个 id 列，我必须按 id 解析它（例如，如果我输入 10，id 的 0-9 转到第一个文件，10-19 转到第二个文件等，直到结束）。问题是在最后一个文件中，内容被覆盖了。作为一个测试用例，我有 1 个 ID 从 1 到 50（包括）的文件。这应该有输出 6 个文件（0 代表 1-9，1 代表 10-19，2 代表 20-29，3 代表 30-39，4 代表 4-49，5 只代表 50）。我的问题是，在文件 4 中，信息被覆盖，它是文件 5（第 50 项）的内容。我附上了下面的源代码：

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_Splitter
{
    public partial class FormSpl : Form
    {

        string filePath = string.Empty, folderPath = string.Empty;

        public FormSpl()
        {
            InitializeComponent();
        }

        private void FormSpl_FormClosed(object sender, FormClosedEventArgs e)
        {
            System.Windows.Forms.Application.Exit();
        }

        private void buttonSelectFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            if (file.ShowDialog() == DialogResult.OK)
            {
                filePath = file.FileName;
                labelPath.Text = filePath;
            }

        }


        private void buttonDestination_Click(object sender, EventArgs e)
        {
            DialogResult result = selectFolder.ShowDialog();

            if (result == DialogResult.OK && !string.IsNullOrWhiteSpace(selectFolder.SelectedPath))
            {
                folderPath = selectFolder.SelectedPath;
                labelDestinationFolder.Text = folderPath;
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            if (System.Text.RegularExpressions.Regex.IsMatch(textBoxValue.Text, "[^0-9]"))
            {
                MessageBox.Show("Please enter only numbers.");
                textBoxValue.Text = textBoxValue.Text.Remove(textBoxValue.Text.Length - 1);
            }
        }

        private void buttonParse_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(textBoxValue.Text))
            {
                MessageBox.Show("please write number of parse");
                return;
            }
            int parseNumber = int.Parse(textBoxValue.Text);
            string message = string.Empty;
            if (filePath.Equals(string.Empty))
                message += "Please select a file first!";
            if (filePath.Equals(string.Empty))
                message += "\nPlease select first where to save the files!";


            if (!message.Equals(string.Empty))
            {
                MessageBox.Show(message);
                return;
            }
            int columnID = 0;

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;


            //search the ID column
            for (int col = 1; col <= colCount; col++)
            {
                if (Convert.ToString(xlRange.Cells[1, col].Value2) == "ID")
                {
                    columnID = col;
                    MessageBox.Show("column is " + columnID);
                    break;
                }

            }

            Excel.Workbook newOne;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            newOne = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)newOne.Worksheets.get_Item(1);


            //in thhe first line
            for (int col = 1; col <= colCount; col++)
            {
                xlWorkSheet.Cells[1, col] = xlRange.Cells[1, col].Value;
            }


            int firstNumber = 0, rowsCurrent = 1;
            for (int row = 2; row <= rowCount; row++)
            {

                //take the ID from current row   
                int currentID = Convert.ToInt32(xlRange.Cells[row, columnID].Value);
                if (currentID == 0)
                    break;
                if (currentID < parseNumber)
                    currentID = 0;

                //if is >value, take the first number          
                while (currentID >= parseNumber)
                    currentID /= parseNumber;

                //if is in still same range, add the row the new file
                if (currentID == firstNumber)
                {
                    ++rowsCurrent;
                    for (int col = 1; col <= colCount; col++)
                    {
                        xlWorkSheet.Cells[rowsCurrent, col] = xlRange.Cells[row, col].Value;

                    }

                }
                //new ID range, close the file, create a new one
                else
                {
                    //save current, reinitalise
                    newOne.SaveAs(folderPath + "\\excel" + firstNumber + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                              false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    rowsCurrent = 1;
                    firstNumber++;
                    row--;


                    //delete data
                    Range maxRange = xlWorkSheet.Cells;
                    Excel.Range range = xlWorkSheet.get_Range("A1:A300", Type.Missing);
                    range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);

                    //write header
                    for (int col = 1; col <= colCount; col++)
                    {
                        xlWorkSheet.Cells[1, col] = xlRange.Cells[1, col].Value2;
                    }


                }

            }

            xlWorkbook.Close(true, misValue, misValue);
            newOne.Close(true, misValue, misValue);
            MessageBox.Show("parse done");
        }
    }
}

知道我该如何解决这个问题吗？

标签： c#excelwinforms