Excel File Export (2) template copy

특정 서식을 반복해서 사용하고, 내용물을 채운다.

위치계산만 잘하면 쉽게 복사된다.

 

Range["D6:G6"], Cells[4, 3])에서 사용하는 좌표(?)가 다르니 주의해야 함.

 

 

 

 

핵심 소스

private void BtnExcelAll_Click(object sender, RoutedEventArgs e) { try { Excel.Application application = new Excel.Application(); Excel.Workbook workbook = null; Excel.Worksheet worksheet = null; workbook = application.Workbooks.Open(@"C:\work\03.GitHub\dotnet\WPF\WPF_Dev_Sample\WpfSample\template.xlsx"); worksheet = workbook.Sheets[1]; Excel.Range range = worksheet.UsedRange; int currentRowNo = 0; //for(int j = 0; j < dsData.Tables[0].Rows.Count; j++ ) Excel.Range copyRow1 = worksheet.Range["A1:G1"].EntireRow; Excel.Range copyRow2 = worksheet.Range["A2:G2"].EntireRow; Excel.Range copyRow3 = worksheet.Range["A3:G3"].EntireRow; Excel.Range copyRow4 = worksheet.Range["A4:G4"].EntireRow; Excel.Range copyRow5 = worksheet.Range["A5:G5"].EntireRow; Excel.Range copyRow6 = worksheet.Range["A6:G6"].EntireRow; //for (int i = 0; i < dsData.Tables[0].Rows.Count; i++) for (int i = 0; i < 10; i++) { if (i == 0) { ExcelMasterDataBind(range, currentRowNo + 1, dsData.Tables[0].Rows[i]); currentRowNo += 7; } else { Excel.Range insertRow1 = worksheet.Range["A" + (currentRowNo + 0).ToString() + ":G" + (currentRowNo + 0).ToString()].EntireRow; insertRow1.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow1.Copy(Type.Missing)); Excel.Range insertRow2 = worksheet.Range["A" + (currentRowNo + 1).ToString() + ":G" + (currentRowNo + 1).ToString()].EntireRow; insertRow2.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow2.Copy(Type.Missing)); Excel.Range insertRow3 = worksheet.Range["A" + (currentRowNo + 2).ToString() + ":G" + (currentRowNo + 2).ToString()].EntireRow; insertRow3.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow3.Copy(Type.Missing)); Excel.Range insertRow4 = worksheet.Range["A" + (currentRowNo + 3).ToString() + ":G" + (currentRowNo + 3).ToString()].EntireRow; insertRow4.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow4.Copy(Type.Missing)); Excel.Range insertRow5 = worksheet.Range["A" + (currentRowNo + 4).ToString() + ":G" + (currentRowNo + 4).ToString()].EntireRow; insertRow5.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow5.Copy(Type.Missing)); Excel.Range insertRow6 = worksheet.Range["A" + (currentRowNo + 5).ToString() + ":G" + (currentRowNo + 5).ToString()].EntireRow; insertRow6.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow6.Copy(Type.Missing)); ExcelMasterDataBind(range, currentRowNo, dsData.Tables[0].Rows[i]); currentRowNo += 6; } DataRow[] dr = dsData.Tables[1].Select(string.Format("CocktailNo = '{0}'", dsData.Tables[0].Rows[i]["CocktailNo"].ToString())); for(int j=0; j < dr.Length; j++) { //first row copy & paste Excel.Range copyRow = worksheet.Range["D6:G6"].EntireRow; Excel.Range insertRow = worksheet.Range["D" + (currentRowNo).ToString() + ":G" + (currentRowNo).ToString()].EntireRow; insertRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow.Copy(Type.Missing)); ((Excel.Range)range.Cells[currentRowNo -1, 3]).Value = (j + 1).ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 4]).Value = dr[j]["MaterialName"].ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 5]).Value = dr[j]["MaterialQuantity"].ToString() + dr[j]["MaterialUnitNM"].ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 6]).Value = dr[j]["Remark"].ToString(); currentRowNo++; } } worksheet.SaveAs(string.Format(@"C:\work\03.GitHub\dotnet\WPF\{0}-{1}.xlsx", "ALL", DateTime.Now.ToString("hhmmss"))); KillExcel(application); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }

 

 

전체

 

 

<Page xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:local="clr-namespace:WpfSample.DevTest" xmlns:dxlc="http://schemas.devexpress.com/winfx/2008/xaml/layoutcontrol" xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core" xmlns:Serialization="clr-namespace:DevExpress.Xpf.LayoutControl.Serialization;assembly=DevExpress.Xpf.LayoutControl.v17.2" xmlns:dxe="http://schemas.devexpress.com/winfx/2008/xaml/editors" xmlns:dxg="http://schemas.devexpress.com/winfx/2008/xaml/grid" x:Class="WpfSample.DevTest.Grid15ExcelTemplate" mc:Ignorable="d" d:DesignHeight="800" d:DesignWidth="1280" Title="Grid01"> <StackPanel Margin="0,10,10,10"> <dxlc:LayoutControl HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Orientation="Vertical" > <dxlc:LayoutGroup Header="Button" View="GroupBox" HorizontalAlignment="Stretch" > <dx:SimpleButton x:Name="btnSearch" Content="Search" HorizontalAlignment="Left" Click="Button_Click" /> <dx:SimpleButton x:Name="btnExcel" Content="Save excel" HorizontalAlignment="Left" Click="BtnExcel_Click" /> <dx:SimpleButton x:Name="btnExcelAll" Content="Save All excel" HorizontalAlignment="Left" Click="BtnExcelAll_Click" /> </dxlc:LayoutGroup> </dxlc:LayoutControl> <dxlc:LayoutControl HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Orientation="Vertical" > <dxlc:LayoutGroup Header="Content" View="GroupBox" HorizontalAlignment="Stretch" > <Grid> <Grid.ColumnDefinitions> <ColumnDefinition Width="600"/> <ColumnDefinition Width="600"/> </Grid.ColumnDefinitions> <Grid.RowDefinitions> <RowDefinition Height="300"/> <RowDefinition Height="250"/> </Grid.RowDefinitions> <dxg:GridControl Grid.Column="0" Grid.Row="0" x:Name="gcGrid" > <dxg:GridControl.Columns > <dxg:GridColumn FieldName="CocktailNo" Header="CocktailNo" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="CocktailNmKR" Header="CocktailNmKR" Width="100" ReadOnly="True"/> <dxg:GridColumn FieldName="CocktailNmEn" Header="CocktailNmEn" Width="100" ReadOnly="True"/> <dxg:GridColumn FieldName="CocktailScore" Header="CocktailScore" Width="100" ReadOnly="True"/> <dxg:GridColumn FieldName="Remark" Header="Remark" Width="100" ReadOnly="True"/> </dxg:GridControl.Columns> <dxg:GridControl.View> <dxg:TableView x:Name="tvViewGrid" NavigationStyle="Row" ShowGroupPanel="False" AutoWidth="True" FocusedRowChanged="TvViewGrid_FocusedRowChanged" /> </dxg:GridControl.View> </dxg:GridControl> <StackPanel Grid.Column="1" Grid.Row="0" > <dxlc:LayoutControl HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Orientation="Vertical" > <dxlc:LayoutGroup Header="Edit" View="GroupBox" HorizontalAlignment="Stretch" Orientation="Vertical" DataContext="{Binding Path=SelectedItem, ElementName=gcGrid}" > <dxlc:LayoutItem Label="CocktailNo"> <dxe:TextEdit HorizontalAlignment="Left" Margin="0,0,0,0" VerticalAlignment="Top" Width="150" Text="{Binding CocktailNo}"/> </dxlc:LayoutItem> <dxlc:LayoutItem Label="CocktailNmKR"> <dxe:TextEdit HorizontalAlignment="Left" Margin="0,0,0,0" VerticalAlignment="Top" Width="150" Text="{Binding CocktailNmKR}"/> </dxlc:LayoutItem> <dxlc:LayoutItem Label="CocktailNmEn"> <dxe:TextEdit HorizontalAlignment="Left" Margin="0,0,0,0" VerticalAlignment="Top" Width="150" Text="{Binding CocktailNmEn}"/> </dxlc:LayoutItem> <dxlc:LayoutItem Label="CocktailScore"> <dxe:TextEdit HorizontalAlignment="Left" Margin="0,0,0,0" VerticalAlignment="Top" Width="150" Text="{Binding CocktailScore}"/> </dxlc:LayoutItem> <dxlc:LayoutItem Label="Remark"> <dxe:TextEdit HorizontalAlignment="Left" Margin="0,0,0,0" VerticalAlignment="Top" Width="150" Text="{Binding Remark}"/> </dxlc:LayoutItem> </dxlc:LayoutGroup> </dxlc:LayoutControl> </StackPanel> <dxg:GridControl Grid.Column="0" Grid.Row="1" Grid.ColumnSpan="2" x:Name="gcGridDetail" > <dxg:GridControl.Columns > <dxg:GridColumn FieldName="CocktailNo" Header="CocktailNo" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="CocktailMaterialNo" Header="CocktailMaterialNo" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="MaterialCode" Header="MaterialCode" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="MaterialName" Header="MaterialName" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="MaterialQuantity" Header="MaterialQuantity" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="MaterialUnit" Header="MaterialUnit" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="Remark" Header="Remark" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="UnitPrice" Header="UnitPrice" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="CreateUser" Header="CreateUser" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="CreateDate" Header="CreateDate" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="UpdateUser" Header="UpdateUser" Width="110" ReadOnly="True"/> <dxg:GridColumn FieldName="UpdateDate" Header="UpdateDate" Width="110" ReadOnly="True"/> </dxg:GridControl.Columns> <dxg:GridControl.View> <dxg:TableView x:Name="tvViewCommon" NavigationStyle="Row" ShowGroupPanel="False" AutoWidth="True" /> </dxg:GridControl.View> </dxg:GridControl> </Grid> </dxlc:LayoutGroup> </dxlc:LayoutControl> </StackPanel> </Page>

 

 

 

using System; using System.Collections.Generic; using System.Windows; using System.Data; using System.Windows.Controls; using Excel = Microsoft.Office.Interop.Excel; using BIZ.Common.Dac; using System.Runtime.InteropServices; namespace WpfSample.DevTest { /// <summary> /// Grid15ExcelTemplate.xaml에 대한 상호 작용 논리 /// </summary> public partial class Grid15ExcelTemplate : System.Windows.Controls.Page { DataSet dsData = null; public Grid15ExcelTemplate() { InitializeComponent(); GetBindGrid(); } private void GetBindGrid() { Dictionary<string, object> dic = new Dictionary<string, object>(); dic.Add("ComCode", "111"); dsData = (new ADOConnect()).GetDataSet("Kaishaku", "Z_UP_COCKTAIL_LIST", dic); gcGrid.ItemsSource = dsData.Tables[0]; } private void GetBindGridDetail(string param1) { Dictionary<string, object> dic = new Dictionary<string, object>(); dic.Add("PARAM1", param1); var data = (new ADOConnect()).GetDataSet("Kaishaku", "Z_UP_COCKTAIL_MAT_LIST", dic); gcGridDetail.ItemsSource = data.Tables[0]; } private void Button_Click(object sender, RoutedEventArgs e) { GetBindGrid(); } private void TvViewGrid_FocusedRowChanged(object sender, DevExpress.Xpf.Grid.FocusedRowChangedEventArgs e) { GetBindGridDetail(gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CocktailNo").ToString()); } private void BtnExcel_Click(object sender, RoutedEventArgs e) { try { Excel.Application application = new Excel.Application(); Excel.Workbook workbook = null; Excel.Worksheet worksheet = null; workbook = application.Workbooks.Open(@"C:\work\03.GitHub\dotnet\WPF\WPF_Dev_Sample\WpfSample\template.xlsx"); worksheet = workbook.Sheets[1]; Excel.Range range = worksheet.UsedRange; //y, x ((Excel.Range)range.Cells[1, 2]).Value = gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CocktailNmKR"); ((Excel.Range)range.Cells[1, 4]).Value = gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CreateTypeNM"); ((Excel.Range)range.Cells[1, 6]).Value = gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CocktailScore"); ((Excel.Range)range.Cells[2, 2]).Value = gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "Remark"); ((Excel.Range)range.Cells[3, 2]).Value = gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CocktailURL"); DataTable dtDetail = gcGridDetail.ItemsSource as DataTable; for (int i = 0; i < dtDetail.Rows.Count; i++) { //first row copy & paste Excel.Range copyRow = worksheet.Range["D6:G6"].EntireRow; Excel.Range insertRow = worksheet.Range["D"+(7 + i).ToString() + ":G" + (7 + i).ToString()].EntireRow; insertRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow.Copy(Type.Missing)); ((Excel.Range)range.Cells[6 + i, 3]).Value = (i + 1).ToString(); ((Excel.Range)range.Cells[6 + i, 4]).Value = dtDetail.Rows[i]["MaterialName"].ToString(); ((Excel.Range)range.Cells[6 + i, 5]).Value = dtDetail.Rows[i]["MaterialQuantity"].ToString() + dtDetail.Rows[i]["MaterialUnitNM"].ToString(); ((Excel.Range)range.Cells[6 + i, 6]).Value = dtDetail.Rows[i]["Remark"].ToString(); } worksheet.SaveAs(string.Format(@"C:\work\03.GitHub\dotnet\WPF\{0}-{1}.xlsx", gcGrid.GetCellValue(tvViewGrid.FocusedRowHandle, "CocktailNmKR"), DateTime.Now.ToString("hhmmss"))); KillExcel(application); } catch (Exception ex) { } } private void BtnExcelAll_Click(object sender, RoutedEventArgs e) { try { Excel.Application application = new Excel.Application(); Excel.Workbook workbook = null; Excel.Worksheet worksheet = null; workbook = application.Workbooks.Open(@"C:\work\03.GitHub\dotnet\WPF\WPF_Dev_Sample\WpfSample\template.xlsx"); worksheet = workbook.Sheets[1]; Excel.Range range = worksheet.UsedRange; int currentRowNo = 0; //for(int j = 0; j < dsData.Tables[0].Rows.Count; j++ ) Excel.Range copyRow1 = worksheet.Range["A1:G1"].EntireRow; Excel.Range copyRow2 = worksheet.Range["A2:G2"].EntireRow; Excel.Range copyRow3 = worksheet.Range["A3:G3"].EntireRow; Excel.Range copyRow4 = worksheet.Range["A4:G4"].EntireRow; Excel.Range copyRow5 = worksheet.Range["A5:G5"].EntireRow; Excel.Range copyRow6 = worksheet.Range["A6:G6"].EntireRow; //for (int i = 0; i < dsData.Tables[0].Rows.Count; i++) for (int i = 0; i < 10; i++) { if (i == 0) { ExcelMasterDataBind(range, currentRowNo + 1, dsData.Tables[0].Rows[i]); currentRowNo += 7; } else { Excel.Range insertRow1 = worksheet.Range["A" + (currentRowNo + 0).ToString() + ":G" + (currentRowNo + 0).ToString()].EntireRow; insertRow1.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow1.Copy(Type.Missing)); Excel.Range insertRow2 = worksheet.Range["A" + (currentRowNo + 1).ToString() + ":G" + (currentRowNo + 1).ToString()].EntireRow; insertRow2.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow2.Copy(Type.Missing)); Excel.Range insertRow3 = worksheet.Range["A" + (currentRowNo + 2).ToString() + ":G" + (currentRowNo + 2).ToString()].EntireRow; insertRow3.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow3.Copy(Type.Missing)); Excel.Range insertRow4 = worksheet.Range["A" + (currentRowNo + 3).ToString() + ":G" + (currentRowNo + 3).ToString()].EntireRow; insertRow4.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow4.Copy(Type.Missing)); Excel.Range insertRow5 = worksheet.Range["A" + (currentRowNo + 4).ToString() + ":G" + (currentRowNo + 4).ToString()].EntireRow; insertRow5.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow5.Copy(Type.Missing)); Excel.Range insertRow6 = worksheet.Range["A" + (currentRowNo + 5).ToString() + ":G" + (currentRowNo + 5).ToString()].EntireRow; insertRow6.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow6.Copy(Type.Missing)); ExcelMasterDataBind(range, currentRowNo, dsData.Tables[0].Rows[i]); currentRowNo += 6; } DataRow[] dr = dsData.Tables[1].Select(string.Format("CocktailNo = '{0}'", dsData.Tables[0].Rows[i]["CocktailNo"].ToString())); for(int j=0; j < dr.Length; j++) { //first row copy & paste Excel.Range copyRow = worksheet.Range["D6:G6"].EntireRow; Excel.Range insertRow = worksheet.Range["D" + (currentRowNo).ToString() + ":G" + (currentRowNo).ToString()].EntireRow; insertRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, copyRow.Copy(Type.Missing)); ((Excel.Range)range.Cells[currentRowNo -1, 3]).Value = (j + 1).ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 4]).Value = dr[j]["MaterialName"].ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 5]).Value = dr[j]["MaterialQuantity"].ToString() + dr[j]["MaterialUnitNM"].ToString(); ((Excel.Range)range.Cells[currentRowNo - 1, 6]).Value = dr[j]["Remark"].ToString(); currentRowNo++; } } worksheet.SaveAs(string.Format(@"C:\work\03.GitHub\dotnet\WPF\{0}-{1}.xlsx", "ALL", DateTime.Now.ToString("hhmmss"))); KillExcel(application); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void ExcelMasterDataBind(Excel.Range range, int row, DataRow dr) { //y, x range.Cells[row, 2].Value = dr["CocktailNmKR"].ToString(); range.Cells[row, 4].Value = dr["CreateTypeNM"].ToString(); range.Cells[row, 6].Value = dr["CocktailScore"].ToString(); range.Cells[row + 1, 2].Value = dr["Remark"].ToString(); range.Cells[row + 2, 2].Value = dr["CocktailURL"].ToString(); } //process [DllImport("User32.dll")] public static extern int GetWindowThreadProcessId(IntPtr hWnd, out int ProcessId); private static void KillExcel(Excel.Application theApp) { int id = 0; IntPtr intptr = new IntPtr(theApp.Hwnd); System.Diagnostics.Process p = null; try { GetWindowThreadProcessId(intptr, out id); p = System.Diagnostics.Process.GetProcessById(id); if (p != null) { p.Kill(); p.Dispose(); } } catch (Exception ex) { } } } }