private void WriteErrorToExcelFile(string FilePath, int ErrorColumn, List <ImportStockTakingDTO> ErrorList)
{
if (FileIsLocked(FilePath))
{
throw new ApplicationException("File is used by another program.");
}
ExcelControl xlApp = new ExcelControl(FilePath);
xlApp.Hide();
//xlApp.Open(FilePath);
foreach (ImportStockTakingDTO dto in ErrorList)
{
xlApp.WriteCellText(dto.LINE_NO.StrongValue, ErrorColumn, dto.ERROR_DESC.StrongValue);
}
//#################
// Save to file.
//#################
xlApp.Save();
xlApp.Show();
//// Close Excel Application.
//xlApp.Hide();
//xlApp.Dispose();
}
private void btn_run_Click(object sender, EventArgs e)
{
if (textBox_excel.Text == "")
{
MessageBox.Show("Excel 파일을 선택하세요");
}
else if (textBox_dem.Text == "")
{
MessageBox.Show("DEM 파일을 선택하세요");
}
else
{
ap = new Microsoft.Office.Interop.Excel.Application();
string directory = Properties.Settings.Default.EXCEL;
workbook = ap.Workbooks.Open(Filename: directory);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
//첫번째 Worksheet를 선택합니다.
Excel.Range rng = worksheet.UsedRange;
DataTable table = new DataTable();
// 현재 Worksheet에서 사용된 셀 전체를 선택합니다.
object[,] data = rng.Value;
int rCnt = rng.Rows.Count;
int cCnt = rng.Columns.Count;
ExcelControl excelControl = new ExcelControl();
excelControl.addGrid(rCnt, cCnt, data, dataGridView2, combo_st, combo_end, textBox_folder, progressBar1);
keyList = excelControl.keylist();
wbclose = true;
}
}
private string GetVmlClientData(ExcelControl ctrl, XmlElement shapeElement)
{
switch (ctrl.ControlType)
{
case eControlType.Button:
return("<x:TextHAlign>Center</x:TextHAlign>");
case eControlType.CheckBox:
case eControlType.GroupBox:
return("<x:SizeWithCells/><x:NoThreeD/>");
case eControlType.RadioButton:
return("<x:SizeWithCells/><x:AutoLine>False</x:AutoLine><x:NoThreeD/><x:FirstButton/>");
case eControlType.DropDown:
return("<x:SizeWithCells/><x:AutoLine>False</x:AutoLine><x:Val>0</x:Val><x:Min>0</x:Min><x:Max>0</x:Max><x:Inc>1</x:Inc><x:Page>1</x:Page><x:Dx>22</x:Dx><x:Sel>0</x:Sel><x:NoThreeD2/><x:SelType>Single</x:SelType><x:LCT>Normal</x:LCT><x:DropStyle>Combo</x:DropStyle> <x:DropLines>8</x:DropLines>");
case eControlType.ListBox:
return("<x:SizeWithCells/><x:AutoLine>False</x:AutoLine><x:Val>0</x:Val><x:Min>0</x:Min><x:Max>0</x:Max><x:Inc>1</x:Inc><x:Page>7</x:Page><x:Dx>22</x:Dx><x:Sel>0</x:Sel><x:NoThreeD2/><x:SelType>Single</x:SelType><x:LCT>Normal</x:LCT>");
case eControlType.Label:
return("<x:AutoFill>False</x:AutoFill><x:AutoLine>False</x:AutoLine>");
case eControlType.ScrollBar:
return("<x:SizeWithCells/><x:Val>0</x:Val><x:Min>0</x:Min><x:Max>100</x:Max><x:Inc>1</x:Inc><x:Page>10</x:Page><x:Dx>22</x:Dx>");
case eControlType.SpinButton:
return(" <x:Val>0</x:Val><x:Min>0</x:Min><x:Max>30000</x:Max><x:Inc>1</x:Inc><x:Page>10</x:Page><x:Dx>22</x:Dx>");
default:
return("");
}
}
internal ExcelVmlDrawingControl AddControl(ExcelControl ctrl, string name)
{
XmlNode node = AddControlDrawing(ctrl, name);
var draw = new ExcelVmlDrawingControl(_ws, node, NameSpaceManager);
_drawings.Add(draw);
_drawingsDict.Add(draw.Id, _drawings.Count - 1);
return(draw);
}
private string GetFontSize(ExcelControl ctrl)
{
if (ctrl.ControlType == eControlType.Button)
{
return("220");
}
else
{
return("160");
}
}
private string GetFontName(ExcelControl ctrl)
{
if (ctrl.ControlType == eControlType.Button)
{
return("Calibri");
}
else
{
return("Segoe UI");
}
}
private string GetVml(ExcelControl ctrl, XmlElement shapeElement)
{
switch (ctrl.ControlType)
{
case eControlType.Button:
return("<v:fill o:detectmouseclick=\"t\" color2=\"buttonFace[67]\"/>");
case eControlType.CheckBox:
return("<v:path fillok=\"t\" strokeok=\"t\" shadowok=\"t\"/>");
default:
return("");
}
}
/// <summary>
/// Excelコントロールに値を設定する
/// </summary>
/// <param name="procModel"></param>
private void SetExcelControl(ProcessModel procModel)
{
try
{
ExcelControl ctrl = (ExcelControl)ContainerPanel.Controls[0];
ctrl.SetBaseValues(procModel);
switch (procModel.ExcelSourceType)
{
case ExcelSourceType.新規作成:
ctrl.ExcelSourceNewRadio.Checked = true;
break;
case ExcelSourceType.既存ファイル:
ctrl.ExcelSourceExistsRadio.Checked = true;
break;
}
ctrl.ExcelLoadFilePathTextBox.Text = procModel.FileFolderPath1;
ctrl.ExcelSaveFilePathTextBox.Text = procModel.FileFolderPath2;
ctrl.ExcelJobGrid.Rows.Clear();
foreach (KeyValuePair <int, ExcelJobModel> pair in procModel.ExcelJobList)
{
ctrl.ExcelJobGrid.Rows.Add();
ctrl.ExcelJobGrid.Rows[pair.Key].Cells[ctrl.COL_処理タイプ].Value = pair.Value.FileReadWriteType == FileReadWriteType.Read ? "読み込み" : "書き込み";
ctrl.ExcelJobGrid.Rows[pair.Key].Cells[ctrl.COL_シート名].Value = pair.Value.SheetName;
ctrl.ExcelJobGrid.Rows[pair.Key].Cells[ctrl.COL_セル名].Value = pair.Value.CellName;
ctrl.ExcelJobGrid.Rows[pair.Key].Cells[ctrl.COL_値].Value = pair.Value.Value;
}
if (ctrl.ExcelJobGrid.Rows.Count > 1)
{
ctrl.ExcelJobGrid.Rows[ctrl.ExcelJobGrid.Rows.Count - 1].Cells[ctrl.COL_処理タイプ].Value = ctrl.ExcelJobGrid.Rows[ctrl.ExcelJobGrid.Rows.Count - 2].Cells[ctrl.COL_処理タイプ].Value;
ctrl.ExcelJobGrid.Rows[ctrl.ExcelJobGrid.Rows.Count - 1].Cells[ctrl.COL_シート名].Value = ctrl.ExcelJobGrid.Rows[ctrl.ExcelJobGrid.Rows.Count - 2].Cells[ctrl.COL_シート名].Value;
}
else
{
ctrl.CreateFirstRow();
}
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
private XmlNode AddControlDrawing(ExcelControl ctrl, string name)
{
var shapeElement = VmlDrawingXml.CreateElement("v", "shape", ExcelPackage.schemaMicrosoftVml);
VmlDrawingXml.DocumentElement.AppendChild(shapeElement);
shapeElement.SetAttribute("spid", ExcelPackage.schemaMicrosoftOffice, "_x0000_s" + ctrl.Id);
shapeElement.SetAttribute("id", name);
//shapeElement.SetAttribute("id", $"{ctrl.ControlTypeString}_x{ctrl.Id}_1");
shapeElement.SetAttribute("type", "#_x0000_t201");
shapeElement.SetAttribute("style", "position:absolute;z-index:1;");
shapeElement.SetAttribute("insetmode", ExcelPackage.schemaMicrosoftOffice, "auto");
SetShapeAttributes(ctrl, shapeElement);
var vml = new StringBuilder();
vml.Append(GetVml(ctrl, shapeElement));
vml.Append("<o:lock v:ext=\"edit\" rotation=\"t\"/>");
vml.Append("<v:textbox style=\"mso-direction-alt:auto\" o:singleclick=\"f\">");
if (ctrl is ExcelControlWithText textControl)
{
vml.Append($"<div style=\"text-align:center\"><font color=\"#000000\" size=\"{GetFontSize(ctrl)}\" face=\"{GetFontName(ctrl)}\">{textControl.Text}</font></div>");
}
vml.Append("</v:textbox>");
vml.Append($"<x:ClientData ObjectType=\"{ctrl.ControlTypeString}\">");
vml.Append(string.Format("<x:Anchor>{0}</x:Anchor>", ctrl.GetVmlAnchorValue()));
vml.Append(GetVmlClientData(ctrl, shapeElement));
vml.Append("<x:PrintObject>False</x:PrintObject>");
vml.Append("<x:AutoFill>False</x:AutoFill>");
if (ctrl.ControlType != eControlType.GroupBox)
{
vml.Append("<x:TextVAlign>Center</x:TextVAlign>");
}
vml.Append("</x:ClientData>");
shapeElement.InnerXml = vml.ToString();
return(shapeElement);
}
private void SetShapeAttributes(ExcelControl ctrl, XmlElement shapeElement)
{
switch (ctrl.ControlType)
{
case eControlType.Button:
shapeElement.SetAttribute("fillcolor", "buttonFace [67]");
shapeElement.SetAttribute("strokecolor", "windowText [64]");
shapeElement.SetAttribute("button", ExcelPackage.schemaMicrosoftOffice, "t");
break;
case eControlType.CheckBox:
case eControlType.RadioButton:
shapeElement.SetAttribute("fillcolor", "windows [65]");
shapeElement.SetAttribute("strokecolor", "windowText [64]");
//shapeElement.SetAttribute("button", ExcelPackage.schemaMicrosoftOffice, "t");
shapeElement.SetAttribute("stroked", "f");
shapeElement.SetAttribute("filled", "f");
//style = "position:absolute; margin-left:15pt;margin-top:10.5pt;width:120.75pt;height:23.25pt;z-index:1; mso-wrap-style:tight" type = "#_x0000_t201" >
break;
case eControlType.ListBox:
case eControlType.DropDown:
shapeElement.SetAttribute("strokecolor", "windowText [64]");
shapeElement.SetAttribute("stroked", "f");
break;
case eControlType.ScrollBar:
shapeElement.SetAttribute("strokecolor", "windowText [64]");
break;
case eControlType.Label:
shapeElement.SetAttribute("fillcolor", "windows [65]");
shapeElement.SetAttribute("strokecolor", "windowText [64]");
shapeElement.SetAttribute("stroked", "f");
shapeElement.SetAttribute("filled", "f");
break;
}
}
private void RefreshPreviewContent()
{
String value = this.Preview_RadioButtonList.SelectedValue;
String page = String.Empty;
Outage outage = (Outage)Session["Outage"];
String ExcelPath = Server.MapPath("~/excel/Blog.xlsx");
switch (value.ToLower())
{
case "email": page = "Customers.aspx";
this.EmailControl.Visible = true;
this.KBControl.Visible = false;
this.ExcelControl.Visible = false;
EmailControl.Refresh(outage);
break;
case "kb": page = "Knowledgebase.aspx";
this.EmailControl.Visible = false;
this.KBControl.Visible = true;
this.ExcelControl.Visible = false;
KBControl.Refresh(outage);
break;
case "excel": page = "Excel.aspx";
this.EmailControl.Visible = false;
this.KBControl.Visible = false;
OutageRecovery or = GetOutages();
String newFilePath = new ExcelMarshall(or, ExcelPath, 5).Marshall();
ExcelControl.Refresh("~/excel/" + newFilePath);
this.ExcelControl.Visible = true;
break;
default: return;
}
///this.PreviewFrame.Attributes["src"] = page;
}
private void SetResource(string filePath, bool loadWithTypeNone = false)
{
var typeInfo = FileType.GetFileViewType(filePath);
if (loadWithTypeNone)
{
typeInfo.Type = FileViewType.None;
}
if (lastViewType != typeInfo.Type || MyGrid.Children[0] is HelloControl)
{
lastViewType = typeInfo.Type;
MyGrid.Children.Clear();
}
else if (MyGrid.Children.Count == 1)
{
(MyGrid.Children[0] as FileControl).OnFileChanged((filePath, typeInfo.Ext));
return;
}
FileControl fc;
switch (typeInfo.Type)
{
case FileViewType.Image:
fc = new ImageControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.Code:
case FileViewType.Txt:
fc = new TextControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.Music:
fc = new MusicControl();
GlobalNotify.OnResizeMode(false);
break;
case FileViewType.Video:
fc = new VideoControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.Pdf:
fc = new PdfControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.Excel:
fc = new ExcelControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.Word:
fc = new WordControl();
GlobalNotify.OnResizeMode(true);
break;
case FileViewType.PowerPoint:
fc = new PowerPointControl();
GlobalNotify.OnResizeMode(true);
break;
default:
fc = new CommonControl();
GlobalNotify.OnResizeMode(false);
break;
}
if (fc != null)
{
LoadFile(fc, filePath, typeInfo.Ext);
}
}
