none
C# Textbox import to Excel RRS feed

  • Question

  • Hello,

    I´m new in Programing,

    and i hope someone can help me.

    this is my source code :

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

    namespace GUI_6600
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

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

            private void label1_Click(object sender, EventArgs e)
            {

            }

            private void numericUpDown1_ValueChanged(object sender, EventArgs e)
            {

            }

            private void label2_Click(object sender, EventArgs e)
            {

            }

            private void Form1_Load(object sender, EventArgs e)
            {

            }

            private void button1_Click(object sender, EventArgs e)
            {

               Microsoft.Office.Interop.Excel.Application xlsx = new Microsoft.Office.Interop.Excel.Application();
               Workbook wb = xlsx.Workbooks.Add(XlSheetType.xlWorksheet);
               Worksheet ws = (Worksheet)xlsx.ActiveSheet;

                xlsx.Visible = true;
                ws.Cells[1, 1] = "FAHNEN NUMMER :";
                ws.Cells[1, 2] = "BEANSTANDUNG :";
                ws.Cells[1, 3] = "NAME :";
                ws.Cells[1, 4] = "DATUM :";
                ws.Cells[1, 5] = "ZUSTÄNDIGKEITS-BERREICH :";

                ws.Cells[2, 1] = Fahne.Text;
                ws.Cells[2, 2] = BEANSTANDUNG.Text;
                ws.Cells[2, 3] = NAME.Text;
                ws.Cells[2, 4] = DATUM.Text;
                ws.Cells[2, 5] = BERREICH.Text;

                    wb.SaveAs("5S_DIGITAL_6600.xlsx");
               
            }

            private void textBox1_TextChanged(object sender, EventArgs e)
            {

            }

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

    My Problem is that the Code gererates every Time a ney Excel Sheet and so it is not possible to add a few things in my Excel list.

    Because every time the code overrides the same Cells.

    Hopefully someone can help me that the code works normaly.

    What i expect from the Code is that i fill in at the Different "Textboxes" some informations

    and the Code should transfer it into Excel.

    Hopefully someone could help me :)

    Thank you very much

    Monday, September 16, 2019 8:05 PM

All replies

  • I recommend looking at SpreadSheetLight totally free, installed in a Visual Studio solution via NuGet.

    Create new file, write some data, save.

    public void ExampleWrite(string firstName, string lastName, DateTime date)
    {
        var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File2.xlsx");
        using (var doc = new SLDocument())
        {
            doc.SetCellValue("A1", fileName);
            doc.SetCellValue("B1", lastName);
            doc.SetCellValue("C1", date);
    
    
            SLStyle style = doc.CreateStyle();
            style.FormatCode = "d-mmm-yyyy";
            doc.SetCellStyle("C1", style);
            doc.SaveAs(fileName);
        }
    }

    Now if we want to open an existing Excel file to a specific sheet and write data, save.

    public void ExampleWrite(string firstName, string lastName, DateTime date)
    {
        var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File2.xlsx");
        using (var doc = new SLDocument(fileName,"Sheet1"))
        {
            doc.SetCellValue("A1", firstName);
            doc.SetCellValue("B1", lastName);
            doc.SetCellValue("C1", date);
    
    
            SLStyle style = doc.CreateStyle();
            style.FormatCode = "d-mmm-yyyy";
            doc.SetCellStyle("C1", style);
            doc.Save();
        }
    }

    Bottom line is Excel automation can be problematic in many ways unless you totally understand the Excel model which for a noobe is not a quick learn. SpreadSheetLight uses Open XML which .xlsx files are based on.

    EDIT: This shows how to write to the next available row in an existing sheet.

    public void ExampleWrite(string firstName, string lastName, DateTime date)
    {
        var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File2.xlsx");
        using (var doc = new SLDocument(fileName, "Sheet1"))
        {
            // get access to last row used, increment by one to use a new row
            var stats = doc.GetWorksheetStatistics();
            var lastRowIndex = stats.EndRowIndex +1;
    
            doc.SetCellValue($"A{lastRowIndex}", firstName);
            doc.SetCellValue($"B{lastRowIndex}", lastName);
            doc.SetCellValue($"C{lastRowIndex}", date);
    
            SLStyle style = doc.CreateStyle();
            style.FormatCode = "d-mmm-yyyy";
            doc.SetCellStyle($"C{lastRowIndex}", style);
            doc.Save();
    
    
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    • Edited by KareninstructorMVP, Moderator Tuesday, September 17, 2019 10:26 AM Added example for writing to the next available row commonly known as the last used row
    Monday, September 16, 2019 9:26 PM
    Moderator
  • Hi Haicho,

    Thank you for posting here.

    For your question, you want to append text to the excel.

    You could try the following code to get it.

    private void Button1_Click(object sender, EventArgs e)
            {
    
                Microsoft.Office.Interop.Excel.Application xlsx = new Microsoft.Office.Interop.Excel.Application();
                Workbook wb = xlsx.Workbooks.Add(XlSheetType.xlWorksheet);
                Worksheet ws = (Worksheet)xlsx.ActiveSheet;
                xlsx.Visible = true;
                String path = @"D:\5S_DIGITAL_6600.xlsx";
                if (!File.Exists(path))
                {
                    ws.Cells[1, 1] = "FAHNEN NUMMER :";
                    ws.Cells[1, 2] = "BEANSTANDUNG :";
                    ws.Cells[1, 3] = "NAME :";
                    ws.Cells[1, 4] = "DATUM :";
                    ws.Cells[1, 5] = "ZUSTÄNDIGKEITS-BERREICH :";
    
                    ws.Cells[2, 1] = Fahne.Text;
                    ws.Cells[2, 2] = BEANSTANDUNG.Text;
                    ws.Cells[2, 3] = NAME.Text;
                    ws.Cells[2, 4] = DATUM.Text;
                    ws.Cells[2, 5] = BERREICH.Text;
                    wb.SaveAs(@"D:\5S_DIGITAL_6600.xlsx");
                }
                else
                {
                    appendToTheXlsx(path);
                }
                
                xlsx.Quit();
            }
    
            private void appendToTheXlsx(String DataSource)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Workbook xlWorkBook;
                Worksheet xlWorkSheet;
                object missingVal = System.Reflection.Missing.Value;
    
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(
                Filename: DataSource,
                   ReadOnly: false,
    
                   Origin: XlPlatform.xlWindows,
                   Editable: true,
                Notify: false,
                   AddToMru: true,
                   Local: true,
                 CorruptLoad: missingVal
                   );
    
                xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1); 
                Range range = xlWorkSheet.UsedRange;
                int usedRowCount = range.Rows.Count;
    
                const int excelRowHeader = 1;
                const int excelColumnHeader = 1;
    
                int curColumnIdx = 0 + excelColumnHeader; 
                int curRrowIdx = usedRowCount ; 
    
                curRrowIdx = curRrowIdx + 1;
                xlWorkSheet.Cells[curRrowIdx, curColumnIdx] = Fahne.Text;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = BEANSTANDUNG.Text;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = NAME.Text;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = DATUM.Text;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = BERREICH.Text;
                xlWorkBook.Close(SaveChanges: true);
                crl.ReleaseComObject(xlWorkSheet);
                crl.ReleaseComObject(xlWorkBook);
                crl.ReleaseComObject(xlApp); //releaseObject
            }


    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, September 17, 2019 5:12 AM
    Moderator