public void Close()
{
if (_workbook == null) return;
_workbook.Close(0, 0, 0);
_workbook = null;
}
public ExcelWriter(string _fileName)
{
fileName = _fileName;
if(File.Exists(FilePath))
throw new ApplicationException("File already exists: " + FilePath);
File.Create(FilePath);
app = new Microsoft.Office.Interop.Excel.Application();
Console.Error.WriteLine("Connected to Excel");
wbs = app.Workbooks;
wb = wbs.Add(1);
wb.Activate();
wss = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
Console.Error.WriteLine("Excel Worksheet Initialized");
}
public void CreateExcelFile()
{
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
AddExcelProcessTolist();
}
//*************************************************************************
// Constructor: GeneralUserSettingsDialog()
//
/// <summary>
/// Initializes a new instance of the <see
/// cref="GeneralUserSettingsDialog" /> class.
/// </summary>
///
/// <param name="generalUserSettings">
/// The object being edited.
/// </param>
///
/// <param name="workbook">
/// Workbook containing the graph data.
/// </param>
//*************************************************************************
public GeneralUserSettingsDialog(
GeneralUserSettings generalUserSettings,
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(generalUserSettings != null);
Debug.Assert(workbook != null);
generalUserSettings.AssertValid();
m_oGeneralUserSettings = generalUserSettings;
m_oWorkbook = workbook;
m_oAxisFont = m_oGeneralUserSettings.AxisFont;
m_oLabelUserSettings = m_oGeneralUserSettings.LabelUserSettings;
// Instantiate an object that saves and retrieves the position of this
// dialog. Note that the object automatically saves the settings when
// the form closes.
m_oGeneralUserSettingsDialogUserSettings =
new GeneralUserSettingsDialogUserSettings(this);
InitializeComponent();
nudEdgeWidth.Minimum =
(Decimal)EdgeWidthConverter.MinimumWidthWorkbook;
nudEdgeWidth.Maximum =
(Decimal)EdgeWidthConverter.MaximumWidthWorkbook;
nudSelectedEdgeWidth.Minimum =
(Decimal)EdgeWidthConverter.MinimumWidthWorkbook;
nudSelectedEdgeWidth.Maximum =
(Decimal)EdgeWidthConverter.MaximumWidthWorkbook;
nudRelativeArrowSize.Minimum =
(Decimal)EdgeDrawer.MinimumRelativeArrowSize;
nudRelativeArrowSize.Maximum =
(Decimal)EdgeDrawer.MaximumRelativeArrowSize;
nudVertexRadius.Minimum = nudVertexImageSize.Minimum =
(Decimal)VertexRadiusConverter.MinimumRadiusWorkbook;
nudVertexRadius.Maximum = nudVertexImageSize.Maximum =
(Decimal)VertexRadiusConverter.MaximumRadiusWorkbook;
( new VertexShapeConverter() ).PopulateComboBox(cbxVertexShape, false);
nudVertexAlpha.Minimum = nudEdgeAlpha.Minimum =
(Decimal)AlphaConverter.MinimumAlphaWorkbook;
nudVertexAlpha.Maximum = nudEdgeAlpha.Maximum =
(Decimal)AlphaConverter.MaximumAlphaWorkbook;
DoDataExchange(false);
AssertValid();
}
public ExcelFile(string filePath)
{
_application = new Microsoft.Office.Interop.Excel.Application();
_workbook = _application.Workbooks.Open(filePath);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets.Item[1];
_filename = filePath;
_currentSheetNumber = 1;
}
public string GetValue(string filename, int row, int col)
{
string fileaddress = Constants.BaseDirectory + filename;
//app.Workbooks.Open(fileaddress, 0, false, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, "", "", false,
// Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, false, false, false);
workbook = app.Workbooks.Open(fileaddress, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string result = (string)worksheet.Columns[row, col];
return result;
}
public DaxStudioWindow(Microsoft.Office.Interop.Excel.Application app)
{
InitializeComponent();
Application = app;
// Insert code required on object creation below this point.
daxEditorUserControl1.AllowDrop = true;
daxEditorUserControl1.Drop += UcDaxEditorDrop;
_xlHelper = new ExcelHelper(_app, cboOutputTo);
_workbook = Application.ActiveWorkbook;
this.btnRunGrid.Click += OnRunGrdClick;
}
public void Create()
{
excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
workBook = excelApplication.Workbooks.Add();
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
createHeader();
}
public RunDialog(Microsoft.Office.Interop.Excel.Workbook wb)
{
_workbook = wb;
InitializeComponent();
buttonCancel.DialogResult = DialogResult.Cancel;
buttonOK.DialogResult = DialogResult.OK;
buttonOK.Enabled = false;
Simulation.Matrix_Worksheet.OnStartProcessingWorksheet += new HydroSharedAddIn.Simulation.Matrix_Worksheet.OnStartProcessingWorksheetHandler(Matrix_Worksheet_OnStartProcessingWorksheet);
Simulation.Subreach_Excel.OnSubreachCompleted += new HydroSharedAddIn.Simulation.Subreach_Excel.OnSubreachCompletedHandler(Subreach_Excel_OnSubreachCompleted);
Simulation.Subreach_Excel.OnError += new HydroSharedAddIn.Simulation.Subreach_Excel.OnErrorHandler(Subreach_Excel_OnError);
excel.RoutingSimulation.OnBeginSpreadsheetRecalc += new HydroSharedAddIn.excel.RoutingSimulation.OnBeginSpreadsheetRecalcHandler(RoutingSimulation_OnBeginSpreadsheetRecalc);
excel.RoutingSimulation.OnEndSpreadsheetRecalc += new HydroSharedAddIn.excel.RoutingSimulation.OnEndSpreadsheetRecalcHandler(RoutingSimulation_OnEndSpreadsheetRecalc);
excel.RoutingSimulation.OnRunCompleted += new HydroSharedAddIn.excel.RoutingSimulation.OnRunCompletedHandler(RoutingSimulation_OnRunCompleted);
}
public void createDoc()
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
Console.Write("Error : " + e.Message);
}
finally
{
}
}
public bool CreateExcel(List<Po> pos)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Po po in pos)
{
xlWorkSheet.Cells[po.Row, po.Column] = po.Value;
}
xlWorkBook.SaveAs("C:\\newcat2", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
int test = 0;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
private void FillCbSheetList()
{
wb = FSCYN.GetExcelWorkbook(txtFile.Text);
foreach (Microsoft.Office.Interop.Excel.Worksheet sheets in wb.Worksheets)
cbSheet.Items.Add((object)sheets.Name);
}
void _ExcelApplication_WindowActivate(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.Window Wn)
{
DisplayInWatchWindow(WindowActivate++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
void _ExcelApplication_WorkbookAfterXmlExport(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.XmlMap Map, string Url, Microsoft.Office.Interop.Excel.XlXmlExportResult Result)
{
DisplayInWatchWindow(WorkbookAfterXmlExport++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
void _ExcelApplication_WorkbookBeforeXmlImport(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.XmlMap Map, string Url, bool IsRefresh, ref bool Cancel)
{
DisplayInWatchWindow(WorkbookBeforeXmlImport++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
/// <summary>
/// ExportDataToDataTable
/// </summary>
/// <param name="excelRangeName">Name given to a range of cells in the document.</param>
/// <param name="columnNamesInFirstRow">If true, then export will assume first row contains column names.</param>
/// <returns>DataTable object that contains the values in the given excelNameRange.</returns>
public DataTable ExportExcelDataToDataTable(string excelRangeName, bool columnNamesInFirstRow)
{
DataTable dt = null;
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
if (this.ExcelOutputFormat == enExcelOutputFormat.CSV)
{
_msg.Length = 0;
_msg.Append("Unable to export data from a CSV file using a named range.");
throw new System.Exception(_msg.ToString());
}
if (File.Exists(this.DocumentFilePath) == false)
{
_msg.Length = 0;
_msg.Append("File ");
_msg.Append(this.DocumentFilePath);
_msg.Append(" does not exist. Will not be able to export data.");
throw new System.Exception(_msg.ToString());
}
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(this.DocumentFilePath);
Microsoft.Office.Interop.Excel.Worksheet ws = null;
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Sheets)
{
if (sh.Name == this.SheetName)
{
ws = sh;
}
}
if (ws == null)
{
_msg.Length = 0;
_msg.Append("Sheet ");
_msg.Append(this.SheetName);
_msg.Append(" does not exist. Will not be able to export data.");
throw new System.Exception(_msg.ToString());
}
Microsoft.Office.Interop.Excel.Range rng = null;
try
{
rng = ws.get_Range(excelRangeName, Type.Missing);
}
catch
{
rng = null;
}
if (rng == null)
{
_msg.Length = 0;
_msg.Append("Range ");
_msg.Append(excelRangeName);
_msg.Append(" does not exist. Will not be able to export data.");
throw new System.Exception(_msg.ToString());
}
dt = ExportExcelDataToDataTable(rng.Row, rng.Column, rng.Rows.Count, rng.Columns.Count, columnNamesInFirstRow);
wb.Close(false);
excelApp.Quit();
excelApp = null;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to export Excel data to DataTable failed.");
_msg.Append(Environment.NewLine);
_msg.Append(PFTextProcessor.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
}
return(dt);
}
protected override void OnActivated(EventArgs e)
{
base.OnActivated(e);
if (!_has_run)
{
_has_run = true;
_simulation = new HydroSharedAddIn.excel.RoutingSimulation();
_simulation.DoEstablishWorksheetNames(_simulation.SimulationWorksheetNames);
// start the simulation...
this.toolStripStatusLabel1.Text = "begin simulations";
this.buttonOK.Enabled = false;
buttonOK.DialogResult = DialogResult.OK;
if (true)
{
Run();
_simulation = null;
_workbook = null;
GC.Collect();
lock (this)
{
buttonOK.Enabled = true;
currentSimulationLabel1.Text = "Successfully completed all simulations";
toolStripProgressBar1.Value = toolStripProgressBar1.Minimum;
toolStripStatusLabel1.Text = "done";
}
}
else
{
RunAsynch();
}
}
}
private void setExcel()
{
if (ss1.ActiveSheet.RowCount <= 0)
{
return;
}
string modelName = "CKP1310C.xls";
GeneralCommon.Gp_CopyModel(modelName);
string fileName = AppDomain.CurrentDomain.BaseDirectory.ToString() + "report" + "\\" + modelName;
Microsoft.Office.Interop.Excel.Application appExcel = null;
appExcel = new Microsoft.Office.Interop.Excel.Application();
appExcel.DisplayAlerts = true;
appExcel.AlertBeforeOverwriting = true;
Microsoft.Office.Interop.Excel.Workbook workbook = appExcel.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Range range = null;
appExcel.Cells[2, 1] = "报表日期：" + txt_DATE.RawDate.Substring(0, 4) + "年" + txt_DATE.RawDate.Substring(4, 2) + "月" + txt_DATE.RawDate.Substring(6, 2) + "日";
appExcel.Cells[35, 2] = "制表日期: " + DateTime.Now.ToString("yyyy") + "年" + DateTime.Now.ToString("MM") + "月" + DateTime.Now.ToString("dd") + "日";
appExcel.Cells[35, 11] = "制表人：" + GeneralCommon.sUserID;
for (int i = 0; i < ss1.ActiveSheet.RowCount; i++)
{
appExcel.Cells[5 + i, 3] = ss1.ActiveSheet.Cells[i, 0].Text;
appExcel.Cells[5 + i, 4] = ss1.ActiveSheet.Cells[i, 1].Text;
appExcel.Cells[5 + i, 5] = ss1.ActiveSheet.Cells[i, 2].Text;
appExcel.Cells[5 + i, 6] = ss1.ActiveSheet.Cells[i, 3].Text;
appExcel.Cells[5 + i, 7] = ss1.ActiveSheet.Cells[i, 4].Text;
appExcel.Cells[5 + i, 8] = ss1.ActiveSheet.Cells[i, 5].Text;
appExcel.Cells[5 + i, 9] = ss1.ActiveSheet.Cells[i, 6].Text;
appExcel.Cells[5 + i, 10] = ss1.ActiveSheet.Cells[i, 7].Text;
appExcel.Cells[5 + i, 11] = ss1.ActiveSheet.Cells[i, 8].Text;
appExcel.Cells[5 + i, 12] = ss1.ActiveSheet.Cells[i, 9].Text;
appExcel.Cells[5 + i, 13] = ss1.ActiveSheet.Cells[i, 10].Text;
appExcel.Cells[5 + i, 14] = ss1.ActiveSheet.Cells[i, 11].Text;
appExcel.Cells[5 + i, 15] = ss1.ActiveSheet.Cells[i, 12].Text;
appExcel.Cells[5 + i, 16] = ss1.ActiveSheet.Cells[i, 13].Text;
appExcel.Cells[5 + i, 17] = ss1.ActiveSheet.Cells[i, 14].Text;
}
for (int i = 0; i < ss2.ActiveSheet.RowCount; i++)
{
appExcel.Cells[18 + i, 3] = ss2.ActiveSheet.Cells[i, 0].Text;
appExcel.Cells[18 + i, 4] = ss2.ActiveSheet.Cells[i, 1].Text;
appExcel.Cells[18 + i, 5] = ss2.ActiveSheet.Cells[i, 2].Text;
appExcel.Cells[18 + i, 6] = ss2.ActiveSheet.Cells[i, 3].Text;
appExcel.Cells[18 + i, 7] = ss2.ActiveSheet.Cells[i, 4].Text;
appExcel.Cells[18 + i, 8] = ss2.ActiveSheet.Cells[i, 5].Text;
appExcel.Cells[18 + i, 9] = ss2.ActiveSheet.Cells[i, 6].Text;
appExcel.Cells[18 + i, 10] = ss2.ActiveSheet.Cells[i, 7].Text;
}
for (int i = 0; i < ss3.ActiveSheet.RowCount; i++)
{
if (i <= 4)
{
appExcel.Cells[27 + i, 2] = ss3.ActiveSheet.Cells[i, 0].Text;
appExcel.Cells[27 + i, 3] = ss3.ActiveSheet.Cells[i, 1].Text;
appExcel.Cells[27 + i, 4] = ss3.ActiveSheet.Cells[i, 2].Text;
appExcel.Cells[27 + i, 5] = ss3.ActiveSheet.Cells[i, 3].Text;
appExcel.Cells[27 + i, 6] = ss3.ActiveSheet.Cells[i, 4].Text;
appExcel.Cells[27 + i, 7] = ss3.ActiveSheet.Cells[i, 5].Text;
appExcel.Cells[27 + i, 8] = ss3.ActiveSheet.Cells[i, 6].Text;
appExcel.Cells[27 + i, 9] = ss3.ActiveSheet.Cells[i, 7].Text;
appExcel.Cells[27 + i, 10] = ss3.ActiveSheet.Cells[i, 8].Text;
appExcel.Cells[27 + i, 11] = ss3.ActiveSheet.Cells[i, 9].Text;
appExcel.Cells[27 + i, 12] = ss3.ActiveSheet.Cells[i, 10].Text;
appExcel.Cells[27 + i, 13] = ss3.ActiveSheet.Cells[i, 11].Text;
appExcel.Cells[27 + i, 14] = ss3.ActiveSheet.Cells[i, 12].Text;
appExcel.Cells[27 + i, 15] = ss3.ActiveSheet.Cells[i, 13].Text;
appExcel.Cells[27 + i, 16] = ss3.ActiveSheet.Cells[i, 14].Text;
}
appExcel.Cells[28 + i, 3] = ss3.ActiveSheet.Cells[i, 1].Text;
appExcel.Cells[28 + i, 5] = ss3.ActiveSheet.Cells[i, 3].Text;
appExcel.Cells[28 + i, 7] = ss3.ActiveSheet.Cells[i, 5].Text;
appExcel.Cells[28 + i, 10] = ss3.ActiveSheet.Cells[i, 7].Text;
appExcel.Cells[28 + i, 12] = ss3.ActiveSheet.Cells[i, 9].Text;
appExcel.Cells[28 + i, 14] = ss3.ActiveSheet.Cells[i, 11].Text;
appExcel.Cells[28 + i, 16] = ss3.ActiveSheet.Cells[i, 13].Text;
}
appExcel.Visible = true;
appExcel = null;
}
public void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
Data data = (Data) e.Argument; // Cast e argument to a local Data instance
// Start Excel/Multiframe calls - Either 11 or 20 stages, depending on debugMode (11 is debugMode, skips creating frame. Does debug info only)
bool debugMode = Properties.Settings.Default.DebugMode;
int progressTotal = debugMode == true ? 11 : 20;
int progressCount = 0;
bool unableToAddQ2Loads = false;
try
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Opening Excel"); progressCount++;
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = Properties.Settings.Default.DisplayExcel;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Open(fileName);
ws = wb.Sheets.get_Item(1); // Expects "Input" sheet to be the first worksheet
// Insert values into Excel Inputs Sheet
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Inserting values into Input Sheet"); progressCount++;
ws.Range["windSpeedVu"].Value = data.ultimateWindSpeed;
ws.Range["windSpeedVs"].Value = data.serviceWindSpeed;
ws.Range["internalPressure"].Value = data.internalPressure;
ws.Range["shedType"].Value = data.roofType;
ws.Range["shedWallType"].Value = data.wallType;
ws.Range["shedEaveHeight"].Value = data.eaveHeight;
ws.Range["shedRoofPitch"].Value = data.roofPitch;
ws.Range["shedSpan"].Value = data.span;
ws.Range["shedBaySpacing"].Value = data.baySpacing;
ws.Range["shedNumberOfBays"].Value = data.numberOfBays;
ws.Range["shedSupports"].Value = data.supports;
ws.Range["shedEndColumnType"].Value = data.endColumnType;
ws.Range["shedEndColumnSection"].Value = data.endColumnSection;
ws.Range["shedEndRafterType"].Value = data.endRafterType;
ws.Range["shedEndRafterSection"].Value = data.endRafterSection;
ws.Range["shedMidColumnType"].Value = data.midColumnType;
ws.Range["shedMidColumnSection"].Value = data.midColumnSection;
ws.Range["shedMidRafterType"].Value = data.midRafterType;
ws.Range["shedMidRafterSection"].Value = data.midRafterSection;
ws.Range["shedMullions"].Value = data.numberOfMullions;
ws.Range["shedMullionsType"].Value = data.mullionType;
ws.Range["shedMullionsSection"].Value = data.mullionSection;
ws.Range["shedCompressionStrutType"].Value = data.strutType;
ws.Range["shedCompressionStrutSection"].Value = data.strutSection;
ws.Range["shedKneeBraceEnd"].Value = data.endKneeBraceType == "None" ? "no" : "yes";
if (data.endKneeBraceType != "None")
{
ws.Range["shedKneeBraceEndType"].Value = data.endKneeBraceType;
ws.Range["shedKneeBraceEndSection"].Value = data.endKneeBraceSection;
}
ws.Range["shedKneeBraceMid"].Value = data.midKneeBraceType == "None" ? "no" : "yes";
if (data.midKneeBraceType != "None")
{
ws.Range["shedKneeBraceMidType"].Value = data.midKneeBraceType;
ws.Range["shedKneeBraceMidSection"].Value = data.midKneeBraceSection;
}
ws.Range["shedKneeBracePercentEave"].Value = data.kneeBracePercentEave;
ws.Range["shedKneeBracePercentSpan"].Value = data.kneeBracePercentSpan;
ws.Range["shedApexBraceEnd"].Value = data.endApexBraceType == "None" ? "no" : "yes";
if (data.endApexBraceType != "None")
{
ws.Range["shedApexBraceEndType"].Value = data.endApexBraceType;
ws.Range["shedApexBraceEndSection"].Value = data.endApexBraceSection;
}
ws.Range["shedApexBraceMid"].Value = data.midApexBraceType == "None" ? "no" : "yes";
if (data.midApexBraceType != "None")
{
ws.Range["shedApexBraceMidType"].Value = data.midApexBraceType;
ws.Range["shedApexBraceMidSection"].Value = data.midApexBraceSection;
}
ws.Range["shedEavePurlinType"].Value = data.eavePurlinType;
ws.Range["shedEavePurlinSection"].Value = data.eavePurlinSection;
ws.Range["shedRoofPurlinType"].Value = data.strutType;
ws.Range["shedRoofPurlinSection"].Value = data.strutSection;
// Start calling macros from Excel Workbook
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting initial excel values"); progressCount++;
excelApp.Run("CsharpSetExcelInputValues");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting units in Multiframe"); progressCount++;
excelApp.Run("CsharpSetUnits");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Frame"); progressCount++;
excelApp.Run("CsharpCreateFrame");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Knee Braces"); progressCount++;
excelApp.Run("CsharpAddKneeBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Apex Braces"); progressCount++;
excelApp.Run("CsharpAddApexBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Mullions"); progressCount++;
excelApp.Run("CsharpAddMullions");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Eave Purlins"); progressCount++;
excelApp.Run("CsharpAddEavePurlins");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Restraints and Sections"); progressCount++;
excelApp.Run("CsharpAddRestraintsAndSections");
}
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Wind Pressures"); progressCount++;
excelApp.Run("CsharpGetWindPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Longwind Reductions"); progressCount++;
excelApp.Run("CsharpGetLWPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Copying Pressures to Loads Sheet"); progressCount++;
excelApp.Run("CsharpCopyPressures");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating New Load Cases"); progressCount++;
excelApp.Run("CsharpCreateNewLoadCases");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting up Loads"); progressCount++;
excelApp.Run("CsharpAddLoadsSetup");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Q2 Loads"); progressCount++;
unableToAddQ2Loads = (bool)excelApp.Run("CsharpAddQ2Loads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Portal Loads"); progressCount++;
excelApp.Run("CsharpAddEndPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Mid Portal Loads"); progressCount++;
excelApp.Run("CsharpAddMidPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Wall Loads"); progressCount++;
excelApp.Run("CsharpAddEndWallLoads");
}
if (debugMode == true)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Geometry Ratios"); progressCount++;
data.excelGeometryRatios = (double[]) excelApp.Run("CsharpGetGeometryRatiosDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Pressure Coefficients"); progressCount++;
data.excelPressureCoefficients = (double[]) excelApp.Run("CsharpGetPressureCoefficientsDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Load Cases"); progressCount++;
data.excelLoadCases = (string[])excelApp.Run("CsharpGetLoadCasesDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Loads"); progressCount++;
data.excelLoads = (string[,]) excelApp.Run("CsharpGetLoadsDebug");
}
// Alert if Q2 loads were unable to be added
if (unableToAddQ2Loads == true)
MessageBox.Show("Q2 loads were unable to be added. You will need to add them manually", "Unable to add Q2 Loads");
}
catch (Exception ex)
{
if (ex is ArgumentException || ex is ArgumentNullException)
MessageBox.Show("Error closing Excel (Marshal.ReleaseFinalComObject)\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
else
MessageBox.Show("Error closing Excel\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
}
finally
{
// Close Excel
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Closing Excel"); progressCount++;
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
wb.Close();
Marshal.FinalReleaseComObject(wb);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
/// <summary>
/// 获取Workbook
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
public Microsoft.Office.Interop.Excel.Workbook GetExcelWorkbook()
{
if (_tables.Count == 0)
{
throw new Exception("Tables集合必须大于零!");
}
if (_rowindex < 0)
{
_rowindex = 0;
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wbs = xlApp.Workbooks;
wb = wbs.Add(Missing.Value); //添加一个工作簿
//添加Sheet表,新建一个Excel文件时候,一般会默认有3个Sheet表,所以用[table.Count - wb.Sheets.Count]
int tabcount = _tables.Count;
int sheets = wb.Worksheets.Count; //获取默认Sheet表个数,一般默认3个
if (tabcount > sheets)
{
wb.Worksheets.Add(Missing.Value, Missing.Value, tabcount - sheets, Missing.Value);
}
//删除多余Sheet表
//((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]).Delete();
//写入Excel
WriteExcelSheet(wb);
//保存工作表
//xlApp.ActiveWorkbook.SaveCopyAs(filename);
//wb.SaveCopyAs(_savepath);
return wb;
}
private void Close()
{
#region 关闭Excel进程
if (wb != null)
{
wb.Close(false, _savepath, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
if (wbs != null)
{
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
}
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
#endregion
}
/// <summary>
/// 销毁Excel内存
/// </summary>
public void CloseExcel()
{
workbook.Close(false, null, null);
//退出Excel，并且释放调用的COM资源
excel.Quit();
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sh2);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sh3);
}
catch { }
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
catch { }
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
catch { }
worksheet = null;
sh2 = null;
sh3 = null;
workbook = null;
excel = null;
GC.Collect();
KillExcelProcess();
}
public ArrayList DoPrint()
{
//根据配置文件读取有关excel模版的信息
_excelName = IniReadValue("模版信息", _printType);
_excelWindowsName = "Microsoft Excel - " + _excelName+" [只读]";
_excelPath = Application.StartupPath + "\\" + _excelName;
//当条形码打印成功后增加到arraylist里返回到调用方设置已打印标志
ArrayList _arPrinted = new ArrayList();
try
{
if (_printData == null || _printData.Tables.Count == 0 || _printData.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无打印数据!", "提示");
CloseExcel();
return _arPrinted;
}
//检验是否取到模版信息，否则退出
if (_excelName == "" || _excelWindowsName == "" || _excelPath == "")
{
MessageBox.Show("模版信息维护错误！", "提示");
CloseExcel();
return _arPrinted;
}
//判断excel模版是否已经被打开，打开excel模版
IntPtr a = FindWindow(null, _excelWindowsName);
if (a.ToString() != "0")
{
MessageBox.Show("Excel模版已经被打开,可能是正在打印或人为打开。\n请确认没有执行打印程序,然后手动关闭该EXCEL文档。", "提示");
CloseExcel();
return _arPrinted;
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;
excel.Top = 8000;
excel.WorkbookActivate+=new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);
string fileName = _excelPath;
workbook = excel.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,missing, missing);
workbook.ReadOnlyRecommended = true;
Int32 sheetNum1 = 0;
try
{
sheetNum1 = Convert.ToInt32(IniReadValue(_printType, "FIELDNUM"));
}
catch
{
MessageBox.Show("配置文件-" + _printType + "-FIELDNUM，维护错误");
CloseExcel();
return _arPrinted;
}
Hashtable hs = new Hashtable();
Hashtable hs2 = new Hashtable();
for (Int32 s = 1; s < sheetNum1 + 1; s++)
{
string FieldValue = IniReadValue(_printType, "F" + s.ToString());
hs.Add("F" + s.ToString(), FieldValue);
string FieldCell = IniReadValue(_printType, "C" + s.ToString());
hs2.Add("C" + s.ToString(), FieldCell);
}
_iniTable.Add(_printType, hs);
_iniCell.Add(_printType, hs2);
//针对每一行数据生成数据表
Int32 num = _printData.Tables[0].Rows.Count;
sh2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType);
for (int i = 1; i < num+1; i++)
{
if (i == 1)
{
sh3 = sh2;
}
else
{
sh3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType + " (" + Convert.ToString(i) + ")");
}
sh2.Copy(Type.Missing, sh3);
}
string Barcode = "";
for (int i = 0; i < num; i++)
{
//根据配置文件设置excel中的数据
DataRow r = _printData.Tables[0].Rows[i];
Barcode = r["条码号"].ToString();
string sheet = "";
sheet = _printType + " (" + Convert.ToString(i + 2) + ")";
try
{
GetWorkSheet(sheet);
}
catch { MessageBox.Show("找不到打印模版表" + sheet, "提示"); return _arPrinted; }
Hashtable ht = (Hashtable)_iniTable[_printType];
Hashtable ht2 = (Hashtable)_iniCell[_printType];
foreach (DictionaryEntry de in ht)
{
try
{
SetCellRangeValue(ht2["C" + de.Key.ToString().Substring(1)].ToString(), r[de.Value.ToString()].ToString());
}
catch { }
}
SetCellRangeValue("A10", r["条码号"].ToString());
_arPrinted.Add(r["条码号"].ToString());
}
//调用VBA宏生成条形码
object robj = new object();
try
{
RunExcelMacro(_excelPath, "getTime3", new Object[] { "" }, out robj, true);
}
catch
{
MessageBox.Show("执行宏失败！", "提示");
CloseExcel();
return _arPrinted;
}
Thread.Sleep(1000);
//打印输出
try
{
for (int i = 0; i < Convert.ToInt32(_printCopies); i++)
{
workbook.Worksheets.PrintOut(2, num+1, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
catch
{
MessageBox.Show("连接打印机失败，请检查操作系统中默认打印机是否运行正常！", "提示");
_arPrinted.Clear();
CloseExcel();
return _arPrinted;
}
CloseExcel();
return _arPrinted;
}
catch
{
CloseExcel();
return _arPrinted;
}
}
public ExcelSpreadsheetReturnEventArgs(Microsoft.Office.Interop.Excel.Application excelApplication, Microsoft.Office.Interop.Excel.Workbook workBook)
{
this.m_ExcelApplication = excelApplication;
this.m_WorkBook = workBook;
}
//*************************************************************************
// Constructor: ImportFromMatrixWorkbookDialog()
//
/// <overloads>
/// Initializes a new instance of the <see
/// cref="ImportFromMatrixWorkbookDialog" /> class.
/// </overloads>
///
/// <summary>
/// Initializes a new instance of the <see
/// cref="ImportFromMatrixWorkbookDialog" /> class with a workbook.
/// </summary>
///
/// <param name="destinationNodeXLWorkbook">
/// Workbook to which the matrix workbook will be imported.
/// </param>
///
/// <param name="clearDestinationTablesFirst">
/// true if the NodeXL tables in <paramref
/// name="destinationNodeXLWorkbook" /> should be cleared first.
/// </param>
//*************************************************************************
public ImportFromMatrixWorkbookDialog(
Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook,
Boolean clearDestinationTablesFirst
)
: this()
{
// Instantiate an object that saves and retrieves the user settings for
// this dialog. Note that the object automatically saves the settings
// when the form closes.
m_oImportFromMatrixWorkbookDialogUserSettings =
new ImportFromMatrixWorkbookDialogUserSettings(this);
m_oDestinationNodeXLWorkbook = destinationNodeXLWorkbook;
m_bClearDestinationTablesFirst = clearDestinationTablesFirst;
lbxSourceWorkbook.PopulateWithOtherWorkbookNames(
m_oDestinationNodeXLWorkbook);
DoDataExchange(false);
AssertValid();
}
//*************************************************************************
// Constructor: CalculateGraphMetricsDialog()
//
/// <summary>
/// Initializes a new instance of the <see
/// cref="CalculateGraphMetricsDialog" /> class with a default list of
/// graph metric calculators.
/// </summary>
///
/// <param name="workbook">
/// Workbook containing the graph contents.
/// </param>
///
/// <param name="graphMetricUserSettings">
/// User settings for calculating graph metrics.
/// </param>
//*************************************************************************
public CalculateGraphMetricsDialog(
Microsoft.Office.Interop.Excel.Workbook workbook,
GraphMetricUserSettings graphMetricUserSettings
)
: this()
{
// Instantiate an object that saves and retrieves the position of this
// dialog. Note that the object automatically saves the settings when
// the form closes.
m_oCalculateGraphMetricsDialogUserSettings =
new CalculateGraphMetricsDialogUserSettings(this);
m_oWorkbook = workbook;
m_oGraphMetricCalculators = null;
m_oGraphMetricUserSettings = graphMetricUserSettings;
m_oGraphMetricCalculationManager = new GraphMetricCalculationManager();
m_oGraphMetricCalculationManager.GraphMetricCalculationProgressChanged
+= new ProgressChangedEventHandler(
GraphMetricCalculationManager_GraphMetricCalculationProgressChanged);
m_oGraphMetricCalculationManager.GraphMetricCalculationCompleted +=
new RunWorkerCompletedEventHandler(
GraphMetricCalculationManager_GraphMetricCalculationCompleted);
DoDataExchange(false);
// Assume that calculations will not succeed.
this.DialogResult = DialogResult.Cancel;
AssertValid();
}
public string ExportExcel(string SQL, string[] StrCloumns, string saveFileName)
{
try
{
DataTable Table0 = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(SQL, TempConn.GetConnStr());
sda.Fill(Table0);
DataSet ds = new DataSet();
ds.Tables.Add(Table0);
ChangeColumnName(ref ds, StrCloumns);
if (ds == null)
{
return("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return("无法创建Excel对象，可能您的机子未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入字段
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return("");
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
MessageBox.Show("导出成功，默认保存在：我的电脑/文档");
return("成功保存到Excel");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
public static void ReadDoc()
{
var time = DateTime.Now;
try
{
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Open("DragonLegendSalesReport-AutoGen", Type.Missing, true, Type.Missing, "19931993");
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
var temp = "blah";
const string format = "ddd MMM d, yyyy";
var i = 4;
var strArray = new string[13];
var hasTodayInfo = true;
while (temp != time.ToString(format))
{
var range = Worksheet.Range["B" + i, "M" + i];
var myvalues = (Array) range.Cells.Value;
strArray = myvalues.OfType<object>().Select(x => x.ToString()).ToArray();
if (strArray.Length == 0)
{
//Excel doesn't have today's info yet
hasTodayInfo = false;
break;
}
temp = strArray[0];
i++;
}
if (hasTodayInfo)
{
ReceptionScreen.TotalTicketSold[0] += Convert.ToInt32(strArray[1]);
ReceptionScreen.TotalTicketSold[1] += Convert.ToInt32(strArray[3]);
ReceptionScreen.TotalTicketSold[2] += Convert.ToInt32(strArray[5]);
ReceptionScreen.TotalTicketSold[3] += Convert.ToInt32(strArray[7]);
ReceptionScreen.TotalCutomers[0] += Convert.ToInt32(strArray[2]);
ReceptionScreen.TotalCutomers[1] += Convert.ToInt32(strArray[4]);
ReceptionScreen.TotalCutomers[2] += Convert.ToInt32(strArray[6]);
ReceptionScreen.TotalCutomers[3] += Convert.ToInt32(strArray[8]);
if (Convert.ToInt32(strArray[9]) != ReceptionScreen.TotalTicketSold[0] +
ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] +
ReceptionScreen.TotalTicketSold[3])
{
throw new InvalidDataException();
}
if (Convert.ToInt32(strArray[10]) != ReceptionScreen.TotalCutomers[0] +
ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] +
ReceptionScreen.TotalCutomers[3])
{
throw new InvalidDataException();
}
App.Quit();
}
else
{
App.Quit();
WriteDoc();
}
}
catch (Exception)
{
App.Quit();
Console.Write(@"File doesn't exist");
SheetAlreadyExist = false;
CreateDoc();
}
}
public static void WriteDoc()
{
var activeWriteRowNum = 4;
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Open("DragonLegendSalesReport-AutoGen", Type.Missing, false, Type.Missing, "19931993", Type.Missing,true);
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
var temp = "blah";
const string format = "ddd MMM d, yyyy";
var time = DateTime.Now;
var hasTodayInfo = true;
while (temp != time.ToString(format))
{
var range = Worksheet.Range["B" + activeWriteRowNum, "M" + activeWriteRowNum];
var myvalues = (Array) range.Cells.Value;
var strArray = myvalues.OfType<object>().Select(x => x.ToString()).ToArray();
if (strArray.Length == 0)
{
hasTodayInfo = false;
break;
}
temp = strArray[0];
activeWriteRowNum++;
}
if (hasTodayInfo)
activeWriteRowNum--;
//creates the main header
CreateHeaders(2, 2, "Sales Report", "B2", "M2", 2, "YELLOW", true, 10, "n");
//creates subheaders
CreateHeaders(3, 2, "Date", "B3", "C3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 4, "TIC:0|2", "D3", "D3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 5, "CUS:0|2", "E3", "E3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 6, "TIC:9|15", "F3", "F3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 7, "CUS:9|15", "G3", "G3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 8, "TIC:17|21", "H3", "H3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 9, "CUS:17|21", "I3", "I3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 10, "TIC:21|24", "J3", "J3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 11, "CUS:21|24", "K3", "K3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 12, "TIC TOTAL", "L3", "L3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 13, "CUS TOTAL", "M3", "M3", 0, "GAINSBORO", true, 10, "");
//add Data to cells
CreateHeaders(activeWriteRowNum, 2, "", "B" + activeWriteRowNum, "C" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 2, time.ToString(format), "B" + activeWriteRowNum, "C" + activeWriteRowNum, "");
CreateHeaders(activeWriteRowNum, 4, "", "D" + activeWriteRowNum, "D" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 4, ReceptionScreen.TotalTicketSold[0].ToString(), "D" + activeWriteRowNum,
"D" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 5, "", "E" + activeWriteRowNum, "E" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 5, ReceptionScreen.TotalCutomers[0].ToString(), "E" + activeWriteRowNum,
"E" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 6, "", "F" + activeWriteRowNum, "F" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 6, ReceptionScreen.TotalTicketSold[1].ToString(), "F" + activeWriteRowNum,
"F" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 7, "", "G" + activeWriteRowNum, "G" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 7, ReceptionScreen.TotalCutomers[1].ToString(), "G" + activeWriteRowNum,
"G" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 8, "", "H" + activeWriteRowNum, "H" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 8, ReceptionScreen.TotalTicketSold[2].ToString(), "H" + activeWriteRowNum,
"H" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 9, "", "I" + activeWriteRowNum, "I" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 9, ReceptionScreen.TotalCutomers[2].ToString(), "I" + activeWriteRowNum,
"I" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 10, "", "J" + activeWriteRowNum, "J" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 10, ReceptionScreen.TotalTicketSold[3].ToString(), "J" + activeWriteRowNum,
"J" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 11, "", "K" + activeWriteRowNum, "K" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 11, ReceptionScreen.TotalCutomers[3].ToString(), "K" + activeWriteRowNum,
"K" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 12, "", "L" + activeWriteRowNum, "L" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
var total = ReceptionScreen.TotalTicketSold[0] + ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] + ReceptionScreen.TotalTicketSold[3];
AddData(activeWriteRowNum, 12, total.ToString(), "L" + activeWriteRowNum, "L" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 13, "", "M" + activeWriteRowNum, "M" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
total = ReceptionScreen.TotalCutomers[0] + ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] + ReceptionScreen.TotalCutomers[3];
AddData(activeWriteRowNum, 13, total.ToString(), "M" + activeWriteRowNum, "M" + activeWriteRowNum, "#,##0");
App.DisplayAlerts = false;
Workbook.SaveAs("DragonLegendSalesReport-AutoGen", Type.Missing, "19931993", Type.Missing, true);
App.Quit();
}
public static void CreateDoc()
{
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Add(1);
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
//creates the main header
CreateHeaders(2, 2, "Sales Report", "B2", "M2", 2, "YELLOW", true, 10, "n");
//creates subheaders
CreateHeaders(3, 2, "Date", "B3", "C3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 4, "TIC:0|2", "D3", "D3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 5, "CUS:0|2", "E3", "E3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 6, "TIC:9|15", "F3", "F3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 7, "CUS:9|15", "G3", "G3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 8, "TIC:17|21", "H3", "H3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 9, "CUS:17|21", "I3", "I3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 10, "TIC:21|24", "J3", "J3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 11, "CUS:21|24", "K3", "K3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 12, "TIC TOTAL", "L3", "L3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 13, "CUS TOTAL", "M3", "M3", 0, "GAINSBORO", true, 10, "");
//add Data to cells
const string format = "ddd MMM d, yyyy";
var time = DateTime.Now;
CreateHeaders(4, 2, "", "B4", "C4", 2, "WHITE", false, 10, "n");
AddData(4, 2, time.ToString(format), "B4", "C4", "");
CreateHeaders(4, 4, "", "D4", "D4", 2, "WHITE", false, 10, "n");
AddData(4, 4, ReceptionScreen.TotalTicketSold[0].ToString(), "D4", "D4", "#,##0");
CreateHeaders(4, 5, "", "E4", "E4", 2, "WHITE", false, 10, "n");
AddData(4, 5, ReceptionScreen.TotalCutomers[0].ToString(), "E4", "E4", "#,##0");
CreateHeaders(4, 6, "", "F4", "F4", 2, "WHITE", false, 10, "n");
AddData(4, 6, ReceptionScreen.TotalTicketSold[1].ToString(), "F4", "F4", "#,##0");
CreateHeaders(4, 7, "", "G4", "G4", 2, "WHITE", false, 10, "n");
AddData(4, 7, ReceptionScreen.TotalCutomers[1].ToString(), "G4", "G4", "#,##0");
CreateHeaders(4, 8, "", "H4", "H4", 2, "WHITE", false, 10, "n");
AddData(4, 8, ReceptionScreen.TotalTicketSold[2].ToString(), "H4", "H4", "#,##0");
CreateHeaders(4, 9, "", "I4", "I4", 2, "WHITE", false, 10, "n");
AddData(4, 9, ReceptionScreen.TotalCutomers[2].ToString(), "I4", "I4", "#,##0");
CreateHeaders(4, 10, "", "J4", "J4", 2, "WHITE", false, 10, "n");
AddData(4, 10, ReceptionScreen.TotalTicketSold[3].ToString(), "J4", "J4", "#,##0");
CreateHeaders(4, 11, "", "K4", "K4", 2, "WHITE", false, 10, "n");
AddData(4, 11, ReceptionScreen.TotalCutomers[3].ToString(), "K4", "K4", "#,##0");
CreateHeaders(4, 12, "", "L4", "L4", 2, "WHITE", false, 10, "n");
var total = ReceptionScreen.TotalTicketSold[0] + ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] + ReceptionScreen.TotalTicketSold[3];
AddData(4, 12, total.ToString(), "L4", "L4", "#,##0");
CreateHeaders(4, 13, "", "M4", "M4", 2, "WHITE", false, 10, "n");
total = ReceptionScreen.TotalCutomers[0] + ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] + ReceptionScreen.TotalCutomers[3];
AddData(4, 13, total.ToString(), "M4", "M4", "#,##0");
App.DisplayAlerts = false;
Workbook.SaveAs("DragonLegendSalesReport-AutoGen",Type.Missing,"19931993",Type.Missing,true);
App.Quit();
}
/// <summary>
/// Writes data contained in ADO.NET DataTable object to path stored in DocumentFilePath property.
/// </summary>
/// <param name="dt">DataTable object containing data to be imported.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
/// <remarks>Ext version of WriteDataToDocument has extra logic for modifying existing documents.</remarks>
public bool WriteDataToDocumentExt(DataTable dt)
{
bool success = true;
try
{
if (File.Exists(this.DocumentFilePath))
{
if (_replaceExistingFile)
{
File.SetAttributes(this.DocumentFilePath, FileAttributes.Normal);
File.Delete(this.DocumentFilePath);
}
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Add(Type.Missing);
excelApp.Columns.ColumnWidth = 30;
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Worksheets["Sheet1"];
excelApp.DisplayAlerts = false;
ws.Delete();
if (File.Exists(this.DocumentFilePath))
{
wb = excelApp.Workbooks.Open(this.DocumentFilePath);
}
if (this.SheetName.Trim().Length == 0)
{
this.SheetName = "Sheet1";
}
if (this._replaceExistingSheet)
{
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Sheets)
{
if (sh.Name == this.SheetName)
{
sh.Delete();
break;
}
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Sheets)
{
if (sh.Name == this.SheetName)
{
sheet = sh;
break;
}
}
if (sheet == null)
{
sheet = wb.Sheets.Add(After: wb.Sheets[wb.Sheets.Count]);
sheet.Name = this.SheetName;
}
for (int c = 0; c < dt.Columns.Count; c++)
{
sheet.Cells[1, c + 1] = dt.Columns[c].ColumnName;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
DataRow row = dt.Rows[r];
sheet.Cells[r + 2, c + 1] = row[c].ToString();
}
}
Microsoft.Office.Interop.Excel.XlFileFormat fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV;
if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2007)
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
}
else if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2003)
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8;
}
else
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV; //default to CSV for not specified or invalid format request
}
excelApp.DisplayAlerts = false;
excelApp.ActiveWorkbook.SaveAs(this.DocumentFilePath, fileFormat);
wb.Close(false);
excelApp.Quit();
excelApp = null;
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append("Attempt to import DataTable into Excel document failed.");
_msg.Append(Environment.NewLine);
_msg.Append(PFTextProcessor.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
;
}
return(success);
}
//*************************************************************************
// Constructor: AutoFillUserSettings()
//
/// <summary>
/// Initializes a new instance of the AutoFillUserSettings class.
/// </summary>
///
/// <param name="workbook">
/// The workbook being autofilled.
/// </param>
//*************************************************************************
public AutoFillUserSettings(
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(workbook != null);
m_oWorkbook = workbook;
AssertValid();
}
/// <summary>
/// ExportDataToDataTable
/// </summary>
/// <param name="startRow">First row of Excel range.</param>
/// <param name="startCol">First column of Excel range.</param>
/// <param name="endRow">Last row of Excel range.</param>
/// <param name="endCol">Last column of Excel range.</param>
/// <param name="columnNamesInFirstRow">If true, then export will assume first row contains column names.</param>
/// <returns>DataTable object that contains the values in the given range of Excel cells.</returns>
/// <remarks>Row and col values are one based. e.g. 1,1 to 100,100 for a range of 100 rows and 100 columns.</remarks>
public DataTable ExportExcelDataToDataTable(int startRow, int startCol, int endRow, int endCol, bool columnNamesInFirstRow)
{
DataTable dt = new DataTable();
int rowNum = 0;
int colNum = 0;
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
if (this.SheetName.Trim().Length == 0)
{
this.SheetName = "Sheet1";
}
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
if (File.Exists(this.DocumentFilePath))
{
wb = excelApp.Workbooks.Open(this.DocumentFilePath);
}
else
{
_msg.Length = 0;
_msg.Append("Unable to load ");
_msg.Append(this.DocumentFilePath);
_msg.Append(". ExportExcelDataToDataTable has failed.");
throw new System.Exception(_msg.ToString());
}
Microsoft.Office.Interop.Excel.Worksheet ws = null;
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Sheets)
{
if (sh.Name == this.SheetName)
{
ws = sh;
}
}
if (ws == null)
{
_msg.Length = 0;
_msg.Append("Sheet ");
_msg.Append(this.SheetName);
_msg.Append(" does not exist. Will not be able to export data.");
throw new System.Exception(_msg.ToString());
}
if (endCol < startCol || endRow < startRow)
{
_msg.Length = 0;
_msg.Append("Invalid row and column coordinates specified.");
_msg.Append("FROM row " + startRow.ToString() + " column " + startCol.ToString() + " TO row " + endRow.ToString() + " column " + endCol.ToString() + ". ");
throw new System.Exception(_msg.ToString());
}
ws = wb.Sheets[this.SheetName];
if (columnNamesInFirstRow)
{
//set datatable column names to values in first row of spreadsheet
for (int colInx = startCol; colInx <= endCol; colInx++)
{
DataColumn dc = new DataColumn();
dc.ColumnName = ws.Cells[startRow, colInx].Text.ToString();
dc.DataType = Type.GetType("System.String");
dc.Caption = dc.ColumnName;
dc.DefaultValue = string.Empty;
dc.MaxLength = int.MaxValue;
dt.Columns.Add(dc);
}
}
else
{
//create arbitrary column names
colNum = 0;
for (int colInx = startCol; colInx < endCol; colInx++)
{
colNum++;
DataColumn dc = new DataColumn();
dc.ColumnName = "F" + colNum.ToString("0");
dc.DataType = Type.GetType("System.String");
dc.Caption = dc.ColumnName;
dc.DefaultValue = string.Empty;
dc.MaxLength = int.MaxValue;
dt.Columns.Add(dc);
}
}
if (columnNamesInFirstRow)
{
rowNum = startRow + 1;
}
else
{
rowNum = startRow;
}
for (int rowInx = rowNum; rowInx <= endRow; rowInx++)
{
DataRow dr = dt.NewRow();
colNum = 0;
for (int colInx = startCol; colInx <= endCol; colInx++)
{
dr[colNum] = ws.Cells[rowInx, colInx].Text.ToString();
colNum++;
}
dt.Rows.Add(dr);
}
wb.Close(false);
excelApp.Quit();
excelApp = null;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to output Excel data to DataTable failed.");
_msg.Append(Environment.NewLine);
_msg.Append(PFTextProcessor.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
}
return(dt);
}
void _ExcelApplication_WorkbookRowsetComplete(Microsoft.Office.Interop.Excel.Workbook Wb, string Description, string Sheet, bool Success)
{
DisplayInWatchWindow(WorkbookRowsetComplete++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
void _ExcelApplication_WorkbookBeforeClose(Microsoft.Office.Interop.Excel.Workbook Wb, ref bool Cancel)
{
DisplayInWatchWindow(WorkbookBeforeClose++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
void _ExcelApplication_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
{
DisplayInWatchWindow(WorkbookOpen++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
void _ExcelApplication_WorkbookAfterSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool Success)
{
DisplayInWatchWindow(WorkbookAfterSave++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
private void button1_Click(object sender, EventArgs e)
{
object missing = Type.Missing;
DataTable DT = new DataTable();
SqlDataAdapter DA = new SqlDataAdapter("prTumGemilerinSatislari", connection);
DA.SelectCommand.CommandType = CommandType.StoredProcedure;
//String raporAy = mtxtGirişTarihi.Text.Substring(0,2);
//String raporYil = mtxtGirişTarihi.Text.Substring(3);
//string[] aylar = new string[13] { "", "Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım", "Aralık" };
DA.SelectCommand.Parameters.AddWithValue("@pIlkTarih", dtpIlkTarih.Value);
DA.SelectCommand.Parameters.AddWithValue("@pSonTarih", dtpSonTarih.Value);
DataSet DS = new DataSet();
try
{
DA.Fill(DS, "Table");
}
catch (Exception hata)
{
DialogResult = DialogResult.Abort;
MessageBox.Show("Bağlantı Hatası " + hata.Message);
}
finally
{
connection.Close();
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(missing);
excel.Visible = false;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
//Header
worksheet.Cells[1, 1] = "SATILAN MİKTAR ";
worksheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[1, 1].Font.Size = 20;
worksheet.Cells[1, 1].Font.Bold = true;
worksheet.Cells[1, 1].RowHeight = 24.75;
worksheet.Range[worksheet.Cells[1, "B"], worksheet.Cells[1, "AF"]].ColumnWidth = 4;
worksheet.Range[worksheet.Cells[1, "A"], worksheet.Cells[1, "AG"]].Merge();
worksheet.Cells[2, 1] = dtpIlkTarih.Value.ToString("MMMM").ToUpper();
worksheet.Cells[2, 1].RowHeight = 60;
worksheet.Cells[2, 1].Font.Size = 14;
worksheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[2, 1].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
worksheet.Cells[2, 1].Font.Bold = true;
worksheet.Cells[2, 1].NumberFormat = "aaaa";
DateTime Tarih = dtpIlkTarih.Value;
int daysInMonth = DateTime.DaysInMonth(Tarih.Date.Year, Tarih.Date.Month);
/* Aşağıdaki döngü hatali. */
for (int i = 0; i < daysInMonth; i++)
{
worksheet.Cells[2, i + 2] = Tarih.AddDays(i).Date;
worksheet.Cells[2, i + 2].Font.Bold = true;
worksheet.Cells[2, i + 2].Orientation = 90;
}
worksheet.Cells[2, "AG"] = "G.TOPLAM";
worksheet.Cells[2, "AG"].Font.Bold = true;
worksheet.Cells[2, "AG"].Orientation = 90;
worksheet.Cells[2, "AG"].ColumnWidth = 6;
int satirno = 2;
int sutunno = 0;
foreach (DataRow satir in DS.Tables[0].Rows)
{
satirno++;
sutunno = 0;
foreach (DataColumn sutun in DS.Tables[0].Columns)
{
sutunno++;
worksheet.Cells[satirno, sutunno] = satir[sutun].ToString();
}
string formula = @"=SUM(B" + satirno + ":AF" + satirno + ")";
worksheet.Cells[satirno, "AG"] = formula;
}
//worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 4]].EntireColumn.ColumnWidth = 11;
/*******************************************************************************
* Adetler
* Son kalınana satir = satirno
* *****************************************************************************/
// Gidilen Gemi adetleri
satirno += 2;
sutunno = 0;
worksheet.Cells[satirno, 1] = "GİDİLEN GEMİ ADEDİ ";
worksheet.Cells[satirno, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[satirno, 1].Font.Size = 20;
worksheet.Cells[satirno, 1].Font.Bold = true;
worksheet.Cells[satirno, 1].RowHeight = 24.75;
worksheet.Range[worksheet.Cells[satirno, "B"], worksheet.Cells[satirno, "AF"]].ColumnWidth = 4;
worksheet.Range[worksheet.Cells[satirno, "A"], worksheet.Cells[satirno, "AG"]].Merge();
satirno++;
worksheet.Cells[satirno, 1] = dtpIlkTarih.Value.ToString("MMMM").ToUpper();
worksheet.Cells[satirno, 1].RowHeight = 60;
worksheet.Cells[satirno, 1].Font.Size = 14;
worksheet.Cells[satirno, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[satirno, 1].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
worksheet.Cells[satirno, 1].Font.Bold = true;
worksheet.Cells[satirno, 1].NumberFormat = "aaaa";
Tarih = dtpIlkTarih.Value;
daysInMonth = DateTime.DaysInMonth(Tarih.Date.Year, Tarih.Date.Month);
/* Aşağıdaki döngü hatali. */
for (int i = 0; i < daysInMonth; i++)
{
worksheet.Cells[satirno, i + 2] = Tarih.AddDays(i).Date;
worksheet.Cells[satirno, i + 2].Font.Bold = true;
worksheet.Cells[satirno, i + 2].Orientation = 90;
}
worksheet.Cells[satirno, "AG"] = "G.TOPLAM";
worksheet.Cells[satirno, "AG"].Font.Bold = true;
worksheet.Cells[satirno, "AG"].Orientation = 90;
worksheet.Cells[satirno, "AG"].ColumnWidth = 6;
foreach (DataRow satir in DS.Tables[1].Rows)
{
satirno++;
sutunno = 0;
foreach (DataColumn sutun in DS.Tables[1].Columns)
{
sutunno++;
worksheet.Cells[satirno, sutunno] = satir[sutun].ToString();
}
string formula = @"=SUM(B" + satirno + ":AF" + satirno + ")";
worksheet.Cells[satirno, "AG"] = formula;
}
excel.Visible = true;
}
void _ExcelApplication_WorkbookSync(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Core.MsoSyncEventType SyncEventType)
{
DisplayInWatchWindow(WorkbookSync++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
//*************************************************************************
// Constructor: CreateSubgraphImagesDialog()
//
/// <overloads>
/// Initializes a new instance of the <see
/// cref="CreateSubgraphImagesDialog" /> class.
/// </overloads>
///
/// <summary>
/// Initializes a new instance of the <see
/// cref="CreateSubgraphImagesDialog" /> class with a workbook.
/// </summary>
///
/// <param name="workbook">
/// Workbook containing the graph data.
/// </param>
///
/// <param name="selectedVertexNames">
/// Collection of zero or more vertex names corresponding to the selected
/// rows in the vertex worksheet. Can't be null.
/// </param>
///
/// <param name="mode">
/// Indicates the mode in which the dialog is being used.
/// </param>
//*************************************************************************
public CreateSubgraphImagesDialog(
Microsoft.Office.Interop.Excel.Workbook workbook,
ICollection<String> selectedVertexNames,
DialogMode mode
)
: this()
{
Debug.Assert(workbook != null);
Debug.Assert(selectedVertexNames != null);
m_oWorkbook = workbook;
m_oSelectedVertexNames = selectedVertexNames;
m_eMode = mode;
// Instantiate an object that saves and retrieves the user settings for
// this dialog. Note that the object automatically saves the settings
// when the form closes.
m_oCreateSubgraphImagesDialogUserSettings =
new CreateSubgraphImagesDialogUserSettings(this);
m_oSubgraphImageCreator = new SubgraphImageCreator();
m_oSubgraphImageCreator.ImageCreationProgressChanged +=
new ProgressChangedEventHandler(
SubgraphImageCreator_ImageCreationProgressChanged);
m_oSubgraphImageCreator.ImageCreationCompleted +=
new RunWorkerCompletedEventHandler(
SubgraphImageCreator_ImageCreationCompleted);
m_eState = DialogState.Idle;
DoDataExchange(false);
AssertValid();
}
void _ExcelApplication_WorkbookPivotTableCloseConnection(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.PivotTable Target)
{
DisplayInWatchWindow(WorkbookPivotTableCloseConnection++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
}
public ArrayList DoPrint()
{
//根据配置文件读取有关excel模版的信息
_excelName = IniReadValue("模版信息", _printType);
_excelWindowsName = "Microsoft Excel - " + _excelName + " [只读]";
_excelPath = Application.StartupPath + "\\" + _excelName;
//当条形码打印成功后增加到arraylist里返回到调用方设置已打印标志
ArrayList _arPrinted = new ArrayList();
try
{
if (_printData == null || _printData.Tables.Count == 0 || _printData.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无打印数据!", "提示");
CloseExcel();
return(_arPrinted);
}
//检验是否取到模版信息，否则退出
if (_excelName == "" || _excelWindowsName == "" || _excelPath == "")
{
MessageBox.Show("模版信息维护错误！", "提示");
CloseExcel();
return(_arPrinted);
}
//判断excel模版是否已经被打开，打开excel模版
IntPtr a = FindWindow(null, _excelWindowsName);
if (a.ToString() != "0")
{
MessageBox.Show("Excel模版已经被打开,可能是正在打印或人为打开。\n请确认没有执行打印程序,然后手动关闭该EXCEL文档。", "提示");
CloseExcel();
return(_arPrinted);
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;
excel.Top = 8000;
excel.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);
string fileName = _excelPath;
workbook = excel.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.ReadOnlyRecommended = true;
Int32 sheetNum1 = 0;
try
{
sheetNum1 = Convert.ToInt32(IniReadValue(_printType, "FIELDNUM"));
}
catch
{
MessageBox.Show("配置文件-" + _printType + "-FIELDNUM，维护错误");
CloseExcel();
return(_arPrinted);
}
Hashtable hs = new Hashtable();
Hashtable hs2 = new Hashtable();
for (Int32 s = 1; s < sheetNum1 + 1; s++)
{
string FieldValue = IniReadValue(_printType, "F" + s.ToString());
hs.Add("F" + s.ToString(), FieldValue);
string FieldCell = IniReadValue(_printType, "C" + s.ToString());
hs2.Add("C" + s.ToString(), FieldCell);
}
_iniTable.Add(_printType, hs);
_iniCell.Add(_printType, hs2);
//针对每一行数据生成数据表
Int32 num = _printData.Tables[0].Rows.Count;
sh2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType);
for (int i = 1; i < num + 1; i++)
{
if (i == 1)
{
sh3 = sh2;
}
else
{
sh3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType + " (" + Convert.ToString(i) + ")");
}
sh2.Copy(Type.Missing, sh3);
}
string Barcode = "";
for (int i = 0; i < num; i++)
{
//根据配置文件设置excel中的数据
DataRow r = _printData.Tables[0].Rows[i];
Barcode = r["条码号"].ToString();
string sheet = "";
sheet = _printType + " (" + Convert.ToString(i + 2) + ")";
try
{
GetWorkSheet(sheet);
}
catch { MessageBox.Show("找不到打印模版表" + sheet, "提示"); return(_arPrinted); }
Hashtable ht = (Hashtable)_iniTable[_printType];
Hashtable ht2 = (Hashtable)_iniCell[_printType];
foreach (DictionaryEntry de in ht)
{
try
{
SetCellRangeValue(ht2["C" + de.Key.ToString().Substring(1)].ToString(), r[de.Value.ToString()].ToString());
}
catch { }
}
SetCellRangeValue("A10", r["条码号"].ToString());
_arPrinted.Add(r["条码号"].ToString());
}
//调用VBA宏生成条形码
object robj = new object();
try
{
RunExcelMacro(_excelPath, "getTime3", new Object[] { "" }, out robj, true);
}
catch
{
MessageBox.Show("执行宏失败！", "提示");
CloseExcel();
return(_arPrinted);
}
Thread.Sleep(1000);
//打印输出
try
{
for (int i = 0; i < Convert.ToInt32(_printCopies); i++)
{
workbook.Worksheets.PrintOut(2, num + 1, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
catch
{
MessageBox.Show("连接打印机失败，请检查操作系统中默认打印机是否运行正常！", "提示");
_arPrinted.Clear();
CloseExcel();
return(_arPrinted);
}
CloseExcel();
return(_arPrinted);
}
catch
{
CloseExcel();
return(_arPrinted);
}
}
private void exportBtnClick(object sender, RoutedEventArgs e)
{
int month = Convert.ToInt32(cmbMonth.SelectedValue.ToString());
int year = Convert.ToInt32(cmbYear.SelectedValue.ToString());
DateTime date = datePicker.SelectedDate.Value;
DataTable dt = new DataTable();
if (cmbType.SelectedIndex == 0)
{
dt = receiptMngController.LoadData();
}
else if (cmbType.SelectedIndex == 1)
{
dt = receiptMngController.LoadDataOnDate(date);
}
else if (cmbType.SelectedIndex == 2)
{
dt = receiptMngController.LoadDataOnMonth(month, year);
}
else
{
dt = receiptMngController.LoadDataOnMonth(0, year);
}
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = null;
Microsoft.Office.Interop.Excel.Range excelCellrange = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Add();
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
excelSheet.Range["A1"].Offset[0, i].Value = dt.Columns[i].ColumnName;
}
double totalIncome = 0;
int totalNormalSeats = 0;
int totalVIPSeats = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
excelSheet.Range["A2"].Offset[i].Resize[1, dt.Columns.Count].Value = dt.Rows[i].ItemArray;
totalIncome += Convert.ToDouble(dt.Rows[i]["Tong tien"].ToString());
totalNormalSeats += Convert.ToInt32(dt.Rows[i]["SoVeThuong"].ToString());
totalVIPSeats += Convert.ToInt32(dt.Rows[i]["SoVeVIP"].ToString());
}
excelSheet.Range["I1"].Value = "Tổng thu nhập";
excelSheet.Range["I2"].Value = totalIncome;
excelSheet.Range["I4"].Value = "Số ghế thường:";
excelSheet.Range["I5"].Value = totalNormalSeats;
excelSheet.Range["I7"].Value = "Số ghế VIP:";
excelSheet.Range["I8"].Value = totalVIPSeats;
excelSheet.Range["K1"].Value = "Tổng số ghế đã bán";
excelSheet.Range["K2"].Value = totalNormalSeats + totalVIPSeats;
excel.Visible = true;
wb.Activate();
}
catch (COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
}
public void Print(object sender, EventArgs e)
{
if (((FrmMAIN)this.MdiParent).ActiveMdiChild == this)
{
if (dgv1.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
try
{
int i, j;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "SaveReleaseRequest";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add();
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int k = 1; k < dgv1.ColumnCount; k++)
{
xlWorkSheet.Cells[1, k] = dgv1.Columns[k].HeaderText.ToString();
}
for (i = 0; i < dgv1.RowCount; i++)
{
for (j = 0; j < dgv1.ColumnCount - 1; j++)
{
if (dgv1[j, i].Value != null)
{
xlWorkSheet.Cells[i + 2, j + 1] = dgv1[j, i].Value.ToString();
}
}
}
xlWorkBook.SaveAs(saveFileDialog1.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlWorkBook.Close(true);
xlApp.Quit();
MessageBox.Show("출력되었습니다.", "출력 완료", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception err)
{
MessageBox.Show("출력에 실패하였습니다.", "출력 실패", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Log.WriteError($"[[RECV {this.Name}]]:{err.Message}");
}
finally
{
if (xlApp != null)
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
}
}
}
private void ExportExcel(DataTable mycsvdt)
{
if (mycsvdt == null || mycsvdt.Rows.Count < 0)
{
return;
}
bool fileSaved = false;
SaveFileDialog sfdSaveFile = new SaveFileDialog();
//设置保存文件的格式
sfdSaveFile.DefaultExt = "xlsx";
//sfdSaveFile.DefaultExt = "xls";
sfdSaveFile.Filter = "Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls";
sfdSaveFile.FileName = string.Empty;
if (sfdSaveFile.ShowDialog() != DialogResult.OK)
{
return;
}
//电脑Excel程序
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel程序！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Workbook集合
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
//Workbook
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//WorkSheet
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段列标题
for (int i = 0; i < mycsvdt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = mycsvdt.Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < mycsvdt.Rows.Count; r++)
{
for (int i = 0; i < mycsvdt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = mycsvdt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
//对指定列进行格式输出
//Microsoft.Office.Interop.Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[this.table.Rows.Count + 1, 1]);
//rg.NumberFormat = "00000000";
try
{
workbook.Saved = true;
workbook.SaveCopyAs(sfdSaveFile.FileName);//保存复制到指定位置
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
finally
{
workbooks.Close();
xlApp.Quit();
GC.Collect();//强行销毁
}
if (fileSaved && System.IO.File.Exists(sfdSaveFile.FileName))
{
//System.IO.File.Open(sfdSaveFile.FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Read);
System.Diagnostics.Process.Start(sfdSaveFile.FileName); //打开EXCEL
}
}
//*************************************************************************
// Constructor: DynamicFilterDialog()
//
/// <overloads>
/// Initializes a new instance of the <see
/// cref="DynamicFilterDialog" /> class.
/// </overloads>
///
/// <summary>
/// Initializes a new instance of the <see
/// cref="DynamicFilterDialog" /> class with a workbook.
/// </summary>
///
/// <param name="workbook">
/// Workbook containing the graph contents.
/// </param>
//*************************************************************************
public DynamicFilterDialog(
Microsoft.Office.Interop.Excel.Workbook workbook
)
: this()
{
Debug.Assert(workbook != null);
// Instantiate an object that saves and retrieves the user settings for
// this dialog. Note that the object automatically saves the settings
// when the form closes.
m_oDynamicFilterDialogUserSettings =
new DynamicFilterDialogUserSettings(this);
m_oWorkbook = workbook;
m_oExcelCalculationRestorer =
new ExcelCalculationRestorer(workbook.Application);
m_oExcelCalculationRestorer.TimerIntervalMs =
CalculationRestorerTimerIntervalMs;
m_bHandleControlEvents = false;
m_oChangeEventDelayTimer = new Timer();
m_oChangeEventDelayTimer.Interval = ChangeEventTimerIntervalMs;
m_oChangeEventDelayTimer.Tick += new EventHandler(
this.m_oChangeEventDelayTimer_Tick);
m_oDynamicFilterSettings = null;
m_oEdgeDynamicFilterColumnData = null;
m_oVertexDynamicFilterColumnData = null;
nudFilteredAlpha.Minimum =
(Decimal)AlphaConverter.MinimumAlphaWorkbook;
nudFilteredAlpha.Maximum =
(Decimal)AlphaConverter.MaximumAlphaWorkbook;
nudFilteredAlpha.Value = (Decimal)
( new PerWorkbookSettings(m_oWorkbook) ).FilteredAlpha;
AssertValid();
}
void excel_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
{
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized;
}
/// <summary>
/// Método que exporta el DataGrid especificado a una hoja de Excel
/// Exporta las columnas correspondientes unicamente al numero de días de corte
/// </summary>
/// <param name="grd">DataGrid que se exportará</param>
/// <param name="Columnas">Nmero de columnas qe no se tomaran en cuenta para la exportación</param>
/// /// <returns>True si se realizó correctamente, false si no</returns>
public bool ExportarFlujo(DataGridView grd)
{
Microsoft.Office.Interop.Excel.Application aplicacion = null;;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo = null;;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo = null;;
try
{
bool okFile = false;
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
int ColumnIndex = 0;
for (int j = 1; j < grd.Columns.Count; j++)
{
if (grd.Columns[j].Visible != false)
{
ColumnIndex++;
hoja_trabajo.Cells[1, ColumnIndex] = grd.Columns[j].HeaderText;
}
}
string LetraColumna = string.Empty;
switch (ColumnIndex)
{
case 1:
LetraColumna = "A";
break;
case 2:
LetraColumna = "B";
break;
case 3:
LetraColumna = "C";
break;
case 4:
LetraColumna = "D";
break;
case 5:
LetraColumna = "E";
break;
case 6:
LetraColumna = "F";
break;
case 7:
LetraColumna = "G";
break;
case 8:
LetraColumna = "H";
break;
case 9:
LetraColumna = "I";
break;
case 10:
LetraColumna = "J";
break;
case 11:
LetraColumna = "K";
break;
case 12:
LetraColumna = "L";
break;
case 13:
LetraColumna = "M";
break;
case 14:
LetraColumna = "N";
break;
case 15:
LetraColumna = "O";
break;
case 16:
LetraColumna = "P";
break;
}
Microsoft.Office.Interop.Excel.Range rango = hoja_trabajo.get_Range("A1", LetraColumna + "1");
rango.Font.Bold = true;
rango.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rango.Interior.ColorIndex = 15;
//rango1.Interior.ColorIndex = 15;
// grd.Columns.Remove("TipoColumna");
int rowIndex = 1;
int columnIndex = 0;
foreach (DataGridViewRow row in grd.Rows)
{
if (row.Cells[0].Value.ToString() != "3")
{
columnIndex = 0;
//hoja_trabajo.Range.RowHeight = 100;
rowIndex++;
for (int j = 0; j < row.Cells.Count; j++)
{
if (grd.Columns[j].Visible != false)
{
columnIndex++;
if (row.Cells[j].Value.GetType() == typeof(decimal))
{
hoja_trabajo.Cells[rowIndex, columnIndex] = decimal.Round(Decimal.Parse(row.Cells[j].Value.ToString()), 2);
}
else
{
hoja_trabajo.Cells[rowIndex, columnIndex] = row.Cells[j].Value;
}
}
}
}
}
rango.RowHeight = 20;
hoja_trabajo.Columns.AutoFit();
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
okFile = true;
return(okFile);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Ocurrió un error inesperado: " + ex.Message, "HalcoNET", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
}
}
public DataTable GetDataTable()
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
string TextFilePath = openfile.FileName;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(TextFilePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
string strCellData = "";
double douCellData;
int rowCnt = 0;
int colCnt = 0;
DataTable dt = new DataTable();
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
double douColumn;
try
{
strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
}
catch (Exception)
{
douColumn = (excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strColumn = douColumn.ToString();
}
try
{
dt.Columns.Add(strColumn, typeof(string));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string strData = "";
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
try
{
strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += strCellData + "|";
}
catch (Exception)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += douCellData.ToString() + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
dt.Rows.Add(strData.Split('|'));
}
excelBook.Close(true, null, null);
excelApp.Quit();
return(dt);
}
return(null);
}
private void btnLoad_Copy_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofdOpenFileDialog = new OpenFileDialog();
ofdOpenFileDialog.Filter = "Excel Files (*.xlsx, *.xls)|*.xlsx;*.xls|All Files (*.*)|*.*";
ofdOpenFileDialog.Multiselect = false;
if (true == ofdOpenFileDialog.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (null != excel)
{
excel.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ofdOpenFileDialog.FileName);
if (null != workbook)
{
Microsoft.Office.Interop.Excel.Worksheet wsParameters = workbook.Sheets["Parameters"];
Microsoft.Office.Interop.Excel.Worksheet wsData = workbook.Sheets["Data"];
if (null != wsParameters && null != wsData)
{
Microsoft.Office.Interop.Excel.Range rangeWebService = wsParameters.get_Range("C3");
Microsoft.Office.Interop.Excel.Range rangeUsername = wsParameters.get_Range("C4");
Microsoft.Office.Interop.Excel.Range rangePassword = wsParameters.get_Range("C5");
Microsoft.Office.Interop.Excel.Range rangeMethod = wsParameters.get_Range("C7");
Microsoft.Office.Interop.Excel.Range rangeDataStartRow = wsParameters.get_Range("C11");
Microsoft.Office.Interop.Excel.Range rangeDataStartColumn = wsParameters.get_Range("C10");
string strStartRow = rangeDataStartRow.Value2.ToString();
string strStartColumn = rangeDataStartColumn.Value2.ToString();
int.TryParse(strStartRow, out iDataVerticalStartPosition);
int.TryParse(strStartColumn, out iDataHorizontalStartPosition);
// Microsoft.Office.Interop.Excel.Range rangeInputClassName = wsData.get_Range("D" + (iDataVerticalStartPosition - 3));
string strURI = rangeWebService.Value;
string strUsername = rangeUsername.Value;
string strPassword = rangePassword.Value;
string strMethod = rangeMethod.Value;
// string strInputClass = rangeInputClassName.Value;
List <Parameter> lsParameters = getParameterColumns(wsData, iDataHorizontalStartPosition, iDataVerticalStartPosition);
// generateSpreadsheet(strURI, strUsername, strPassword, strMethod, workbook);
compiledAssembly compiledAsm = generateCompiledAssembly(strURI, strUsername, strPassword, strMethod);
//if (null != (compilerResults = compiledAsm.compilerResults))
callWebService(generateCompiledAssembly(strURI, strUsername, strPassword, strMethod), strMethod, lsParameters, wsData, iDataVerticalStartPosition);
}
else
{
Console.WriteLine("Failed to retrieve Parameters sheet");
}
}
else
{
Console.WriteLine("Failed to open " + ofdOpenFileDialog.FileName);
}
excel.Quit();
}
}
}
private void generateSpreadsheet(string astrWebServiceURI, string astrUsername, string astrPassword, string astrMethod, Microsoft.Office.Interop.Excel.Workbook workbook)
{
CompilerResults compilerResult = null;
compiledAssembly compiledAsm = generateCompiledAssembly(astrWebServiceURI, astrUsername, astrPassword, astrMethod);
if (null != (compilerResult = compiledAsm.compilerResults))
{
if (compilerResult.Errors.Count <= 0)
{
var allTypes = compilerResult.CompiledAssembly.GetTypes();
List <Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod);
Type tpParametersToPopulate = lsTypeHierarchy.Last();
PropertyInfo[] piProperties = tpParametersToPopulate.GetProperties();
if (null != piProperties)
{
int i = iDataHorizontalStartPosition;
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets["Data"];
if (null != worksheet)
{
spreadsheetDataSetTemplate(worksheet, astrMethod);
generateSpreadsheetColumnsRecursive(piProperties, worksheet, i);
//foreach (PropertyInfo currentProperty in piProperties)
//{
// Microsoft.Office.Interop.Excel.Range rangeName = worksheet.get_Range(GetColumnLetter(i) + (iDataVerticalStartPosition - 2));
// Microsoft.Office.Interop.Excel.Range rangeType = worksheet.get_Range(GetColumnLetter(i) + (iDataVerticalStartPosition + 1 - 2));
// rangeName.Value2 = currentProperty.Name;
// rangeName.Font.Bold = true;
// rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString());
// rangeType.Font.Italic = true;
// i++;
//}
}
}
}
}
}
public void OpenReadOnly(string fileName)
{
fileName = TransformFilename(fileName);
_workbook = _application.Workbooks.Open(fileName, 0, true, TextFileFormat.Nothing, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, null, false, false, 0, true, 1, 0);
}
private void btnGenerate_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofdOpenFileDialog = new OpenFileDialog();
ofdOpenFileDialog.Filter = "Excel Files (*.xlsx, *.xls)|*.xlsx;*.xls|All Files (*.*)|*.*";
ofdOpenFileDialog.Multiselect = false;
if (true == ofdOpenFileDialog.ShowDialog())
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (null != excel)
{
excel.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ofdOpenFileDialog.FileName);
if (null != workbook)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets["Parameters"];
if (null != worksheet)
{
Microsoft.Office.Interop.Excel.Range rangeWebService = worksheet.get_Range("C3");
Microsoft.Office.Interop.Excel.Range rangeUsername = worksheet.get_Range("C4");
Microsoft.Office.Interop.Excel.Range rangePassword = worksheet.get_Range("C5");
Microsoft.Office.Interop.Excel.Range rangeMethod = worksheet.get_Range("C7");
Microsoft.Office.Interop.Excel.Range rangeDataStartRow = worksheet.get_Range("C11");
Microsoft.Office.Interop.Excel.Range rangeDataStartColumn = worksheet.get_Range("C10");
Microsoft.Office.Interop.Excel.Range rangeResultColumn = worksheet.get_Range("C16");
Microsoft.Office.Interop.Excel.Range rangeErrorCountCell = worksheet.get_Range("C17");
Microsoft.Office.Interop.Excel.Range rangeStartTimeCell = worksheet.get_Range("C18");
Microsoft.Office.Interop.Excel.Range rangeEndTimeCell = worksheet.get_Range("C19");
Microsoft.Office.Interop.Excel.Range rangeDurationCell = worksheet.get_Range("C20");
string strStartRow = rangeDataStartRow.Value2.ToString();
string strStartColumn = rangeDataStartColumn.Value2.ToString();
int.TryParse(strStartRow, out iDataVerticalStartPosition);
int.TryParse(strStartColumn, out iDataHorizontalStartPosition);
string strURI = rangeWebService.Value;
string strUsername = rangeUsername.Value;
string strPassword = rangePassword.Value;
string strMethod = rangeMethod.Value;
generateSpreadsheet(strURI, strUsername, strPassword, strMethod, workbook);
}
else
{
Console.WriteLine("Failed to retrieve Parameters sheet");
}
}
else
{
Console.WriteLine("Failed to open " + ofdOpenFileDialog.FileName);
}
excel.Quit();
}
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
pbar.Visible = true;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Loop over DataTables in DataSet.
DataSet ds = new DataSet();
DataTable dtGST = new DataTable();
foreach (DataGridViewColumn col in dgvGST.Columns)
{
dtGST.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvGST.Rows)
{
DataRow dRow = dtGST.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtGST.Rows.Add(dRow);
}
DataTable dtCate = new DataTable();
foreach (DataGridViewColumn col in dgvCategory.Columns)
{
dtCate.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvCategory.Rows)
{
DataRow dRow = dtCate.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtCate.Rows.Add(dRow);
}
ds.Tables.Add(dtGST);
ds.Tables.Add(dtCate);
DataTableCollection collection = ds.Tables;
for (int i = collection.Count; i > 0; i--)
{
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
pbar.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected bool printExcel(string excelPath, string printerName)
{
bool flag = true;
try
{
if (CheckExcelInstalled.isExcelInstalled())
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(excelPath);
//excel.Visible = true;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Worksheets["Sheet1"];
try
{
paintPicByExcel(ws);
//打印方向
if (_PageSetup.Orientation == 1)
{
ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
}
//边距
if (_PageSetup.TopMargin != 0)
{
ws.PageSetup.TopMargin = _PageSetup.TopMargin;
}
if (_PageSetup.BottomMargin != 0)
{
ws.PageSetup.BottomMargin = _PageSetup.BottomMargin;
}
if (_PageSetup.LeftMargin != 0)
{
ws.PageSetup.LeftMargin = _PageSetup.LeftMargin;
}
if (_PageSetup.RightMargin != 0)
{
ws.PageSetup.RightMargin = _PageSetup.RightMargin;
}
ws.PrintOut(1, 2, 1, false, printerName, false, false, missing);
workbook.Saved = true;
}
catch (Exception e)
{
flag = false;
}
finally
{
workbook.Close(missing, missing, missing);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
}
else
{
ET.Application et = new ET.Application();
ET._Workbook ewb = et.Workbooks.Add(excelPath);
//et.Visible = true;
ET._Worksheet ews = (ET._Worksheet)ewb.Worksheets["Sheet1"];
try
{
paintPicByEt(ews);
if (_PageSetup.Orientation == 1)
{
ews.PageSetup.Orientation = ET.XlPageOrientation.xlLandscape;
}
//边距
if (_PageSetup.TopMargin != 0)
{
ews.PageSetup.TopMargin = _PageSetup.TopMargin;
}
if (_PageSetup.BottomMargin != 0)
{
ews.PageSetup.BottomMargin = _PageSetup.BottomMargin;
}
if (_PageSetup.LeftMargin != 0)
{
ews.PageSetup.LeftMargin = _PageSetup.LeftMargin;
}
if (_PageSetup.RightMargin != 0)
{
ews.PageSetup.RightMargin = _PageSetup.RightMargin;
}
ews.PrintOut(1, 1, 1, false, printerName, false, false, missing, false, 1, 1, 0, 0, false, ET.ETPaperTray.etPrinterDefaultBin, false, ET.ETPaperOrder.etPrinterRepeat);
ewb.Saved = true;
}
catch (Exception e)
{
flag = false;
}
finally
{
ewb.Close(missing, missing, missing);
et.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ews);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ewb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(et);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
flag = false;
}
return(flag);
}
void export(string Serialnumber)
{
string teststandard = "", SNsql = "";
int sheetCount = 1;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
object missing = System.Reflection.Missing.Value;
string templetFile = Environment.CurrentDirectory + @"\ReportFolder\OQCreport.xlsx";
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, true, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
for (int i = 1; i < sheetCount; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);
if (sheet == null)
{
return;
}
string testlistsql = @"select * from OQC_TestListNew where serialnumber='" + Serialnumber + "'";
DataTable testdt = DbAccess.SelectBySql(testlistsql).Tables[0];
if (testdt == null || testdt.Rows.Count < 1)
{
return;
}
string customer = "", PN = "", model = "", productionphase = "", deliveryID = "", sendqty = "", sampleqty = "", NGQty = "", checkdate = "", item = "", ECN = "", worknosendqty = "", testtestresult = "", testremark = "", testman = "", QE = "";
string TestListsampleplan = "";
customer = testdt.Rows[0]["customer"].ToString();
PN = testdt.Rows[0]["hytcode"].ToString();
model = testdt.Rows[0]["model"].ToString();
productionphase = testdt.Rows[0]["productionphase"].ToString();
deliveryID = testdt.Rows[0]["deliveryID"].ToString();
sendqty = testdt.Rows[0]["sendqty"].ToString();
sampleqty = testdt.Rows[0]["sampleqty"].ToString();
NGQty = testdt.Rows[0]["NGQty"].ToString();
checkdate = testdt.Rows[0]["checkdate"].ToString();
item = testdt.Rows[0]["item"].ToString();
ECN = testdt.Rows[0]["ECNnumber"].ToString();
worknosendqty = testdt.Rows[0]["sendqty"].ToString();
testtestresult = testdt.Rows[0]["testresult"].ToString();
teststandard = testdt.Rows[0]["teststandard"].ToString();
testremark = testdt.Rows[0]["testremark"].ToString();
testman = testdt.Rows[0]["testman"].ToString();
QE = testdt.Rows[0]["QE"].ToString();
TestListsampleplan = testdt.Rows[0]["sampleplan"].ToString();
sheet.Cells.get_Range("C4").Value = customer;
sheet.Cells.get_Range("C5").Value = model;
sheet.Cells.get_Range("C6").Value = PN;
sheet.Cells.get_Range("C7").Value = ECN;
sheet.Cells.get_Range("C8").Value = Serialnumber;
sheet.Cells.get_Range("S3").Value = deliveryID;
sheet.Cells.get_Range("S4").Value = sendqty;
sheet.Cells.get_Range("S5").Value = sampleqty;
sheet.Cells.get_Range("S6").Value = NGQty;
sheet.Cells.get_Range("S7").Value = checkdate.Substring(0, 9);
sheet.Cells.get_Range("A43").Value = "出货明细：工单号数量为：" + worknosendqty;
sheet.Cells.get_Range("Q38").Value = "备注：" + testremark;
sheet.Cells.get_Range("C44").Value = testman;
sheet.Cells.get_Range("L44").Value = QE;
sheet.Cells.get_Range("R44").Value = DateTime.Now.ToString("yyyy-MM-dd");
if (productionphase.Contains("样机/试产"))
{
sheet.Cells.get_Range("I5").Value = "√样机/试产";
}
else if (productionphase.Contains("工程变更"))
{
sheet.Cells.get_Range("I6").Value = "√工程变更";
}
else if (productionphase.Contains("正常量产"))
{
sheet.Cells.get_Range("I7").Value = "√正常量产";
}
if (testtestresult.Contains("OK"))
{
sheet.Cells.get_Range("N43").Value = "√允收";
}
if (testtestresult.Contains("NG"))
{
sheet.Cells.get_Range("O43").Value = "√退货";
}
SNsql = @" select * from OQC_SampleNewList where items ='" + item + "'";
DataTable SNtestdt = DbAccess.SelectBySql(SNsql).Tables[0];
if (SNtestdt != null && SNtestdt.Rows.Count > 0)
{
string SNbaddescribe = "";
int SNbadcount = 1;
for (int n = 0; n < SNtestdt.Rows.Count; n++) //// SNtestdt.Rows[n]["SNnumber"].ToString() != ""
{
if (SNtestdt.Rows[n]["badnumber"].ToString() != "" && SNtestdt.Rows[n]["badclass"].ToString() != "" && SNtestdt.Rows[n]["baddescribe"].ToString() != "")
{
SNbaddescribe = SNbaddescribe + "[" + SNbadcount.ToString() + "]【" + SNtestdt.Rows[n]["SNnumber"].ToString() + "；" + SNtestdt.Rows[n]["badnumber"].ToString() + "；" + SNtestdt.Rows[n]["badclass"].ToString() + "；" + SNtestdt.Rows[n]["baddescribe"].ToString() + "】" + "\r\n";
SNbadcount = SNbadcount + 1;
}
}
sheet.Cells.get_Range("Q11").Value = SNbaddescribe;
}
else
{
sheet.Cells.get_Range("Q11").Value = "NA";
}
string testitem = "", checkmethod = "", checkMA = "否", checkMI = "否";
string sampleplan = "", MA = "", MI = "", IPCA610F = "", customerstandard = "", otherstandard = "";
double MAvalue, MIvalue;
int count = 0;
string sql = "";
sql = @" select * from OQCTestProgSet where customer ='" + customer + "' and PN='" + PN + "' order by testitem ,standardsequence ";
DataTable dt = DbAccess.SelectBySql(sql).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
count = dt.Rows.Count;
sampleplan = dt.Rows[0]["sampleplan"].ToString();
MA = dt.Rows[0]["MA"].ToString();
MI = dt.Rows[0]["MI"].ToString();
IPCA610F = dt.Rows[0]["IPCA610F"].ToString();
customerstandard = dt.Rows[0]["customerstandard"].ToString();
otherstandard = dt.Rows[0]["otherstandard"].ToString();
for (int n = 0, m = 11; n < dt.Rows.Count; n++, m++)
{
sheet.Cells[m, 1] = dt.Rows[n]["testitem"].ToString();
sheet.Cells[m, 3] = dt.Rows[n]["standardsequence"].ToString();
sheet.Cells[m, 4] = dt.Rows[n]["teststandard"].ToString();
sheet.Cells[m, 13] = dt.Rows[n]["checkmethod"].ToString();
string Testitem = "", Testnumber = "", Testitemresult = "";
string[] resultString = Regex.Split(teststandard, "；", RegexOptions.IgnoreCase);
foreach (string testresult in resultString)
{
string[] result = Regex.Split(testresult, "，", RegexOptions.IgnoreCase);
if (result.Length == 3)
{
Testitem = result[0].Trim();
Testnumber = result[1].Trim();
Testitemresult = result[2].Trim();
if (dt.Rows[n]["testitem"].ToString() == Testitem && dt.Rows[n]["standardsequence"].ToString() == Testnumber)
{
if (Testitemresult.Contains("OK"))
{
sheet.Cells[m, 15] = Testitemresult;
}
else
{
sheet.Cells[m, 16] = Testitemresult;
}
}
}
}
}
}
else
{
sql = @" select * from OQCTestProgSet where customer ='" + customer + "' and PN='' order by testitem ,standardsequence ";
DataTable dts = DbAccess.SelectBySql(sql).Tables[0];
if (dts != null && dts.Rows.Count > 0)
{
count = dts.Rows.Count;
sampleplan = dts.Rows[0]["sampleplan"].ToString();
MA = dts.Rows[0]["MA"].ToString();
MI = dts.Rows[0]["MI"].ToString();
IPCA610F = dts.Rows[0]["IPCA610F"].ToString();
customerstandard = dts.Rows[0]["customerstandard"].ToString();
otherstandard = dts.Rows[0]["otherstandard"].ToString();
for (int n = 0, m = 11; n < dts.Rows.Count; n++, m++)
{
sheet.Cells[m, 1] = dts.Rows[n]["testitem"].ToString();
sheet.Cells[m, 3] = dts.Rows[n]["standardsequence"].ToString();
sheet.Cells[m, 4] = dts.Rows[n]["teststandard"].ToString();
sheet.Cells[m, 13] = dts.Rows[n]["checkmethod"].ToString();
string Testitem = "", Testnumber = "", Testitemresult = "";
string[] resultString = Regex.Split(teststandard, "；", RegexOptions.IgnoreCase);
foreach (string testresult in resultString)
{
string[] result = Regex.Split(testresult, "，", RegexOptions.IgnoreCase);
if (result.Length == 3)
{
Testitem = result[0].Trim();
Testnumber = result[1].Trim();
Testitemresult = result[2].Trim();
if (dts.Rows[n]["testitem"].ToString() == Testitem && dts.Rows[n]["standardsequence"].ToString() == Testnumber)
{
if (Testitemresult.Contains("OK"))
{
sheet.Cells[m, 15] = Testitemresult;
}
else
{
sheet.Cells[m, 16] = Testitemresult;
}
}
}
}
}
}
else
{
sql = @" select * from OQCTestProgSet where customer ='' and PN='' order by testitem ,standardsequence ";
DataTable dss = DbAccess.SelectBySql(sql).Tables[0];
if (dss != null && dss.Rows.Count > 0)
{
count = dss.Rows.Count;
sampleplan = dss.Rows[0]["sampleplan"].ToString();
MA = dss.Rows[0]["MA"].ToString();
MI = dss.Rows[0]["MI"].ToString();
IPCA610F = dss.Rows[0]["IPCA610F"].ToString();
customerstandard = dss.Rows[0]["customerstandard"].ToString();
otherstandard = dss.Rows[0]["otherstandard"].ToString();
for (int n = 0, m = 11; n < dss.Rows.Count; n++, m++)
{
sheet.Cells[m, 1] = dss.Rows[n]["testitem"].ToString().Trim();
sheet.Cells[m, 3] = dss.Rows[n]["standardsequence"].ToString();
sheet.Cells[m, 4] = dss.Rows[n]["teststandard"].ToString().Trim();
sheet.Cells[m, 13] = dss.Rows[n]["checkmethod"].ToString();
string Testitem = "", Testnumber = "", Testitemresult = "";
string[] resultString = Regex.Split(teststandard, "；", RegexOptions.IgnoreCase);
foreach (string testresult in resultString)
{
string[] result = Regex.Split(testresult, "，", RegexOptions.IgnoreCase);
if (result.Length == 3)
{
Testitem = result[0].Trim();
Testnumber = result[1].Trim();
Testitemresult = result[2].Trim();
if (dss.Rows[n]["testitem"].ToString() == Testitem && dss.Rows[n]["standardsequence"].ToString() == Testnumber)
{
if (Testitemresult.Contains("OK"))
{
sheet.Cells[m, 15] = Testitemresult;
}
else
{
sheet.Cells[m, 16] = Testitemresult;
}
}
}
}
}
}
else
{
MessageBox.Show("没有维护检验项目", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
{
if (TestListsampleplan == "C=0")
{
sheet.Cells.get_Range("N3").Value = "√C=0";
}
else if (TestListsampleplan == "ISO2859-1")
{
sheet.Cells.get_Range("O3").Value = "√ISO2859-1一般II级";
}
else
{
sheet.Cells.get_Range("Q3").Value = "√全检";
}
if (MA == "AQL=0.65")
{
sheet.Cells.get_Range("N4").Value = "√0.65";
}
else if (MA == "AQL=0.40")
{
sheet.Cells.get_Range("O4").Value = "√0.4";
}
else if (MA == "AQL=0.01")
{
sheet.Cells.get_Range("P4").Value = "√0.01";
}
else
{
sheet.Cells.get_Range("Q4").Value = "√其他";
}
if (MI == "AQL=0.65")
{
sheet.Cells.get_Range("N5").Value = "√0.65";
}
else if (MI == "AQL=0.40")
{
sheet.Cells.get_Range("O5").Value = "√0.4";
}
else if (MI == "AQL=0.01")
{
sheet.Cells.get_Range("P5").Value = "√0.01";
}
else
{
sheet.Cells.get_Range("Q5").Value = "√其他";
}
if (IPCA610F == "I级")
{
sheet.Cells.get_Range("O6").Value = "√I级";
}
if (IPCA610F == "II级")
{
sheet.Cells.get_Range("P6").Value = "√II级";
}
if (IPCA610F == "III级")
{
sheet.Cells.get_Range("Q6").Value = "√III级";
}
if (customerstandard == "客户检验标准规范")
{
sheet.Cells.get_Range("N7").Value = "√客户检验标准规范";
}
if (otherstandard == "其他")
{
sheet.Cells.get_Range("N8").Value = "√其他";
}
}
}
//*************************************************************************
// Constructor: GraphMetricsDialog()
//
/// <overloads>
/// Initializes a new instance of the <see cref="GraphMetricsDialog" />
/// class.
/// </overloads>
///
/// <summary>
/// Initializes a new instance of the <see cref="GraphMetricsDialog" />
/// class with a GraphMetricUserSettings object.
/// </summary>
///
/// <param name="workbook">
/// Workbook containing the graph contents.
/// </param>
///
/// <param name="graphMetricUserSettings">
/// The object being edited.
/// </param>
///
/// <param name="mode">
/// Indicates the mode in which the dialog is being used.
/// </param>
//*************************************************************************
public GraphMetricsDialog(
Microsoft.Office.Interop.Excel.Workbook workbook,
GraphMetricUserSettings graphMetricUserSettings,
DialogMode mode
)
: this()
{
Debug.Assert(workbook != null);
Debug.Assert(graphMetricUserSettings != null);
m_oWorkbook = workbook;
m_oGraphMetricUserSettings = graphMetricUserSettings;
m_eMode = mode;
if (m_eMode == DialogMode.EditOnly)
{
this.Text += " Options";
btnOK.Text = "OK";
}
// Instantiate an object that saves and retrieves the position of this
// dialog. Note that the object automatically saves the settings when
// the form closes.
m_oGraphMetricsDialogUserSettings =
new GraphMetricsDialogUserSettings(this);
DoDataExchange(false);
AssertValid();
}
void exportF1(string Serialnumber)
{
string sql = "", item = "";
int sheetCount = 1;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
object missing = System.Reflection.Missing.Value;
string templetFile = Environment.CurrentDirectory + @"\ReportFolder\OQCF1report.xlsx";
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, true, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
for (int i = 1; i < sheetCount; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);
if (sheet == null)
{
return;
}
sql = @" select item, checkdate, model,sendqty,sampleqty from OQC_TestListNew where serialnumber='" + Serialnumber + "'";
DataTable dt = DbAccess.SelectBySql(sql).Tables[0];
if (dt == null || dt.Rows.Count < 0)
{
return;
}
sheet.Cells.get_Range("O3").Value = dt.Rows[0]["checkdate"].ToString();
sheet.Cells.get_Range("F62").Value = dt.Rows[0]["checkdate"].ToString();
sheet.Cells.get_Range("P62").Value = dt.Rows[0]["checkdate"].ToString();
sheet.Cells.get_Range("B5").Value = dt.Rows[0]["model"].ToString();
sheet.Cells.get_Range("O4").Value = dt.Rows[0]["sendqty"].ToString();
sheet.Cells.get_Range("O5").Value = dt.Rows[0]["sampleqty"].ToString();
item = dt.Rows[0]["item"].ToString();
sql = @" select SNnumber ,max(CartonNo) CartonNo, max(VersionNO) VersionNO from OQC_SampleNewList where items = '" + item + "' group by SNnumber order by SNnumber asc ";
// sql = @" select SNnumber , CartonNo ,VersionNO from OQC_SampleNewList where items = '20180211111146803' ";
DataTable itemdt = DbAccess.SelectBySql(sql).Tables[0];
if (itemdt == null && itemdt.Rows.Count < 0)
{
return;
}
sheet.Cells.get_Range("B3").Value = itemdt.Rows [0]["CartonNo"].ToString();
sheet.Cells.get_Range("B4").Value = "";
sheet.Cells.get_Range("F5").Value = itemdt.Rows[0]["VersionNO"].ToString();
int n = itemdt.Rows.Count / 50;
int a = (int)'A';
for (int i = 1, j = 1, m = 8; i <= itemdt.Rows.Count; i++)
{
//sheet.Cells[m, j] = i;
sheet.Cells.get_Range((char)a + m.ToString()).Value = i;
// sheet.Cells.get_Range((char)a+m.ToString()).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Yellow);
// sheet.Cells[m, j + 1] = itemdt.Rows[i-1]["SNnumber"].ToString().Trim();
sheet.Cells.get_Range((char)(a + 1) + m.ToString()).Value = itemdt.Rows[i - 1]["SNnumber"].ToString().Trim();
// sheet.Cells.get_Range((char)(a+1)+ m.ToString()).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Yellow);
// sheet.Cells[m, j + 2] = itemdt.Rows[i-1]["CartonNo"].ToString().Trim();
sheet.Cells.get_Range((char)(a + 2) + m.ToString()).Value = itemdt.Rows[i - 1]["CartonNo"].ToString().Trim();
// sheet.Cells.get_Range((char)(a + 2)+ m.ToString()).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Yellow);
//sheet.Cells[m, j + 3] = "ACC";
sheet.Cells.get_Range((char)(a + 3) + m.ToString()).Value = "ACC";
// sheet.Cells.get_Range((char)(a + 3)+ m.ToString()).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Yellow);
m++;
if (i % 50 == 0 && i != 0)
{
m = 8;
a = a + 4;
j = j + 4;
}
}
}
public static bool ConnectToExcelRegisterAndGetInformation(string dateToFind)
{
generalSumDictionary = new Dictionary<String, double>(); // код ордера, общая сумма
// Создаём приложение
ExcelApp = new Interop.Excel.Application();
ExcelApp.Visible = false;
// Открываем книгу
Register = ExcelApp.Workbooks.Open(Properties.Settings.Default.RegisterPath,
0, true, 5, "", "", false, Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, false, false, false);
// Выбираем таблицу(лист)
Interop.Excel.Worksheet RegisterSheet = (Interop.Excel.Worksheet)Register.Worksheets[Properties.Settings.Default.RegisterSheetName];
registerPart = new DataTable();
// Выполняем поиск диапазона нужных строк (по дате выписки) и заполняем данными registerPart
Interop.Excel.Range range = RegisterSheet.get_Range(excelCellStart, excelCellEnd + RegisterSheet.UsedRange.Rows.Count);
Interop.Excel.Range firstFind = null;
Interop.Excel.Range currentFind;
Interop.Excel.Range usedRange = RegisterSheet.UsedRange;
int usedColumnsCount = RegisterSheet.UsedRange.Columns.Count;
Interop.Excel.Range columnNamesRange = RegisterSheet.get_Range("A1", (char)('A' + usedColumnsCount -1) + "1");//"A" + usedColumnsCount);
// считываем имена столбцов из первой строки
bool found = false;
bool found_1 = false;
for (int i = 2; i <= usedColumnsCount; i++)
{
String columnName = String.Empty;
try
{
columnName = (columnNamesRange.Cells[1, i] as Interop.Excel.Range).Value2.ToString();
}
catch (NullReferenceException)
{
break;
}
if (!found)
{
if (columnName.Contains(filialNames.First()))
{
found = true;
filialRegisterIndent = i;
}
if(!found_1)
{
if(columnName.Contains(strGeneralSum))
{
found_1 = true;
}
}
}
registerPart.Columns.Add(new DataColumn(columnName));
}
currentFind = range.Find(dateToFind, Type.Missing, Interop.Excel.XlFindLookIn.xlValues, Interop.Excel.XlLookAt.xlPart,
Interop.Excel.XlSearchOrder.xlByRows, Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
while (currentFind != null)
{
// Сохраняем первый найденный
if (firstFind == null)
{
firstFind = currentFind;
}
// Если мы не сдвинулись в поиске, значит это конец поиска
else if (currentFind.get_Address(Interop.Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Interop.Excel.XlReferenceStyle.xlA1))
{
break;
}
// помещаем данные в registerPart
List<String> paramsList = new List<String>();
int row = currentFind.Row;
int columnsNeed = filialRegisterIndent + filialNames.Length - 1;
for (int i = 2; i <= columnsNeed; i++)
{
Object value = (usedRange.Cells[row, i] as Interop.Excel.Range).Value;
if (value == null)
paramsList.Add(String.Empty);
else
paramsList.Add(Convert.ToString(value, cultureInfo));
}
generalSumDictionary.Add(paramsList[0], Convert.ToDouble(paramsList[indexGeneralSum], cultureInfo));
registerPart.Rows.Add(paramsList.ToArray());
// ищем дальше, начиная с последней найденной ячейки
currentFind = range.FindNext(currentFind);
}
if (firstFind == null)
return false;
filialRegisterIndent -= 2;
return true;
}
/// <summary>
/// Writes data contained in ADO.NET DataTable object to path stored in DocumentFilePath property.
/// </summary>
/// <param name="dt">DataTable object containing data to be imported.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
/// <remarks>AppendDataToExistingDocument will report an error if workbook or sheet cannot be found.s.</remarks>
public bool AppendDataToExistingSheet(DataTable dt)
{
bool success = true;
int excelRow = 0;
try
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
if (File.Exists(this.DocumentFilePath))
{
wb = excelApp.Workbooks.Open(this.DocumentFilePath);
}
else
{
_msg.Length = 0;
_msg.Append("Unable to load ");
_msg.Append(this.DocumentFilePath);
_msg.Append(". AppendDataToExistSheet has failed.");
throw new System.Exception(_msg.ToString());
}
Microsoft.Office.Interop.Excel.Worksheet ws = null;
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Sheets)
{
if (sh.Name == this.SheetName)
{
ws = sh;
}
}
if (ws == null)
{
_msg.Length = 0;
_msg.Append("Unable to find sheet ");
_msg.Append(this.SheetName);
_msg.Append(". AppendDataToExistSheet has failed.");
throw new System.Exception(_msg.ToString());
}
//no headings output when appending to existing data
Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
excelRow = range.Rows.Count;
for (int r = 0; r < dt.Rows.Count; r++)
{
excelRow++;
for (int c = 0; c < dt.Columns.Count; c++)
{
DataRow row = dt.Rows[r];
excelApp.Cells[excelRow, c + 1] = row[c].ToString();
}
}
Microsoft.Office.Interop.Excel.XlFileFormat fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV;
if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2007)
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
}
else if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2003)
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8;
}
else
{
fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV; //default to CSV for not specified or invalid format request
}
excelApp.DisplayAlerts = false;
excelApp.ActiveWorkbook.SaveAs(this.DocumentFilePath, fileFormat);
wb.Close(false);
excelApp.Quit();
excelApp = null;
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append("Attempt to import DataTable into Excel document failed.");
_msg.Append(Environment.NewLine);
_msg.Append(PFTextProcessor.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
;
}
return(success);
}//end method