// Add a traffic entry. Flag entries that differ from the Hitachi Spec
private void AddTrafficEntry()
{
// Set the Traffic worksheet as active
wsTraffic.Activate();
// Set the time and elapsed time
excelApp.Cells[wsTrafficRow, 1] = pkt.When.ToString("yy/MM/dd HH:mm:ss.ffff");
elapsed = pkt.When - lastTraffic;
excelApp.Cells[wsTrafficRow, 2] = (elapsed.TotalMilliseconds / 1000f).ToString("0.000");
lastTraffic = pkt.When;
string[] s = pkt.Data.Split('\t');
for (int i = 0; i < s.Length; i++)
{
excelApp.Cells[wsTrafficRow, i + 3] = s[i];
switch (i)
{
case 0:
case 1:
if (s[i] == "False")
{
excelApp.Cells[wsTrafficRow, i + 3].Interior.Color = Color.LightYellow;
}
break;
}
}
wsTrafficRow++;
}
/// <summary>
/// 绘制动态曲线图
/// </summary>
/// <param name="p_dicDate_ChosenDatum"></param>
/// <param name="arrChosenTags"></param>
/// <param name="arrDataDisplacement"></param>
/// <returns></returns>
/// <remarks></remarks>
private Chart DrawDynamicChart(Dictionary <DateTime, object[]> p_dicDate_ChosenDatum,
string[] arrChosenTags,
object[,] arrDataDisplacement)
{
//dic_Date_ChosenDatum以每一天的日期来索引这一天的监测数据，监测数据只包含列表中选择了的监测点
Chart r_myChart = default(Chart);
F_shtDrawing.Activate();
//--------------------------------------------------------------- 在工作表“标高图”中添加图表
r_myChart = F_shtDrawing.Shapes.AddChart(XlChartType.xlLineMarkers).Chart;
//---------- 选定模板
string t_path = System.IO.Path.Combine(System.Convert.ToString(My.Settings.Default.Path_Template),
Constants.FolderOrFileName.File_Template.Chart_Horizontal_Dynamic);
// 如果监测曲线图所使用的"Chart模板"有问题，则在chart.ChartArea.Copy方法（或者是chartObject.Copy方法）中可能会出错。
r_myChart.ApplyChartTemplate(t_path);
//-------------------- 获取图表中的信息文本框
F_textbox_Info = r_myChart.Shapes[0].TextFrame2; //Chart中的Shapes集合的第一个元素的下标值为0
//textbox_Info.AutoSize = Microsoft.Office.Core.MsoAutoSize.msoAutoSizeShapeToFitText
//------------------------ 设置曲线的数据
DateTime Date_theFirstCurve = System.Convert.ToDateTime(p_dicDate_ChosenDatum.Keys(0));
SeriesCollection mySeriesCollection = r_myChart.SeriesCollection();
Series series = mySeriesCollection.Item(1);
series.Name = Date_theFirstCurve.ToString(); //系列名称
series.XValues = arrChosenTags; //X轴的数据
series.Values = p_dicDate_ChosenDatum.Item(Date_theFirstCurve); //Y轴的数据
//
this.F_TheFirstseriesTag = new clsDrawing_Mnt_RollingBase.SeriesTag(series, Date_theFirstCurve);
//------------------------ 设置X、Y轴的格式——监测点位编号
dynamic with_3 = r_myChart.Axes(XlAxisType.xlCategory);
with_3.AxisTitle.Text = GetAxisLabel(DrawingType.Monitor_Dynamic, this.F_MonitorType, XlAxisType.xlCategory);
//-设置Y轴的格式——测点位移
Axis axes = r_myChart.Axes(XlAxisType.xlValue);
//由数据的最小与最大值来划分表格区间
float imin = (float)(F_AppDrawing.WorksheetFunction.Min(arrDataDisplacement));
float imax = (float)(F_AppDrawing.WorksheetFunction.Max(arrDataDisplacement));
//主要与次要刻度单位，先确定刻度单位是为了后面将坐标轴的区间设置为主要刻度单位的倍数
float unit = float.Parse(Strings.Format((imax - imin) / ClsDrawing_Mnt_OtherDynamics.cstChartParts_Y, "0.0E+00")); //这里涉及到有效数字的处理的问题
axes.MajorUnit = unit;
axes.MinorUnitIsAuto = true;
//坐标轴上显示的总区间
axes.MinimumScale = F_AppDrawing.WorksheetFunction.Floor_Precise(imin, axes.MajorUnit);
axes.MaximumScale = F_AppDrawing.WorksheetFunction.Ceiling_Precise(imax, axes.MajorUnit);
//坐标轴标题
axes.AxisTitle.Text = GetAxisLabel(DrawingType.Monitor_Dynamic, this.F_MonitorType, XlAxisType.xlValue);
return(r_myChart);
}
private void EditInExcel_Click(object sender, RoutedEventArgs e)
{
//需要实现的功能：根据选择打开指定的Excel，目前的功能：
try
{
TreeNode node = LYTunnelTreeview.SelectedItem as TreeNode;
if (node != null & node.Level == 2)
{
string excelName = Runtime.dataPath + "\\ProjectData\\LYTunnel.xls"; //你的excel文件的位置
string sheetName = node.Context; //你的sheet的名字
object missing = Type.Missing;
Excel.Application excel = new Excel.Application();
Excel.Workbook book = excel.Workbooks.Open(excelName, missing,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing);
Excel.Worksheet sheet = book.Worksheets[sheetName];
sheet.Activate();
excel.Visible = true;
//string path = Runtime.dataPath + "\\ProjectData\\LYTunnel.xls";
//ProcessStartInfo psi = new ProcessStartInfo(path);
//Process.Start(psi);
Statelabel.Content = "请在弹出的Excel模板里录入新的数据";
}
}
catch (Exception ex)
{
}
}
//private void wrtBySht(List<string> workList)
private void wrtBySht(string shtName)
{
xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName);
xlWorkSht.Activate();
string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index
Excel.Range rng = xlWorkSht.get_Range(idx, idx);
DataTable dt;
if (shtName == "InstrumentClassData")
dt = Util.DbConn.SqlTsk.GetTable("procGetInstData");
else
dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName);
int j = 1;
int i = 0;
foreach (DataRow row in dt.Rows)
{
for (i = 0; i < dt.Columns.Count; i++)
{
rng[j + 1, i + 1].Value = row[i].ToString();
}
j++;
if (j > dt.Rows.Count)
{
break;
}
}
rng[j + 1, 1].Value = "end";
}
public void GoBackToDashboard()
{
ExcelInterop.Workbook workbook = null;
ExcelInterop.Worksheet dashBoard = null;
try
{
workbook = ETKExcel.ExcelApplication.Application.ActiveWorkbook;
dashBoard = ETKExcel.ExcelApplication.GetWorkSheetFromName(workbook, "Dashboard");
if (dashBoard != null)
{
dashBoard.Activate();
}
}
finally
{
if (dashBoard != null)
{
Marshal.ReleaseComObject(dashBoard);
dashBoard = null;
}
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
workbook = null;
}
}
}
public static void FreezePanes(Excel.Worksheet worksheet, int splitColumn, int splitRow)
{
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitColumn = splitColumn;
worksheet.Application.ActiveWindow.SplitRow = splitRow;
worksheet.Application.ActiveWindow.FreezePanes = true;
}
/// <summary>
/// Method designs table
/// </summary>
/// <param name="worksheet"></param>
/// <param name="worksheetPivot"></param>
/// <param name="worksheetPeeChart"></param>
public static void DesignMyTableExcel(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
// Now apply autofilter
Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
BordersMaker.DrawFullTableBorders(app, workbook, worksheet);
Range rng = worksheet.UsedRange;
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.get_Range("A1", "I1").Cells.Font.Size = 14;
rng.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range("A1", "I1").Cells.Font.Name = "Arial Narrow";
worksheet.get_Range("A1", "I1").Cells.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 71, 179));
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.Columns.AutoFit();
Excel.Range thisRange = worksheet.get_Range("H:H");
thisRange.NumberFormat = "0.00";
Marshal.FinalReleaseComObject(thisRange);
}
/// <summary>
/// Adds
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddListObject_Click(object sender, EventArgs e)
{
// This is Microsoft.Office.Interop.Excel.Worksheet (COM)
Excel.Worksheet ws = (Excel.Worksheet)cboWorksheets.SelectedItem;
ws.Activate();
// This is Microsoft.Office.Tools.Excel.Worksheet (VSTO wrapper)
Worksheet vstoWs = Worksheet.GetVstoObject(ws);
try
{
// Now we have the VSTO wrapper, add some VSTO objects to it...
// First a ListObject
ListObject lo = vstoWs.Controls.AddListObject(vstoWs.Range["A3", Type.Missing], "myTable");
// Try bind some data to the ListObject
lo.DataSource = GetDemoData();
lo.DataMember = "DemoTable";
// Now add a button.
Button btnVsto = vstoWs.Controls.AddButton(vstoWs.Range["A1", Type.Missing], "btnVSTO");
btnVsto.Text = "VSTO Button";
btnVsto.Width = 100;
btnVsto.Height = 23;
// Setup the button Click event handler.
btnVsto.Click += delegate(object s, EventArgs args)
{
MessageBox.Show("VSTO button clicked.", "GetVstoObject demo", MessageBoxButtons.OK, MessageBoxIcon.Information);
};
}
catch (RuntimeException rtEx)
{
MessageBox.Show(rtEx.ToString(), "GetVstoObject demo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Open the selected worksheet within the relevant excel file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void listBoxFiles_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBoxFiles.SelectedItems.Count == 1)
{
// Instantiate Excel application
Excel.Application xl2 = new Excel.Application();
xl2.Visible = true; // Excel visible to user
// Separate sheet name and file name for processing
string fullName = listBoxFiles.GetItemText(listBoxFiles.SelectedItem);
string[] stringSeparators = new string[] { " - " };
string[] fileSheet = fullName.Split(stringSeparators, StringSplitOptions.None);
string sheetName = fileSheet[0];
string fileName = fileSheet[1];
// Open an existing workbook
string workbookPath = currentDirectory + fileName;
Excel.Workbook wb2 = xl2.Workbooks.Open(workbookPath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
// Get sheet in workbook
Excel.Sheets excelSheets = wb2.Worksheets;
Excel.Worksheet wsh = (Excel.Worksheet)excelSheets[sheetName];
wsh.Activate(); // Activate the worksheet
listBoxFiles.SelectedItems.Clear(); // Clear selected item
}
}
public void OpenExcelWorksheet(string worksheetName, out Excel.Application excelApplication, out _Worksheet openSheet, string fileName)
{
Excel._Workbook openWorkbook;
excelApplication = new Excel.Application
{
Visible = false,
DisplayAlerts = false
};
try
{
openWorkbook = (Excel._Workbook)(excelApplication.Workbooks.Open(fileName, UpdateLinks: 2));
}
catch (Exception)
{
excelApplication.Quit();
throw new Exception("Bitte eine gültige Excel-Datei auswählen.");
}
Excel.Sheets excelSheets = openWorkbook.Worksheets;
if (worksheetName != string.Empty)
{
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(worksheetName);
excelWorksheet.Activate();
}
openSheet = (Excel._Worksheet)openWorkbook.ActiveSheet;
}
/// <summary>
/// 简单的 复制 粘贴
/// 从 源 Sheet 复制到 目标 Sheet.
/// </summary>
/// <param name="fromSheetName">源 Sheet 名称.</param>
/// <param name="toSheetName"> 目标 Sheet 名称. </param>
/// <param name="fromPlace"> 源 Sheet 的起始地址 </param>
/// <param name="toPlace"> 目标 Sheet 的起始地址</param>
public void SimpleCopy(string fromSheetName, string toSheetName,
string fromPlace,
string toPlace)
{
// 选择源工作表.
Excel.Worksheet fromSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName);
// 选择
fromSheet.Select();
// 复制. （从指定坐标开始， 到全部）
fromSheet.Range[fromPlace, GetEndAddress(fromSheet)].Copy();
// 选择目的工作表.
Excel.Worksheet toSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(toSheetName);
// 选择.
toSheet.Activate();
toSheet.Select();
// 粘贴数据.
toSheet.Range[toPlace].PasteSpecial(Excel.XlPasteType.xlPasteValues);
// 粘贴格式.
toSheet.Range[toPlace].PasteSpecial(Excel.XlPasteType.xlPasteFormats);
}
public PivotTableListingBuyer(string pvSheetName, int TopPadding, string TableName, ListingStatus Status, ReportType BuyerReportType)
{
this.PivotSheetName = pvSheetName;
this.PivotTableName = TableName;
this.PivotTableTopPaddingRows = TopPadding;
this.Status = Status; //Library.GetStatus(Status);
this.ReportType = BuyerReportType;
switch (Globals.Ribbons.Ribbon1.ReportDataSheet)
{
case "Spreadsheet":
colMLS = "ML #";
colLandValue = "Room28Dim1";
colBuildingValue = "Room28Dim2";
colTotalValue = "Room28Lev";
colChangePer = "Room28Type";
colPrice = "Price";
colTotalFloorArea = "FlArTotFin";
break;
case "Listings Table":
colMLS = "MLS";
colLandValue = "LandValue";
colBuildingValue = "ImproveValue";
colTotalValue = "BCAValue";
colChangePer = "Change%";
colPrice = "Price0";
colTotalFloorArea = "FlArTotFin";
break;
}
this.ListingSheet = Globals.ThisAddIn.Application.Worksheets[Globals.Ribbons.Ribbon1.ReportDataSheet];
this.ListingBook = Globals.ThisAddIn.Application.ActiveWorkbook;
this.ListingSheet.AutoFilterMode = false;
//TEST FILTERS IF NO RECORDS THEN PASS PIVOT TABLE FUNCTION
int iCol = ListingSheet.Range[ListingDataColNames.Status + "1"].Column;
string[] StatusArray = Library.StatusArray(Status);
ListingSheet.Range["A1"].AutoFilter(iCol, StatusArray, Excel.XlAutoFilterOperator.xlFilterValues);
int LastRow = Library.GetLastRow(ListingSheet);
if (LastRow > 1)
{
ListingDataRows = LastRow - 1;
if (!Library.SheetExist(PivotSheetName))
{
Excel.Worksheet NewSheet = Globals.ThisAddIn.Application.Worksheets.Add();
NewSheet.Name = PivotSheetName;
}
PivotSheet = Globals.ThisAddIn.Application.Worksheets[PivotSheetName];
PivotSheet.Activate();
int PivotTableFirstRow = Library.GetLastRow(PivotSheet) + PivotTableTopPaddingRows;
this.PivotTableLocation = "A" + PivotTableFirstRow;
}
else
{
ListingDataRows = 0;
}
}
public void Initialize()
{
mergeInstanceApplication = new Excel.Application(); // Create a new application for another excel to merge instance.
excelApplication = new Excel.Application();
if (!File.Exists(this._fileName))
{
excelWorkbook = excelApplication.Workbooks.Add();
}
if (File.Exists(this._fileName))
{
File.Copy(this._fileName, this._tmpFileName, true);
excelWorkbook = excelApplication.Application.Workbooks.Open(_tmpFileName, ReadOnly: false);
}
excelApplication.DisplayAlerts = false;
excelWorkbook = excelApplication.Workbooks[1];
excelWorkbook.Activate();
int worksheetNum = excelWorkbook.Worksheets.Count;
excelWorksheet = excelWorkbook.Worksheets[worksheetNum];
excelWorksheet.Activate();
}
public Excel.Worksheet InicializarExcelConTemplate(string nombreHoja)
{
try
{
_sheet1 = (Excel.Worksheet)Application.ActiveSheet;
_sheet1.Unprotect();
var found = Application.Sheets.Cast <Excel.Worksheet>().Any(sheet => sheet.Name == nombreHoja);
var awa = Application.Workbooks.Application;//nueva app
if (!found)
{
var ows = Application.Worksheets[1]; // excel actual
var sPath = Path.GetTempFileName(); // archivo temporal
File.WriteAllBytes(sPath, Properties.Resources.TABLASMERCASTOCK);
var oTemplate = Application.Workbooks.Add(sPath); //path del template temporal
var worksheet = oTemplate.Worksheets[nombreHoja] as Excel.Worksheet; //descripcion del template
worksheet?.Copy(After: ows);
oTemplate.Close(false, missing, missing);
File.Delete(sPath);
}
_reporte = awa.Worksheets[nombreHoja] as Excel.Worksheet;//descripcion de la hoja actual
_reporte?.Activate();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(_reporte);
}
/// <summary>
/// 选择工作表.
/// </summary>
/// <param name="sheetName"></param>
public void SelectSheet(String sheetName)
{
// 选择工作表.
xlSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName);
xlSheet.Activate();
}
// TODO: This function needs significant refactoring.
public static void WriteOutputBlockToExcel(
Excel.Application xlApp,
Excel.Workbook workbook,
string wsName,
string[] titles,
object[,] output,
int rowOffset
)
{
if (output.GetLength(0) > 0)
{
Excel.Worksheet ws = null;
if (SheetExists(workbook, wsName))
{
ws = xlApp.Sheets[wsName];
}
else
{
ws =
xlApp.Sheets.Add(After: xlApp.Worksheets[xlApp.Worksheets.Count]);
ws.Name = wsName;
}
Excel.Range c1;
Excel.Range c2;
Excel.Range range;
var titleOffset = 0;
if (titles.Length > 0)
{
titleOffset = 1;
Excel.Range baseCell = ws.Cells[1, 1];
c1 = baseCell.Offset[0, 0];
c2 = c1.Offset[0, titles.Length - 1];
range = ws.get_Range(c1, c2);
range.Value = titles;
range.Font.Bold = true;
}
c1 = ws.Cells[1 + titleOffset + rowOffset, 1];
c2 = ws.Cells[1 + titleOffset + rowOffset + output.GetLength(0) - 1, output.GetLength(1)];
range = ws.get_Range(c1, c2);
range.Value = output;
range.NumberFormat = "#,##0";
ws.Columns["C:C"].NumberFormat = "d-mmm-yy";
ws.Columns["M:M"].NumberFormat = "d-mmm-yy";
range.Columns.AutoFit();
foreach (Excel.Range column in range.Columns)
{
if (column.ColumnWidth == 255)
{
column.ColumnWidth = 30;
}
}
ws.Activate();
xlApp.ActiveWindow.DisplayGridlines = false;
}
}
public void Init()
{
try
{
//viewsOwnerSheet = ETKExcel.ExcelApplication.GetWorkSheetFromName(ETKExcel.ExcelApplication.Application.ActiveWorkbook, DestinationSheetName);
// Create the destination sheet
ExcelInterop.Workbook workbook = ETKExcel.ExcelApplication.Application.ActiveWorkbook;
ExcelInterop.Sheets sheets = workbook.Sheets;
ExcelInterop.Worksheet lastSheet = workbook.Sheets[sheets.Count];
ExcelInterop.Worksheet firstSheet = workbook.Sheets[1];
viewsOwnerSheet = workbook.Worksheets.Add(Type.Missing, lastSheet);
viewsOwnerSheet.Name = DestinationSheetName;
viewsOwnerSheet.Visible = ExcelInterop.XlSheetVisibility.xlSheetHidden;
firstSheet.Activate();
int i = Marshal.ReleaseComObject(firstSheet);
i = Marshal.ReleaseComObject(lastSheet);
i = Marshal.ReleaseComObject(sheets);
i = Marshal.ReleaseComObject(workbook);
// End create the destination sheet
// Create the 'GoBackToDashboard' view
GoBackView = ETKExcel.TemplateManager.AddView("Dashboard Templates", "GoBackToDashboard", DestinationSheetName, "A1");
GoBackView.SetDataSource(new GoBackToDashBoardManager());
GoBackView.Render();
// End create the 'GoBackToDashboard' view
}
catch (Exception ex)
{
throw new Exception(string.Format("Init Topics failed:{0}", ex.Message), ex);
}
}
/// <summary>
/// 方法名称： Open
/// 内容描述： 无
/// 实现流程： 打开/连接一个excel数据文档
/// 作 者： 林付国
/// 日 期： 2006-5-13 16:23:23
/// </summary>
/// <returns></returns>
public bool Open()
{
bool bolRetValue = false;
string strPath = "";
strPath = this.FilePath.ToString();
if (!System.IO.File.Exists(strPath))
{
this.KillExcelProcess();
throw new Exception("源文件不存在，请检查！");
}
this.FileExteCheck(strPath);
try
{
if (this.IsOpen == false)
{
wb = app.Workbooks.Open(strPath, 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);
ws = (excel.Worksheet)wb.Worksheets[1]; //.get_Item(1);
//ws = (excel.Worksheet)(wb.Sheets.Add(objOpt,objOpt,objOpt,objOpt)) ;
ws.Activate();
}
bolRetValue = true;
}
catch
{
this.KillExcelProcess();
throw new Exception("打开或连接Excel文档错误，请检查！");
}
return(bolRetValue);
}
public static void PageSetup(Excel.Worksheet ws, string verfahrensname)
{
ws.Activate();
Globals.ThisAddIn.Application.ActiveWindow.View = Excel.XlWindowView.xlPageLayoutView;
ws.PageSetup.CenterHeader = "&\"Arial\" &B &11 " + verfahrensname;
ws.PageSetup.LeftMargin = 23;
ws.PageSetup.RightMargin = 23;
ws.PageSetup.TopMargin = 71;
ws.PageSetup.BottomMargin = 57;
ws.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
ws.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
ws.PageSetup.FirstPageNumber = (int)Excel.Constants.xlAutomatic;
ws.PageSetup.Order = Excel.XlOrder.xlDownThenOver;
ws.PageSetup.Zoom = false;
ws.PageSetup.PrintErrors = Excel.XlPrintErrors.xlPrintErrorsDisplayed;
ws.PageSetup.ScaleWithDocHeaderFooter = true;
ws.PageSetup.AlignMarginsHeaderFooter = true;
ws.PageSetup.CenterFooter = "&\"Arial\" &B &11 Seite &P von &N";
ws.PageSetup.RightFooterPicture.Filename = @"\\SSTOR01\Inso\Vorlagen\Logo\KDLB Kirstein\KDLB_Kirstein_Logo_neu.jpg";
ws.PageSetup.RightFooter = "&G";
ws.PageSetup.PrintTitleRows = "$1:$1";
ws.PageSetup.CenterHorizontally = true;
ws.PageSetup.PrintComments = Excel.XlPrintLocation.xlPrintNoComments;
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = false;
}
private static void AddPivotTable(Excel.Workbook wb,
Excel.Worksheet ws,
Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"HalvaPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.HasAutoFormat = false;
pivotTable.PivotFields("Совпадение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Совпадение").Position = 1;
pivotTable.PivotFields("Адрес Торговой точки").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Адрес Торговой точки").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Дата обработки транзакции Банком"),
"Количество совпадений", Excel.XlConsolidationFunction.xlCount);
wsPivote.Activate();
wsPivote.Columns["A:H"].Select();
xlApp.Selection.ColumnWidth = 15;
xlApp.Selection.WrapText = true;
xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop;
wsPivote.Range["A1"].Select();
pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
}
public Rehong_BangLuongNgay(Excel.Application oExcel, NativeWindow xlMain, Hashtable hs)
{
try
{
this.oExcel = oExcel;
oSheet = (Excel.Worksheet)oExcel.Worksheets[1];
oSheet.Activate();
this.oTemplate = oExcel.Sheets["Template"] as Excel.Worksheet;
//param
strDateChoose = hs["strDateChoose"].ToString();
strWhere = hs["strWhere"].ToString();
//strStatus = hs["strStatus"].ToString();
strThangChon = Convert.ToDateTime(strDateChoose).ToString("yyyyMMdd");
string dateTemp = Convert.ToDateTime(strDateChoose).ToString("MM/yyyy");;
ngayChon = Convert.ToDateTime(dateTemp).ToString("yyyy-MM-dd");
ngayDauThangSauChon = Convert.ToDateTime(dateTemp).AddMonths(1).ToString("yyyy-MM-dd");
ngayCuoiThangChon = Convert.ToDateTime(dateTemp).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
BuildExcel();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"FrontOfficeClientsPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C4", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("ФИО сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО сотрудника").Position = 1;
pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность").Position = 2;
pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Кол-во клиентов"), "Кол-во клиентов ", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("ФИО сотрудника").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("ФИО сотрудника").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Дата").NumberFormat = "[$-ru-RU]Д МММ;@";
wb.ShowPivotTableFieldList = false;
pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
/// <summary>
/// open an excel file,then write the content to file
/// </summary>
/// <param name="FileName">file name</param>
/// <param name="findString">first cloumn</param>
/// <param name="replaceString">second cloumn</param>
public void WriteToExcel(string filename, string findString, string replaceString)
{
//open
string excelName;
excelName = _path;
object Nothing = System.Reflection.Missing.Value;
var app = new Excel.Application();
app.Visible = false;
Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];
mysheet.Activate();
//get activate sheet max row count
int maxrow = mysheet.UsedRange.Rows.Count + 1;
mysheet.Cells[maxrow, 1] = filename;
mysheet.Cells[maxrow, 2] = findString;
mysheet.Cells[maxrow, 3] = replaceString;
mybook.Save();
mybook.Close(false, Type.Missing, Type.Missing);
mybook = null;
//quit excel app
app.Quit();
}
public string OpenExcel(string FilePath, string SheetName = "")
{
string result = "";
try
{
excelApp.Visible = true;
excelApp.DisplayAlerts = false;
Wb = excelApp.Workbooks.Open(FilePath);
if (SheetName == "")
{
Ws = (Excel.Worksheet)Wb.ActiveSheet;
Ws.Activate();
}
else
{
Ws = (Excel.Worksheet)Wb.Worksheets[SheetName];
Ws.Activate();
}
result = "";
}
catch (Exception e)
{
result = "Exception caught - " + e.Message;
}
return(result);
}
/*
* Create a Excel file with 3 sheets.
* Saves the file in the directory and shows up the result to the user.
*/
private void CreateExcelFile()
{
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application ExcelApplication = default(Microsoft.Office.Interop.Excel.Application);
Microsoft.Office.Interop.Excel.Workbook workBook = default(Microsoft.Office.Interop.Excel.Workbook);
Microsoft.Office.Interop.Excel.Worksheet workSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
ExcelApplication = new Microsoft.Office.Interop.Excel.Application
{
Visible = true
};
//Sheets names by default
workBook = ExcelApplication.Workbooks.Add();
workBook.Worksheets[1].Name = "Report";
workBook.Worksheets[2].Name = "Pivot Table";
workBook.Worksheets[3].Name = "To check";
workSheet = workBook.Worksheets[1];
workSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
workSheet.Activate();
ExportToExcel(workSheet, DataGridView1);
Microsoft.Office.Interop.Excel.Range formatRange = workSheet.get_Range("A1", "I1");
formatRange.Font.Bold = true;
formatRange.Columns.ColumnWidth = 15;
workSheet.UsedRange.Columns.Select();
workBook.SaveAs(saveFileDialog.FileName, XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
XlSaveAsAccessMode.xlShared, false, false,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
}
}
private void sCopyRowTo(Microsoft.Office.Interop.Excel.Worksheet pInputSheet, Microsoft.Office.Interop.Excel.Worksheet pOutputSheet, int pRowIn, int pRowOut, int pErrorCell, DateTime pToDate, string pErrorMessage)
{
pErrorMessage = "";
pInputSheet.Activate();
pInputSheet.Rows[pRowIn].Copy();
pOutputSheet.Activate();
pOutputSheet.Rows[pRowOut].Select();
pOutputSheet.Paste();
if ((pErrorCell != 0))
{
pOutputSheet.Cells[pRowOut, pErrorCell].Interior.Color = Color.Red;
}
else
{
for (int i = 1; (i <= 40); i++)
{
string vDateString;
vDateString = (pToDate.Month.ToString() + ("/"
+ (pOutputSheet.Cells[pRowOut, i].Value + ("/" + pToDate.Year.ToString()))));
if (IsDate(vDateString))
{
pOutputSheet.Cells[(pRowOut + 1), i].Value = Convert.ToDateTime(vDateString).DayOfWeek.ToString();
}
}
}
if ((pErrorMessage != ""))
{
pOutputSheet.Cells[pRowOut, (pOutputSheet.UsedRange.Column
+ (pOutputSheet.UsedRange.Columns.Count - 1))].Value = pErrorMessage;
pOutputSheet.Cells[pRowOut, (pOutputSheet.UsedRange.Column
+ (pOutputSheet.UsedRange.Columns.Count - 1))].Interior.Color = Color.Red;
}
}
/// <summary>
/// 简单的 复制 粘贴
/// 从 源 Sheet 复制到 目标 Sheet.
/// </summary>
/// <param name="fromSheetName"></param>
/// <param name="toSheetName"></param>
public void SimpleCopy(string fromSheetName, string toSheetName, string startPlace = "A1")
{
// 选择源工作表.
Excel.Worksheet fromSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName);
// 选择
fromSheet.Select();
// 复制.
fromSheet.UsedRange.Copy();
// 选择目的工作表.
Excel.Worksheet toSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(toSheetName);
// 选择.
toSheet.Activate();
toSheet.Select();
// 注意:
// 这里是 先粘贴数据， 后粘贴格式
// 假如 先粘贴格式 后粘贴数据的话
// 如果 源Sheet 有 合并单元格的情况下
// 粘贴格式完毕以后， 再粘贴数据将发生错误。
// 粘贴数据.
toSheet.Range[startPlace].PasteSpecial(Excel.XlPasteType.xlPasteValues);
// 粘贴格式.
toSheet.Range[startPlace].PasteSpecial(Excel.XlPasteType.xlPasteFormats);
}
/// <summary>
/// webber
/// </summary>
/// <param name="sSheet"></param>
/// <returns></returns>
public bool OpenExcelFile(string sSheet)
{
if (excelApplication != null)
{
CloseExcelApplication();
}
if (!File.Exists(excelFileName))
{
throw new Exception(excelFileName + "File NOT Exist！");
}
try
{
excelApplication = new Excel.Application();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelFileName, 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));
////excelWorkBook.Unprotect("RTG");
excelWorkSheet = ((Excel.Worksheet)excelWorkBook.Worksheets[sSheet]);
excelWorkSheet.Unprotect("RTG");
excelWorkSheet.Activate();
excelApplication.Visible = excelVisible;
return(true);
}
catch (Exception e)
{
CloseExcelApplication();
MessageBox.Show("Error happens；\nDetail："
+ e.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
}
public void ExportToExcel(string filename, string path, string template, TextBox textBox1, DataGridView dataGridView1, SaveFileDialog saveFileDialog1)
{
string worksheetname = textBox1.Text;
sr = new StreamReader(path, Encoding.Default);
filename = sr.ReadLine();
sr.Close();
ExcelApp = new Excel.Application();
try
{
workbook = ExcelApp.Workbooks.Open(filename);
}
catch
{
ExcelApp.Quit();
GC.Collect();
_close.CloseProcess();
MessageBox.Show("Файл не найден :(");
return;
}
try
{
workbookTemplate = ExcelApp.Workbooks.Open(template);
}
catch
{
ExcelApp.Quit();
GC.Collect();
_close.CloseProcess();
MessageBox.Show("Файл шаблона не найден :(");
return;
}
ExcelApp.Visible = false;
worksheet = null;
int sheetscount = workbook.Sheets.Count;
if (ShExist(workbook, worksheetname) == false)
{//Создаем лист, если лист с таким именем отсутствует
try
{
ExcelApp.Visible = false;
worksheet = (Excel.Worksheet)workbookTemplate.Sheets[1];
worksheet.Name = worksheetname;
workbookTemplate.Save();
workbookTemplate.Worksheets[1].Copy(After: workbook.Worksheets[sheetscount]);
ExcelApp.Visible = true;
}
catch { }
//Выгрузка массива в лист
UploadInExcel(ExcelApp, dataGridView1, saveFileDialog1);
return;
}
ExcelApp.Visible = true;
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(worksheetname);
worksheet.Activate();
//Выгрузка массива в лист
UploadInExcel(ExcelApp, dataGridView1, saveFileDialog1);
}
//按模板生成报表
private void reportAccordTemplet(Excel.Worksheet templet, DataTable recordTb)
{
this.Application.ScreenUpdating = false;
//复制模板到新的工作簿
Excel.Workbook newWorkbook = this.Application.Workbooks.Add();
templet.Copy(newWorkbook.Worksheets[1]);
//newWorkbook.Application.Caption = templetComBox.Text;
Excel.Worksheet newWorkSheet = newWorkbook.Worksheets[templetComBox.Text];
//删除自动生成的SHEET1
newWorkbook.Worksheets["Sheet1"].Delete();
newWorkSheet.Activate();
for (int row = 0; row < recordTb.Rows.Count; row++)
{
int startRow = newWorkSheet.UsedRange.Rows.Count + 1;
//在基础数据表中查找对应站址编码的位置
int baseTableIndex = FindBaseTableIndex(recordTb.Rows[row]["站址编码"].ToString());
for (int col = 1; col <= templet.UsedRange.Columns.Count; col++)
{
string tempString = templet.Cells[1, col].value.ToString();
switch (tempString)
{
case "常量":
newWorkSheet.Cells[startRow, col] = templet.Cells[2, col];
break;
case "计算":
newWorkSheet.Cells[2, col].copy();
newWorkSheet.Cells[startRow, col].PasteSpecial();
break;
case "发电记录":
newWorkSheet.Cells[startRow, col] = recordTb.Rows[row][templet.Cells[2, col].value];
break;
case "基础信息":
newWorkSheet.Cells[startRow, col] = baseTable.Rows[baseTableIndex][templet.Cells[2, col].value.ToString()];
break;
default:
break;
}
}
}
//删除模板上面两行
newWorkSheet.Rows[1].Delete();
newWorkSheet.Rows[1].Delete();
//if (templetComBox.Text != "日报表模板")
this.Application.ScreenUpdating = true;
}
public void openCsv()
{
Object missing = Missing.Value;
excelDoc = excelApp.Workbooks.Open((string)filepath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
ws = (MSExcel.Worksheet)excelDoc.Sheets[1];
ws.Activate();
}
/// <summary>
/// 方法名称： Open
/// 内容描述： 无
/// 实现流程： 打开/连接一个excel数据文档
/// 作 者： 林付国
/// 日 期： 2006-5-13 16:23:23
/// </summary>
/// <returns></returns>
public bool Open()
{
bool bolRetValue = false;
string strPath = "";
strPath = this.FilePath.ToString();
if(!System.IO.File.Exists(strPath))
{
this.KillExcelProcess();
throw new Exception("源文件不存在，请检查！");
}
this.FileExteCheck(strPath);
try
{
if(this.IsOpen == false)
{
wb = app.Workbooks.Open(strPath,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);
ws = (excel.Worksheet)wb.Worksheets[1];//.get_Item(1);
//ws = (excel.Worksheet)(wb.Sheets.Add(objOpt,objOpt,objOpt,objOpt)) ;
ws.Activate();
}
bolRetValue = true;
}
catch
{
this.KillExcelProcess();
throw new Exception("打开或连接Excel文档错误，请检查！");
}
return bolRetValue ;
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
exportProgress.Minimum = 0;
exportProgress.Maximum = TestInfoList.Count ;
exportProgress.Value = 0;
double value = 0;
UpdateProgressBarDelegate updatePbDelegate = new UpdateProgressBarDelegate(exportProgress.SetValue);
try
{
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(templatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
mySheet1.Activate();
int rowCount = TestInfoList.Count + 2; ;//总行数
//写入参数信息
int paramCount = 0;//参数行数
if (IsExportParams)
{
paramCount = ParamList.Count * 4;
for (int i = 0; i < ParamList.Count; i++)
{
Model.Parameter p = ParamList[i];
Range r = mySheet1.get_Range(mySheet1.Cells[1, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[1, testInfoColumnCount + i * 4 + 4]);
r.Merge();
r.Value = p.ParamName;
r.Font.Bold = true;
r.HorizontalAlignment = XlHAlign.xlHAlignCenter;
r.VerticalAlignment = XlHAlign.xlHAlignCenter;
Range r1 = mySheet1.get_Range(mySheet1.Cells[2, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[2, testInfoColumnCount + i * 4 + 4]);
r1.Value2 = paramContents;
r1.Font.Bold = true;
r1.EntireColumn.AutoFit();
}
}
//写入测试信息
string[,] content = new string[rowCount, testInfoColumnCount + paramCount];
//double?[,] paramContent = new double?[rowCount, paramCount];
XDocument xdoc;
BLL.TB_Dict dictBLL = new BLL.TB_Dict();
for (int i = 0; i < TestInfoList.Count; i++)
{
int rowIndex = i;
Model.TestInfoModel model = TestInfoList[i];
content[rowIndex, 0] = string.Format("测试{0}", i + 1);//测试顺序
content[rowIndex, 1] = model.Ath_Name;//姓名
content[rowIndex, 2] = model.TestDate.ToString("yyyy-MM-dd HH:mm");//测试日期
content[rowIndex, 3] = model.DJoint;//测试关节
content[rowIndex, 4] = model.DJointSide;//测试侧
content[rowIndex, 5] = model.DPlane;//运动方式
content[rowIndex, 6] = model.DTestMode;//测试模式
content[rowIndex, 7] = model.MotionRange;//运动范围
content[rowIndex, 8] = model.Speed;//测试速度
content[rowIndex, 9] = model.Break;//休息时间
content[rowIndex, 10] = model.NOOfSets;//测试组数
content[rowIndex, 11] = model.NOOfRepetitions;//重复次数
content[rowIndex, 12] = model.DInsuredSide;//受伤测
content[rowIndex, 13] = model.DGravitycomp;//重力补偿
if (IsExportParams)
{
string xmlPath = Model.AppPath.XmlDataDirPath + model.DataFileName;
xdoc = XDocument.Load(xmlPath);
List<XElement> action1 = xdoc.Descendants("action1").Elements<XElement>().ToList<XElement>();
List<XElement> action2 = xdoc.Descendants("action2").Elements<XElement>().ToList<XElement>();
for (int j = 0; j < ParamList.Count; j++)
{
int paramOneColumnIndex = j * 4;
double p1;
if (double.TryParse(action1[ParamList[j].Index].Attribute("max").Value, out p1)) {
//paramContent[rowIndex, paramOneColumnIndex] = p1;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 1] = p1;
}
double p2;
if (double.TryParse(action1[ParamList[j].Index].Attribute("avg").Value, out p2))
{
//paramContent[rowIndex, paramOneColumnIndex + 1] = p2;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 2] = p2;
}
double p3;
if (double.TryParse(action2[ParamList[j].Index].Attribute("max").Value, out p3))
{
//paramContent[rowIndex, paramOneColumnIndex + 2] = p3;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 3] = p3;
}
double p4;
if (double.TryParse(action2[ParamList[j].Index].Attribute("avg").Value, out p4))
{
//paramContent[rowIndex, paramOneColumnIndex + 3] = p4;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 4] = p4;
}
}
}
//写进度条
value += 1;
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background,
new object[] { ProgressBar.ValueProperty, value });
}
//写入测试信息
Range range1 = mySheet1.get_Range(mySheet1.Cells[3, 1], mySheet1.Cells[rowCount, testInfoColumnCount]);
range1.Value2 = content;
//写入参数信息
//Range range2 = mySheet1.get_Range(mySheet1.Cells[3, testInfoColumnCount + 1], mySheet1.Cells[rowCount, testInfoColumnCount + paramCount]);
//range2.Value2 = paramContent;
//if (IsExportParams)
//{
// rowCount = TestInfoList.Count + (ParamList.Count + 1) * TestInfoList.Count + 1;//信息行数+信息行数×参数行数+第一行列头信息
// paramCount = ParamList.Count + 1;//参数行数加1行参数名
//}
//else {
// rowCount = TestInfoList.Count + 1;
//}
//string[,] content = new string[rowCount, 13];
//XDocument xdoc;
//Model.TB_Dict actionModel;
//BLL.TB_Dict dictBLL = new BLL.TB_Dict();
//for (int i = 0; i < TestInfoList.Count; i++) {
// int rowIndex = i + i * paramCount;
// Model.TestInfoModel model = TestInfoList[i];
// content[rowIndex, 0] = model.Ath_Name;//姓名
// content[rowIndex, 1] = model.TestDate.ToString("yyyy-MM-dd HH:mm");//测试日期
// content[rowIndex, 2] = model.DJoint;//测试关节
// content[rowIndex, 3] = model.DJointSide;//测试侧
// content[rowIndex, 4] = model.DPlane;//运动方式
// content[rowIndex, 5] = model.DTestMode;//测试模式
// content[rowIndex, 6] = model.MotionRange;//运动范围
// content[rowIndex, 7] = model.Speed;//测试速度
// content[rowIndex, 8] = model.Break;//休息时间
// content[rowIndex, 9] = model.NOOfSets;//测试组数
// content[rowIndex, 10] = model.NOOfRepetitions;//重复次数
// content[rowIndex, 11] = model.DInsuredSide;//受伤测
// content[rowIndex, 12] = model.DGravitycomp;//重力补偿
// if (IsExportParams) {
// //写入参数信息
// actionModel = dictBLL.GetModel(model.Joint, model.Plane, model.Test_Mode);
// content[rowIndex + 1, 0] = "所选测试顺序";
// content[rowIndex + 1, 1] = "参数";
// content[rowIndex + 1, 2] = actionModel.actionone + "(极值)";
// content[rowIndex + 1, 3] = actionModel.actionone + "(平均值)";
// content[rowIndex + 1, 4] = actionModel.actiontwo + "(极值)";
// content[rowIndex + 1, 5] = actionModel.actiontwo + "(平均值)";
// string xmlPath = Model.AppPath.XmlDataDirPath + model.DataFileName;
// xdoc = XDocument.Load(xmlPath);
// List<XElement> action1 = xdoc.Descendants("action1").Elements<XElement>().ToList<XElement>();
// List<XElement> action2 = xdoc.Descendants("action2").Elements<XElement>().ToList<XElement>();
// for (int j = 0; j < ParamList.Count; j++)
// {
// content[rowIndex + 1 + j + 1, 0] = "测试" + (i + 1);
// content[rowIndex + 1 + j + 1, 1] = ParamList[j].ParamName;
// content[rowIndex + 1 + j + 1, 2] = action1[ParamList[j].Index].Attribute("max").Value;
// content[rowIndex + 1 + j + 1, 3] = action1[ParamList[j].Index].Attribute("avg").Value;
// content[rowIndex + 1 + j + 1, 4] = action2[ParamList[j].Index].Attribute("max").Value;
// content[rowIndex + 1 + j + 1, 5] = action2[ParamList[j].Index].Attribute("avg").Value;
// }
// }
// //写进度条
// value += 1;
// Dispatcher.Invoke(updatePbDelegate,
// System.Windows.Threading.DispatcherPriority.Background,
// new object[] { ProgressBar.ValueProperty, value });
//}
//Range range = mySheet1.get_Range(mySheet1.Cells[2, 1], mySheet1.Cells[rowCount, 13]);
//range.Value2 = content;
mySheet1.SaveAs(choosePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
myWorkBooks.Close();
excelApp.Quit();
MessageBox.Show("导出成功！", "系统信息");
System.Windows.Window.GetWindow(this).Close();
}
catch (Exception ee) {
MessageBox.Show("导出出错！\r\n错误信息为：" + ee.Message, "系统错误");
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
if ( !timer1.Enabled )
btnStart_Click(sender, e);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlFunction = xlApp.WorksheetFunction;
xlWorkSheetData = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheetData.Name = "Pendulum";
xlWorkSheetData.Activate();
xlWorkSheetData.Cells[1, 1] = "Constants used, mass: " + tbm.Text + ", gravity: " + tbG.Text + ", Spring Constant: " + tbk.Text + ", Length: " + tbH.Text;
xlRng = xlWorkSheetData.get_Range("A1", "N1");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[2, 1] = "Initial Values used, Intial X: " + tbXi.Text + ", Initial Y: " + tbYi.Text + ", Initial X Velocity: " + vx0.Text + ", Initial Y Velocity: " + vy0.Text;
xlRng = xlWorkSheetData.get_Range("A2", "N2");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[lastRowExcel, 1] = "t"; // changes these to whatever you want
xlWorkSheetData.Cells[lastRowExcel, 2] = "X";
xlWorkSheetData.Cells[lastRowExcel, 3] = "Y";
xlWorkSheetData.Cells[lastRowExcel, 4] = "Vx";
xlWorkSheetData.Cells[lastRowExcel, 5] = "Vy";
lblTransfer.Visible = true;
for (int i = 0; i < excelData.Count; i++)
{
xlWorkSheetData.Cells[i + 4, 1] = (excelData[i].time / 1000.00).ToString();
xlWorkSheetData.Cells[i + 4, 2] = excelData[i].x.ToString();
xlWorkSheetData.Cells[i + 4, 3] = excelData[i].y.ToString();
xlWorkSheetData.Cells[i + 4, 4] = excelData[i].vx.ToString();
xlWorkSheetData.Cells[i + 4, 5] = excelData[i].vy.ToString();
}
lblTransfer.Visible = false;
try //essaye le sauvegarde
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//sauvegarde le classeur courant
xlWorkBook.SaveAs(saveFileDialog1.FileName,
Excel.XlFileFormat.xlWorkbookDefault, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue,
misValue, misValue, misValue);
xlWorkBook.Close();
}
}
catch //en cas d'erreur affiche le message
{
MessageBox.Show("Impossible de sauvegarder le fichier.", "Erreur de sauvegarde de fichier Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Export(List<List<XElement>> datalist,string savePath)
{
try
{
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(templatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
mySheet1.Activate();
int rowCount = 1 + 2; ;//总行数
//写入参数信息
int paramCount = 0;//参数行数
List<DSJL.Model.Parameter> paramList = DSJL.Model.Parameter.GetAllParams();
paramCount = paramList.Count * 4;
for (int i = 0; i < paramList.Count; i++)
{
Model.Parameter p = paramList[i];
Range r = mySheet1.get_Range(mySheet1.Cells[1, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[1, testInfoColumnCount + i * 4 + 4]);
r.Merge();
r.Value = p.ParamName;
r.Font.Bold = true;
r.HorizontalAlignment = XlHAlign.xlHAlignCenter;
r.VerticalAlignment = XlHAlign.xlHAlignCenter;
Range r1 = mySheet1.get_Range(mySheet1.Cells[2, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[2, testInfoColumnCount + i * 4 + 4]);
r1.Value2 = paramContents;
r1.Font.Bold = true;
r1.EntireColumn.AutoFit();
}
//写入测试信息
string[,] content = new string[rowCount, testInfoColumnCount + paramCount];
//double?[,] paramContent = new double?[rowCount, paramCount];
BLL.TB_Dict dictBLL = new BLL.TB_Dict();
int rowIndex = 0;
//content[rowIndex, 0] ="";//测试顺序
//content[rowIndex, 1] = model.Ath_Name;//姓名
//content[rowIndex, 2] = model.TestDate.ToString("yyyy-MM-dd HH:mm");//测试日期
//content[rowIndex, 3] = model.DJoint;//测试关节
//content[rowIndex, 4] = model.DJointSide;//测试侧
//content[rowIndex, 5] = model.DPlane;//运动方式
//content[rowIndex, 6] = model.DTestMode;//测试模式
//content[rowIndex, 7] = model.MotionRange;//运动范围
//content[rowIndex, 8] = model.Speed;//测试速度
//content[rowIndex, 9] = model.Break;//休息时间
//content[rowIndex, 10] = model.NOOfSets;//测试组数
//content[rowIndex, 11] = model.NOOfRepetitions;//重复次数
//content[rowIndex, 12] = model.DInsuredSide;//受伤测
//content[rowIndex, 13] = model.DGravitycomp;//重力补偿
List<XElement> action1 = datalist[0];
List<XElement> action2 = datalist[1];
for (int j = 0; j < paramList.Count; j++)
{
int paramOneColumnIndex = j * 4;
double p1;
if (double.TryParse(action1[paramList[j].Index].Attribute("max").Value, out p1))
{
//paramContent[rowIndex, paramOneColumnIndex] = p1;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 1] = p1;
}
double p2;
if (double.TryParse(action1[paramList[j].Index].Attribute("avg").Value, out p2))
{
//paramContent[rowIndex, paramOneColumnIndex + 1] = p2;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 2] = p2;
}
double p3;
if (double.TryParse(action2[paramList[j].Index].Attribute("max").Value, out p3))
{
//paramContent[rowIndex, paramOneColumnIndex + 2] = p3;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 3] = p3;
}
double p4;
if (double.TryParse(action2[paramList[j].Index].Attribute("avg").Value, out p4))
{
//paramContent[rowIndex, paramOneColumnIndex + 3] = p4;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 4] = p4;
}
}
//写入测试信息
Range range1 = mySheet1.get_Range(mySheet1.Cells[3, 1], mySheet1.Cells[rowCount, testInfoColumnCount]);
range1.Value2 = content;
mySheet1.SaveAs(savePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
myWorkBooks.Close();
excelApp.Quit();
}
catch (Exception ee)
{
throw ee;
}
finally {
DSJL.Model.Parameter.CheckDefault();
}
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
/// <summary>
/// 激活Sheet表
/// </summary>
public void ActiveSheet(EnumType.SheetIndex sheetindex)
{
if(((Excel.Sheets)(((Excel.ApplicationClass)(app)).Worksheets)).Count < (int) sheetindex)
{
this.Dispose();
throw new Exception("Sheet表不存在，请检查！");
}
try
{
ws = (excel.Worksheet)wb.Worksheets[sheetindex];
ws.Activate();
}
catch
{
this.Dispose();
throw new Exception("Sheet表激活错误，请检查！");
}
int irowcount = 0;
app.ActiveCell.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Select();
irowcount = app.ActiveCell.Row;
if((this.WriteType != EnumType.WriteType.ReWrite) && (irowcount > iMaxRow))
{
this.Dispose();
throw new Exception("当前Sheet表已达存储上限，不能写入，请检查！");
}
int iSheetCount = wb.Sheets.Count ;
for(int i = 1; i <= iSheetCount ; i++)
{
Excel.Worksheet wsT = (Excel.Worksheet) wb.Sheets[i];
if(((this.m_sheetName != "") && ((this.m_sheetName != null)) && (this.m_sheetName.Trim().ToString() == wsT.Name.Trim().ToString()))
&& (wsT.Name != ws.Name))
{
this.Dispose();
throw new Exception("当前文件存在同名Sheet表，请检查！");
}
}
}
/// <summary>
/// 选择工作表.
/// </summary>
/// <param name="sheetName"></param>
public void SelectSheet(String sheetName)
{
// 选择工作表.
xlSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName);
xlSheet.Activate();
}
