菜鸟C#操作Excel的问题

彤小晨er 2015-12-26 12:47:09

菜鸟C#操作Excel的问题，请大家帮忙~
将文件夹下所有xlsx的文件合并，在指定位置【C】添加名为“连接后”的一列，赋值为A列与B列连接的字符串，并降序排列（除表头。
比如5个文件一个文件一条数据，每个数据都有表头，希望合并后表为：

点击按钮连接后新表为：

谢谢大家啦~
最好是提供源码啦~拜托大家~

...全文

396 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

早起晚睡 2016-01-21

打赏
举报

引用 16 楼 chenxt87 的回复:

[quote=引用 13 楼 bjgzxx 的回复:] 这是大牛人写的你看看吧应该改改添加方法就能用

谢谢您啦~ 但我整了半天也运行不起来···肿么办··· 嘿嘿~不过还是感谢您~[/quote]先合并然后排序那就是Excel的事情

彤小晨er 2016-01-20

打赏
举报

引用 13 楼 bjgzxx 的回复:

这是大牛人写的你看看吧应该改改添加方法就能用

谢谢您啦~ 但我整了半天也运行不起来···肿么办··· 嘿嘿~不过还是感谢您~

轩宸 2016-01-05

打赏
举报

1、使用C#读取指定文件夹下的Excel，并生成DataTable 2、处理读取文件的数据流，excel表格从左到右读，从上到下。从左到右，每个单元格数值间必有一个"\t"；从上到下，每切换一行，数据增加"\r\n"；在适当位置添加"\t"，就是切换单元格；添加"\r\n"，就是换行。 2、创建excel 这里注意编码的转换。

秋的红果实 2015-12-31

打赏
举报

可以使用OleDb驱动，把一个excel文件看作一个数据库，一个sheet看作一张表，追加记录就行了

早起晚睡 2015-12-30

打赏
举报

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace Excelhebing
{
    class MergeExcel
    {
        Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //保存目标的对象
        Excel.Workbook bookDest = null;
        Excel.Worksheet sheetDest = null;
        //读取数据的对象 
        Excel.Workbook bookSource = null;
        Excel.Worksheet sheetSource = null;
        string[] _sourceFiles = null;
        public static string[,] _dff;
        string _destFile = string.Empty;
        string _columnEnd = string.Empty;
        int _headerRowCount = 1;
        int _currentRowCount = 0;
        public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {

            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            sheetDest.Name = "Data";
            _sourceFiles = sourceFiles;
            _dff = new string[sourceFiles.Count(), 3];
            _destFile = destFile;
            _columnEnd = columnEnd;
            _headerRowCount = headerRowCount;
        }
        /// <summary>
        /// 打开工作表
        /// </summary>
        /// <param name="fileName"></param>
        void OpenBook(string fileName)
        {
            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
        }
        /// <summary>
        /// 关闭工作表
        /// </summary>
        void CloseBook()
        {
            bookSource.Close(false, Missing.Value, Missing.Value);
        }
        /// <summary>
        /// 复制表头
        /// </summary>
        void CopyHeader()
        {
            Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
            range.Copy(sheetDest.get_Range("A1", Missing.Value));
            _currentRowCount += _headerRowCount;
        }
        /// <summary>
        /// 复制数据
        /// </summary>
        void CopyData(int filesint)
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            int money = 0;
            for (int i = 4; i < sheetRowCount; i++)
            {
                //cellsstr = ;
                if (((Excel.Range)sheetSource.Cells[i, 1]).Text.ToString() == "" && ((Excel.Range)sheetSource.Cells[i, 5]).Text.ToString() == "")
                {
                    sheetRowCount = i - 1;
                    break;
                }
                else 
                {
                    money = money+int.Parse(((Excel.Range)sheetSource.Cells[i, 6]).Text.ToString());
                }

            }
            
            _dff[filesint, 0] = ((Excel.Range)sheetSource.Cells[sheetRowCount - 1, 2]).Text.ToString();
            _dff[filesint, 1] = (sheetRowCount-3).ToString();
            _dff[filesint, 2] = money.ToString();
            sheetSource.Cells[sheetRowCount, 8] = (sheetRowCount - 3).ToString();
            sheetSource.Cells[sheetRowCount, 9] = money.ToString();
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount + 1), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }

        //void DeleteRows(int rowIndex)
        //{
        //    Excel.Range range = (Excel.Range)sheetSource.Rows[rowIndex,];
        //    range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        //}
        public static string[,] sfff()
        {
            return _dff;
        }
         void Delete()
         {
             int sheetRowCount = sheetSource.UsedRange.Rows.Count;
             int rowint = 3;
             //string cellsstr = "";
             for (int i = 3; i < sheetRowCount;i++ )
             {
                 //cellsstr = ;
                 if (((Excel.Range)sheetSource.Cells[i, 1]).Text.ToString()=="")
                 {
                     rowint = i;
                     break;
                 }

             }
             if(rowint!=3)
             {             
                 Excel.Range range = sheetSource.get_Range(sheetSource.Cells[rowint, 1], sheetSource.Cells[sheetRowCount, 10]);
                  range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
             }

     
             //Excel.Range range = sheetSource.get_Range(sheetSource.Cells[startRow, startColumn], sheetSource.Cells[endRow, endColumn]);
             //range.Select();
             //range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
         }

        /// <summary>
        /// 保存结果
        /// </summary>
        void Save()
        {
            bookDest.Saved = true;
            bookDest.SaveCopyAs(_destFile);
        }
        /// <summary>
        /// 退出进程
        /// </summary>
        void Quit()
        {
            app.Quit();
        }
        /// <summary>
        /// 合并
        /// </summary>
        void DoMerge()
        {
            bool b = false;
            int filesint = 0;
            foreach (string strFile in _sourceFiles)
            {
                OpenBook(strFile);
                if (b == false)
                {
                    CopyHeader();
                    b = true;
                }
                Delete();
                CopyData(filesint);
                filesint = filesint + 1;
                CloseBook();
            }
            Save();
            Quit();
        }
        /// <summary>
        /// 合并表格
        /// </summary>
        /// <param name="sourceFiles">源文件</param>
        /// <param name="destFile">目标文件</param>
        /// <param name="columnEnd">最后一列标志</param>
        /// <param name="headerRowCount">表头行数</param>
        public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
        }


    }
}

早起晚睡 2015-12-30