读取和写入两个excel表并使用C＃将其写入另一个excel？ [英] Read and write two excel sheet and writing it in another excel using C#?

问题描述

在这里，我可以使用C＃中的oledb读取和写入数据，但是我尝试从两张excel表中读取数据并在另一张表中写入。



例如我的表1包含coloum | code | descriptin - 表2包含代码|描述（空的我试图从表1中获取）



我必须从表1中获取表格描述的描述

Here i can able to get read and write data using oledb in C# , but i have try to read data from two excel sheet and write in another sheet.

for example my sheet 1 contain coloum |code |descriptin -- sheet 2 contain code |description (empty i have try to get from sheet 1)

I have to get description from sheet 1 for sheet description

using System.Collections.Generic;
using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
//using System.Data;
using System.Data.SqlClient;

namespace Train1
{
    public partial class Form1 : Form
    {
        //private dynamic data1;
        public Form1()
        {
            InitializeComponent();
        }

        private void Subbut_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection();
            string Import_FileName = txtFileName.Text;
            string fileExtension = Path.GetExtension(Import_FileName);
            
            if (fileExtension == ".xls")
            {
                conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + Import_FileName + ";Extended Properties= \"Excel 12.0;HDR=yes\"";
            }
            
            else if (fileExtension == ".xlsx")
            {
                conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " + Import_FileName + ";" + "Extended Properties ='Excel 12.0 Xml;HDR=YES;'";
            }
            else
            {
                MessageBox.Show("PLese choose correct excel file format");
            }


            OleDbCommand comm = new OleDbCommand("Select * from [Sheet1$]");
            comm.Connection = conn;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = comm;
            da.Fill(ds);
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Excel.Application excelApp = new Excel.Application();
            excelApp.Application.Workbooks.Add(true);
            string timeMark = DateTime.Now.ToString(" - yyyyMMddHHmmss");
            string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Output" + timeMark + ".xlsx");
            //Create an Excel workbook instance and open it from the predefined location  
            Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;
            Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);

            foreach (System.Data.DataTable table in ds.Tables)
            {   
                //Add a new worksheet to workbook with the Datatable name
                Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;
                
                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                    
                }

                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                    }
                }


                excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
                excelWorkBook.Close(false, miss, miss);
                //excelWorkBook.Save();  
                books.Close();
                excelApp.Quit();
            }
        }



        private void butbrow_Click(object sender, EventArgs e)
        {
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK) // Test result.
            {
                txtFileName.Text = openFileDialog1.FileName;
            }
        }

    }
}

我尝试了什么：



我试图从单个Excel工作表中读取和写入数据。但是我已经尝试从一张纸到另一张纸获取数据并将输出表生成另一张纸。





谢谢提前....

What I have tried:

I have tried to get read and write data from single excel sheet. but i have try to get data from sheet to sheet and creat output sheet to another sheet.


Thanks advance....

推荐答案

）;
comm.Connection = conn;
OleDbDataAdapter da = new OleDbDataAdapter（）;
da.SelectCommand = comm;
da.Fill（ds）;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.Application excelApp = new Excel.Application（）;
excelApp.Application.Workbooks.Add（ true ）;
string timeMark = DateTime.Now.ToString（ - yyyyMMddHHmmss）;
string FilePath = Path.Combine（Environment.GetFolderPath（Environment。斯佩恰lFolder.Desktop）， 输出 + timeMark + 。xlsx）;
// 创建Excel工作簿实例并从预定义位置打开它
Excel.Workbooks books =（Excel.Workbooks）excelApp.Workbooks;
Excel.Workbook excelWorkBook =（Excel.Workbook）books.Add（miss）;

foreach （System.Data.DataTable表 in ds.Tables）
{
// 使用数据名称 $ b向工作簿添加新工作表$ b Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add（）;
excelWorkSheet.Name = table.TableName;

for （ int i = 1 ; i < table.Columns.Count + 1 ; i ++）
{
excelWorkSheet.Cells [ 1 ，i] = table.Columns [i - 1 ]的ColumnName;

}

for （ int j = 0 ; j < table.Rows.Count; j ++）
{
for （ int k = 0 ; k < table.Columns.Count; k ++）
{
excelWorkSheet.Cells [j + 2 ，k + 1 ] = table.Rows [j] .ItemArray [k] .ToString（）;
}
}


excelWorkBook.SaveAs（FilePath，miss，miss，miss，miss，miss，Excel.XlSaveAsAccessMode.xlNoChange，System.Text.Encoding。 UTF8，小姐，小姐）;
excelWorkBook.Close（ false ，miss，miss）;
// excelWorkBook.Save（）;
books.Close（）;
excelApp.Quit（）;
}
}



private void butbrow_Click（ object sender，EventArgs e）
{
DialogResult result = openFileDialog1.ShowDialog（）;
if （result == DialogResult.OK） // 测试结果。
{
txtFileName.Text = openFileDialog1.FileName;
}
}

}
}

"); comm.Connection = conn; OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = comm; da.Fill(ds); System.Reflection.Missing miss = System.Reflection.Missing.Value; Excel.Application excelApp = new Excel.Application(); excelApp.Application.Workbooks.Add(true); string timeMark = DateTime.Now.ToString(" - yyyyMMddHHmmss"); string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Output" + timeMark + ".xlsx"); //Create an Excel workbook instance and open it from the predefined location Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks; Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss); foreach (System.Data.DataTable table in ds.Tables) { //Add a new worksheet to workbook with the Datatable name Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(); excelWorkSheet.Name = table.TableName; for (int i = 1; i < table.Columns.Count + 1; i++) { excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName; } for (int j = 0; j < table.Rows.Count; j++) { for (int k = 0; k < table.Columns.Count; k++) { excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString(); } } excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss); excelWorkBook.Close(false, miss, miss); //excelWorkBook.Save(); books.Close(); excelApp.Quit(); } } private void butbrow_Click(object sender, EventArgs e) { DialogResult result = openFileDialog1.ShowDialog(); if (result == DialogResult.OK) // Test result. { txtFileName.Text = openFileDialog1.FileName; } } } }

我尝试了什么：



我试图从单个Excel工作表中读取和写入数据。但是我已经尝试从一张纸到另一张纸获取数据并将输出表生成另一张纸。





谢谢提前....

What I have tried:

I have tried to get read and write data from single excel sheet. but i have try to get data from sheet to sheet and creat output sheet to another sheet.


Thanks advance....

用于读取值可能对您有所帮助

for reading values it may be helpfull for you

var package = new ExcelPackage(new FileInfo("filename with path of the file")); 
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
 var start = workSheet.Dimension.Start;
 var end = workSheet.Dimension.End;
 for (int row = start.Row; row <= end.Row; row++) { // Row by row... 
for (int col = start.Column; col <= end.Column; col++) {
object cellValue = workSheet.Cells[row, col].Text; 
 }
 }

这篇关于读取和写入两个excel表并使用C＃将其写入另一个excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！