C#操作Excel的问题,请大家帮忙~
将文件夹下所有xlsx的文件合并,在指定位置【C】添加名为“连接后”的一列,赋值为A列与B列连接的字符串,并降序排列(除表头。
比如5个文件一个文件一条数据,每个数据都有表头,希望合并后表为:
点击按钮连接后新表为:
谢谢大家啦~
最好是提供源码啦~拜托大家~
将文件夹下所有xlsx的文件合并,在指定位置【C】添加名为“连接后”的一列,赋值为A列与B列连接的字符串,并降序排列(除表头。
比如5个文件一个文件一条数据,每个数据都有表头,希望合并后表为:
点击按钮连接后新表为:
谢谢大家啦~
最好是提供源码啦~拜托大家~
解决方案:40分
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();
}
}
}