Newline角色被导出到Excel

Question

我正在编写一个体重秤的程序，该程序称重袋子并将金额发送到Richtextbox。 我使用\ n，因此值可以垂直堆叠。然后，我将列表导出到Excel。 导出到Excel的值被指定为Excel中的文本，我无法使用自动和。当我仔细研究Excel值时，我看到一条新线路与我的值一起出口到同一单元格。当我删除此空间时，Excel会自动将其转换为一个数字。我的新线角色如何出口到Excel？关于如何解决此问题的任何想法都是最欢迎的。我的代码中的评论是我已经尝试过的所有不同的事情。我是编程的新手。请温柔。附加是导出后Excel文件的图像和程序的UI。 ui of Program || || 导出的excel文件 || 新线条变得可见单击单元格

 public partial class Form1 : Form
{
    private SerialPort _serialPort;         //<-- declares a SerialPort Variable to be used throughout the form
    private const int BaudRate = 9600;      //<-- BaudRate Constant. 9600 seems to be the scale-units default value

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        string[] portNames = SerialPort.GetPortNames();     //<-- Reads all available comPorts
        foreach (var portName in portNames)
        {
            cboSelectPort.Items.Add(portName);                  //<-- Adds Ports to combo box
        }
        cboSelectPort.SelectedIndex = 0;                        //<-- Selects first entry (convenience purposes)
    }
     

    private delegate void Closure();
    private void SerialPortOnDataReceived(object sender, SerialDataReceivedEventArgs serialDataReceivedEventArgs)
    {
        if (InvokeRequired)     //<-- Makes sure the function is invoked to work properly in the UI-Thread
            BeginInvoke(new Closure(() => { SerialPortOnDataReceived(sender, serialDataReceivedEventArgs); }));     //<-- Function invokes itself
        else
        {
            int dataLength = _serialPort.BytesToRead;
            byte[] data = new byte[dataLength];
            int nbrDataRead = _serialPort.Read(data, 0, dataLength);
            if (nbrDataRead == 0)
                return;
            string str = System.Text.Encoding.UTF8.GetString(data);
            txbWeightList.Text = str.ToString();
           

        }
    }
    private void btnWeigh_Click(object sender, EventArgs e)
    {
       

        //<-- This block ensures that no exceptions happen
        if (_serialPort != null && _serialPort.IsOpen)
            _serialPort.Close();
        if (_serialPort != null)
            _serialPort.Dispose();
        //<-- End of Block
      


        _serialPort = new SerialPort(cboSelectPort.Text, BaudRate, Parity.None, 8, StopBits.One);       //<-- Creates new SerialPort using the name selected in the combo box
        _serialPort.DataReceived += SerialPortOnDataReceived;       //<-- this event happens every time when new data is received by the ComPort
        _serialPort.Open();     //<-- make the comport listen
        _serialPort.DataReceived -= SerialPortOnDataReceived;
        txbWeightList.Text += "\n"; //uncomment to add new line character
        //_serialPort.DataReceived -= SerialPortOnDataReceived;


        Thread.Sleep(200);

        string response = _serialPort.ReadLine();
        _serialPort.WriteTimeout = 200;

        txbWeightList.Text += response;

    }


    private void button4_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlApp == null)
        {
            MessageBox.Show("Excel is not properly installed!!");
            return;
        }

        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;

        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet.Columns[7].NumberFormat = "0";

        string[] stringList = txbWeightList.Text.Split('\n');

        //string textWithNewLine = txbWeightList.Text.Replace("\n", Environment.NewLine);


        //txbWeightList.Text.AppendFormat(@" <Alignment ss:WrapText=""1"" ss:Vertical=""Bottom""/>{0}", Environment.NewLine);

        //txbWeightList.Text.Replace("\n", String.Empty);

        //for (int row = 2; row < (stringList.Length + 2); row++)
        //{
        //    xlWorkSheet.Cells[row, 7] = stringList[row - 2];
        //}

        for (int row = 1; row < (stringList.Length + 1); row++)
        {
            xlWorkSheet.Cells[row, 7] = stringList[row - 1];
        }

        xlWorkSheet.Cells[1, 1] = "Date";
        xlWorkSheet.Cells[1, 2] = "Batch No";
        xlWorkSheet.Cells[1, 3] = "Totale Sakke";
        xlWorkSheet.Cells[1, 4] = "Pallet Gewig";
        xlWorkSheet.Cells[1, 5] = "Tipe Sak";
        xlWorkSheet.Cells[1, 6] = "Kultivar";
        xlWorkSheet.Cells[1, 7] = "Gewig p/sak";
        xlWorkSheet.Cells[2, 1] = DateTime.Today;
        xlWorkSheet.Cells[2, 2] = txtBatch.Text;
        xlWorkSheet.Cells[2, 3] = txtTotaleSakke.Text;
        xlWorkSheet.Cells[2, 4] = txtPalletWeight.Text;
        xlWorkSheet.Cells[2, 5] = cmbTipeSak.Text;
        xlWorkSheet.Cells[2, 6] = cmbKultivar.Text;

        xlWorkSheet.Columns.AutoFit();
        xlWorkSheet.Rows.AutoFit();
        xlWorkSheet.Cells.Style.WrapText = true;


        xlWorkBook.SaveAs("C:\\Users\\Skaal\\Documents\\Excel Nuwe Lewerings\\Nuwe Lewering.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        MessageBox.Show("Excel file created , you can find the file C:\\Nuwe Lewering.xlsx");
        



    }

    private void btnClear_Click(object sender, EventArgs e)
    {
        txbWeightList.Clear();
    }

    private void btnExit_Click(object sender, EventArgs e)
    {
        this.Close();
    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void txbWeightList_TextChanged(object sender, EventArgs e)
    {
        txbWeightList.Text = Regex.Replace(txbWeightList.Text, "[a-zA-Z]", "");
        //txbWeightList.Text = Regex.Replace(txbWeightList.Text, @"\s+", "");
        //txbWeightList.Text = Regex.Replace(txbWeightList.Text, @"[\""]", "", RegexOptions.None);
        //txbWeightList.Text = Regex.Replace(txbWeightList.Text, @"[.]", ",");
        //decimal myDoubleValue = Decimal.Parse(txbWeightList.Text);
        //string str = txbWeightList.Text.TrimEnd(new Char[] {'\n'});

        //string no_start_zeros = txbWeightList.Text.TrimStart('0');
        //txbWeightList.Text.TrimStart('0').PadLeft(3, '0');

        //string first0 = txbWeightList.Text.TrimStart(new Char[] { '0' });
        //if (txbWeightList.Text.StartsWith("0") && !txbWeightList.Text.StartsWith("0.") && txbWeightList.Text.Length > 1)
        //{
        //    txbWeightList.Text = txbWeightList.Text.Substring(1);
        //txbWeightList.Text.ToString().TrimEnd('\r', '\n');
        //}

    }
}

Answer 1

要在单元格中获取数值，只需将数字值传递给它：

更改以下线：

xlWorkSheet.Cells[row, 7] = stringList[row - 1];

将其传递给以下数值：

double Weight = 0;
double.TryParse(stringList[row - 1], out Weight);
xlWorkSheet.Cells[row, 7] = Weight;