将多个Excel导出到Execl示例
private void btn_Gather_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//定义object缺省值
string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名
string P_str_Name = "";//存储遍历到的Excel文件名
List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象,用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中
}
Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
}
workbook.Save();//保存目标工作簿
workbook.Close(false, missing, missing);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL");//关闭所有Excel进程
}
private void btn_Gather_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//定义object缺省值
string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名
string P_str_Name = "";//存储遍历到的Excel文件名
List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象,用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中
}
Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
}
workbook.Save();//保存目标工作簿
workbook.Close(false, missing, missing);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL");//关闭所有Excel进程
}
/// <summary>
/// test 导出execl
/// </summary>
/// <returns></returns>
public static string testExcel()
{
#region 建立excle
// 定义一个 Missing 的值,方便后面使用
Missing miss = Missing.Value; // 创建 Excel,并制定是不可见的
Application excel = new Application();
excel.Visible = false; //新建工作蒪
Workbook _workbook = excel.Workbooks.Add(); //打开现有的工作蒪
//Workbook _workbook = excel.Workbooks.Open("demo.xls"); //取得第一个工作表
Worksheet _worksheet = _workbook.Worksheets[1] as Worksheet; //取得当前默认工作表
//Worksheet _worksheet = _workbook.ActiveSheet as Worksheet;
#endregion //range.EntireColumn.AutoFit(); //自动调整列宽
//Range.WrapText=true;
#region 操作excel
//单元格赋值
excel.Cells[1, 1] = "5";
excel.Cells[1, 2] = "20";
excel.Cells[1, 3] = excelSum(new List<string> { "A1", "B1" });
excel.Cells[1, 4] = excelSumproduct(new List<string> { "A1", "B1" });
excel.Cells[2, 1] = "Second Row First Column";
excel.Cells[2, 2] = "Second Row Second Column"; //添加超链接
_worksheet.Hyperlinks.Add(excel.Cells[5, 1], "http://emax.co.nz", miss, miss, miss); //设置要合并的单元格 左上,右下 那个坐标集合
Range _range = _worksheet.get_Range("A3","C4");//
//执行合并
_range.MergeCells = true;
//设置合并后的值
_range.set_Value(miss, "a3b3");
//插入图片 //选中单元格
_range = _worksheet.get_Range("A6");
_range.Select();
//设置单元格行高,宽度
_range.RowHeight = 108;
_range.ColumnWidth = 18;
//插入图片到单元格
_worksheet.Shapes.AddPicture(@"C:\Documents and Settings\hasee\桌面\pvha3gdrn7gzq2r0.jpg",
Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,
Convert.ToSingle(_range.Left)+4, Convert.ToSingle(_range.Top)+4, 100, 100); // 保存,格式编码为56(xls)(.xlsx 的编码为51)
try
{
_workbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\testExecl.xls", 56);
}
catch (Exception ex)
{
return ex.Message;
}
//保存
//_workbook.SaveAs("demo.xls", 56);
#endregion #region 结束excle
// 关闭电子表格,释放资源
_workbook.Close();
_workbook = null; //退出 Excel,释放资源
excel.Quit();
killExcel(excel);
excel = null;
#endregion return "";
}