internal void GetHorizontalIDs(_Worksheet worksheet)
{
string currentColumn = idRowStart;
string cellName = currentColumn + idRow.ToString();
Range range = null;
byte id;
for (int i = 0; i < pointsCount; i++)
{
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
id = Convert.ToByte(range.Value2);
TeleportPoint point = leftPoints[id].Clone();
point.TableColumnPos = currentColumn;
point.TableRowPos = idRow;
if (!topPoints.ContainsKey(id))
{
topPoints.Add(id, point);
}
else
{
topPoints[id] = point;
}
currentColumn = GetNextColumnName(currentColumn);
cellName = currentColumn + idRow.ToString();
}
}
private void LoadToExcel(List<Week> weeks, _Worksheet worksheet)
{
int currentRow = 2;
foreach (var week in weeks)
{
foreach (var day in week.Days)
{
var currentColumn = 1;
worksheet.Cells[currentRow, currentColumn] = day.Date;
foreach (var lesson in day.ListOfLessons)
{
currentColumn = 1;
PutLessonData(lesson.Name, worksheet,currentRow, ref currentColumn);
PutLessonData(lesson.StartTime, worksheet, currentRow, ref currentColumn);
PutLessonData(lesson.Classroom, worksheet, currentRow, ref currentColumn);
PutLessonData(lesson.Teacher, worksheet, currentRow, ref currentColumn);
currentRow++;
}
}
}
}
public void InformeGeneralObras()
{
oExcel = new Application();
oBooks = oExcel.Workbooks;
oBook = oBooks.Add(1);
oSheets = (Sheets)oBook.Worksheets;
oSheet = oSheets.get_Item(1);
this.oSheet.Cells[1,1] = "Consecutivo";
this.oSheet.Cells[1,2] = "Título";
this.oSheet.Cells[1,3] = "Núm. de Material";
this.oSheet.Cells[1,4] = "Año";
this.oSheet.Cells[1,5] = "Tiraje";
int ind = 2;
for (int j = 0; j < obrasImprimir.Count; j++)
{
oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo;
oSheet.Cells[2][ind] = obrasImprimir[j].Titulo;
oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial;
oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion;
oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje;
ind++;
}
this.oExcel.ActiveWorkbook.Save();
this.oExcel.Quit();
}
/// <summary>
/// Activates a workbook via its name.
/// </summary>
/// <param name="name">The name of the workbook</param>
/// <returns>true if the workbook was found, false otherways</returns>
public bool activateWorkbook(string name)
{
foreach (_Workbook workbook in excel.Workbooks)
{
if (workbook.Name == name)
{
workbook.Activate();
this.workbook = workbook;
this.worksheet = (_Worksheet) workbook.ActiveSheet;
return true;
}
}
return false;
}
//Método para cargar un archivo de Excel
static bool excel_init(String ruta)
{
appExcel = new ApplicationClass();
if (System.IO.File.Exists(ruta)){
workBookExl = appExcel.Workbooks.Open(ruta,0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
wSheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
return true;
}else{
Console.WriteLine("El documento " + ruta + " No puede abrirse");
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
return false;
}
}
private static void CloseExcel(_Worksheet worksheet, Application application, Workbook workbook, string path)
{
var range = worksheet.Range["A1", "F1"];
range.EntireColumn.AutoFit();
range.Font.Bold = true;
application.Visible = false;
application.UserControl = false;
workbook.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close();
}
//Método para cerrar una conexión en excel
static void excel_close()
{
if (appExcel != null){
try{
workBookExl.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
wSheet = null;
}catch (Exception ex){
appExcel = null;
Console.WriteLine("Ocurrieron problemas al intentar liberar los recursos: " + ex.ToString());
}finally{
GC.Collect();
}
}
}
private string InitExcel(out Application application, out Workbook workbook, out _Worksheet worksheet)
{
String path = GetFilePath();
File.Delete(path);
application = new Application { Visible = true };
workbook = application.Workbooks.Add();
worksheet = (_Worksheet)workbook.ActiveSheet;
worksheet.Cells[1, 1] = "День Недели";
worksheet.Cells[1, 2] = "Предмет";
worksheet.Cells[1, 3] = "Время";
worksheet.Cells[1, 4] = "Аудитория";
worksheet.Cells[1, 5] = "Преподователь";
return path;
}
private void OpenExcel (out Application application, out Workbook workbook, out _Worksheet worksheet)
{
var path = GetFilePath();
File.Delete(path);
application = new Application { Visible = true };
workbook = application.Workbooks.Add();
worksheet = (_Worksheet)workbook.ActiveSheet;
worksheet.Cells[1, 1] = "День Недели";
worksheet.Cells[1, 2] = "Предмет";
worksheet.Cells[1, 3] = "Время";
worksheet.Cells[1, 4] = "Аудитория";
worksheet.Cells[1, 5] = "Преподователь";
var range = worksheet.Range["A1", "F1"];
range.EntireColumn.AutoFit();
range.Font.Bold = true;
}
//Method to close excel connection
public void excel_close()
{
if (appExcel != null)
{
try
{
newWorkbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
objsheet = null;
}
catch (Exception ex)
{
appExcel = null;
}
finally
{
GC.Collect();
}
}
}
//Method to close excel connection
static void excel_close()
{
if (appExcel != null)
{
try
{
newWorkbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
objsheet = null;
}
catch (Exception ex)
{
appExcel = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
private void WriteData(
_Worksheet excelWorksheet,
IEnumerable<Player> players,
IDictionary<Player, double> playerAveragesDictionary,
string teamName,
double teamAverage,
double teamHandicap,
ref int rowIndex)
{
excelWorksheet.Cells[rowIndex, 1] = teamName;
rowIndex += 2;
excelWorksheet.Cells[rowIndex, 1] = "Name";
excelWorksheet.Cells[rowIndex, 2] = "Average";
excelWorksheet.Cells[rowIndex, 3] = "Game 1";
excelWorksheet.Cells[rowIndex, 4] = "Game 2";
excelWorksheet.Cells[rowIndex, 5] = "Game 3";
excelWorksheet.Cells[rowIndex, 6] = "Total";
rowIndex += 2;
foreach (Player player in players)
{
excelWorksheet.Cells[rowIndex, 1] = player.Name;
excelWorksheet.Cells[rowIndex, 2] = playerAveragesDictionary[player];
rowIndex++;
}
excelWorksheet.Cells[rowIndex, 1] = "Team";
excelWorksheet.Cells[rowIndex, 2] = teamAverage;
rowIndex += 2;
excelWorksheet.Cells[rowIndex, 1] = "Handicap";
excelWorksheet.Cells[rowIndex, 2] = teamHandicap;
rowIndex += 2;
excelWorksheet.Cells[rowIndex, 1] = "Total";
excelWorksheet.Cells[rowIndex, 2] = teamHandicap + teamAverage;
rowIndex += 2;
}
private void button1_Click(object sender, EventArgs e)
{
if (myArray == null)
{
MessageBox.Show("请先读取数据");
return;
}
//開啟一個新的應用程式
myExcel = new Excel.Application();
//加入新的活頁簿
myExcel.Workbooks.Add(true);
//停用警告訊息
myExcel.DisplayAlerts = true;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//加入新的工作表在第1張工作表之後
myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
//引用第一個工作表
mySheet = (Worksheet)myBook.Worksheets[1];
//命名工作表的名稱為 "Array"
mySheet.Name = "Array";
//設工作表焦點
mySheet.Activate();
int UpBound1 = myArray.GetUpperBound(0);//二維陣列數上限
int UpBound2 = myArray.GetUpperBound(1);//二維陣列數上限
//寫入報表名稱
myExcel.Cells[1, 4] = "全自动生成報表";
//設定範圍
myRange = (Range)mySheet.Range[mySheet.Cells[2, 1], mySheet.Cells[UpBound1 + 2, UpBound2 + 1]];
myRange.Select();
//用陣列一次寫入資料
myRange.Value2 = myArray;
//設定儲存路徑
string PathFile = Directory.GetCurrentDirectory() + @"\我的报表.xlsx";
//另存活頁簿
myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//關閉活頁簿
//myBook.Close(false, Type.Missing, Type.Missing);
////關閉Excel
//myExcel.Quit();
////釋放Excel資源
//System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
//Method to initialize opening Excel
static void excel_init(String path)
{
//string slash = @"\\";
//slash = Regex.Escape(slash);
//string singleslash = @"/";
//path = Regex.Replace(slash,path,singleslash);
// path = "@" + path;
// path = path.Replace("\\","\\");
appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (System.IO.File.Exists(path))
{
// then go and load this into excel
newWorkbook = appExcel.Workbooks.Open(path, true, true);
objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
}
else
{
MessageBox.Show("Unable to open file!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
System.Windows.Forms.Application.Exit();
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
//创建一个新的excel文档
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbooks wbks = excel.Workbooks;
//若打开已有excel，把“true”替换成该excel的文件路径；
//注：若新建一个excel文档，“xxx”替换成true即可；不过这里新建的excel文档默认只有一个sheet。
_Workbook _wbk = wbks.Add(true);
//取得sheet
Sheets shs = _wbk.Sheets;
//i是要取得的sheet的index
_Worksheet _wsh = (_Worksheet)shs.get_Item(1);
//_wsh.Cells[1, 1] = "
//添加行
//((Range)_wsh.Rows[11, Missing.Value]).Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//添加列
//_wsh.get_Range(_wsh.Cells[1, 1], Missing.Value).Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);
//_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1]).Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);
//设置单元格颜色
//((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;
_wsh.Cells[1, 1] = "流水号";
_wsh.Cells[1, 2] = "零件号";
_wsh.Cells[1, 3] = "零件名称";
_wsh.Cells[1, 4] = "数量";
_wsh.Cells[1, 5] = "工序";
_wsh.Cells[1, 6] = "工位";
_wsh.Cells[1, 7] = "计划号";
_wsh.Cells[1, 8] = "SO";
_wsh.Cells[1, 9] = "地点";
_wsh.Cells[1, 10] = "供应商";
_wsh.Cells[1, 11] = "类型";
_wsh.Cells[1, 12] = "保管员";
for (int i = 0; i < ASPxGridView1.VisibleRowCount; i++)
{
//添加行
//object a = ASPxGridView1.GetRow(i);
_wsh.Cells[i + 2, 1] = ASPxGridView1.GetRowValues(i, "GHTM");
_wsh.Cells[i + 2, 2] = ASPxGridView1.GetRowValues(i, "ABOM_COMP");
_wsh.Cells[i + 2, 3] = ASPxGridView1.GetRowValues(i, "ABOM_DESC");
_wsh.Cells[i + 2, 4] = ASPxGridView1.GetRowValues(i, "ABOM_QTY");
_wsh.Cells[i + 2, 5] = ASPxGridView1.GetRowValues(i, "ABOM_OP");
_wsh.Cells[i + 2, 6] = ASPxGridView1.GetRowValues(i, "ABOM_WKCTR");
_wsh.Cells[i + 2, 7] = ASPxGridView1.GetRowValues(i, "ABOM_JHDM");
_wsh.Cells[i + 2, 8] = ASPxGridView1.GetRowValues(i, "ABOM_SO");
_wsh.Cells[i + 2, 9] = ASPxGridView1.GetRowValues(i, "GZDD");
_wsh.Cells[i + 2, 10] = ASPxGridView1.GetRowValues(i, "ABOM_GYS");
_wsh.Cells[i + 2, 11] = ASPxGridView1.GetRowValues(i, "COMP_FLAG");
_wsh.Cells[i + 2, 12] = ASPxGridView1.GetRowValues(i, "BGY");
string compFlag = ASPxGridView1.GetRowValues(i, "COMP_FLAG").ToString();
switch (compFlag)
{
case "0":
//2代表白色
((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 2;
break;
case "1":
//3代表红色
((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 3;
break;
case "2":
//10代表不刺眼的绿色
((Range)_wsh.Rows[i + 2, Missing.Value]).Interior.ColorIndex = 10;
break;
default:
return;
}
}
//如果目录不存在则创建目录
if (!Directory.Exists("C:/excel"))
{
Directory.CreateDirectory("C:/excel");
}
//屏蔽掉系统跳出的覆盖文件Alert
excel.DisplayAlerts = false;
_wbk.Saved = true;
//保存到指定目录
//替换点否会报错
_wbk.SaveAs("C:/excel/改制差异清单明细信息导出.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//用@的方法只是不会报错，但\显示不出来
//showAlert(this, @"文件已保存至C:\excel\改制差异清单明细信息导出.xls");
//加一个中文的双引号就不会有什么问题
showAlert(this, "文件已成功保存！");
//设置回默认值
excel.DisplayAlerts = true;
excel.Quit();
//释放掉多余的excel进程
//??释放有问题，关闭电脑的时候会提示--将这些资源依次释放再设置为空，最后回收内存就不会再有问题
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)wbks);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)_wbk);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)shs);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)_wsh);
_wsh = null;
shs = null;
_wbk = null;
wbks = null;
excel = null;
GC.Collect(0);
}
catch
{
showAlert(this, "导出失败！");
return;
}
}
private void PutLessonData(String data, _Worksheet worksheet,int currentRow, ref int currentColumn)
{
currentColumn++;
worksheet.Cells[currentRow, currentColumn] = data;
}
/// <summary>
/// ConsolidatedXMLExportToExcel
/// </summary>
/// <param name="dt"></param>
/// <param name="ws"></param>
/// <param name="nextRow"></param>
/// <param name="isCompare"></param>
/// <param name="isPolicyWiseSummary"></param>
public string ConsolidatedXmlExportToExcel(DataTable dt, _Worksheet ws, bool nextRow, bool isCompare,
bool isPolicyWiseSummary)
{
// Copy the DataTable to an object array
var rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
int intRowNum;
if (nextRow)
{
if (ws.UsedRange.Rows.Count == 1)
{
intRowNum = ws.UsedRange.Rows.Count;
if (isCompare)
{
intRowNum = intRowNum + 2;
}
}
else
{
////intRowNum = ws.UsedRange.Rows.Count + 1;
if (isPolicyWiseSummary)
{
intRowNum = ws.UsedRange.Rows.Count;
}
else
{
intRowNum = ws.UsedRange.Rows.Count + 1;
}
}
}
else
{
intRowNum = ws.UsedRange.Rows.Count;
}
if (!(intRowNum > 1))
{
// Copy the column names to the first row of the object array
for (var col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (var col = 0; col < dt.Columns.Count; col++)
{
for (var row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
//if (dt.Rows.Count > 0)
//{
// ((Range)ws.Rows[intRowNum + dt.Rows.Count, Type.Missing]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
//}
}
else
{
if (isCompare || isPolicyWiseSummary)
{
for (var col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
for (var col = 0; col < dt.Columns.Count; col++)
{
for (var row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
}
else
{
// Copy the values to the object array
for (var col = 0; col < dt.Columns.Count; col++)
{
for (var row = 0; row < dt.Rows.Count; row++)
{
rawData[row, col] = dt.Rows[row].ItemArray[col];
}
}
}
if (dt.Rows.Count > 0)
{
((Range)ws.Rows[intRowNum + dt.Rows.Count, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.Yellow);
}
}
// Calculate the final column letter
var finalColLetter = string.Empty;
const string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
string excelRange;
if (intRowNum > 1)
{
if (nextRow)
{
excelRange =
string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}",
finalColLetter, intRowNum, (intRowNum + dt.Rows.Count));
}
else
{
excelRange =
string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}",
finalColLetter, intRowNum + 1, (intRowNum + dt.Rows.Count));
}
}
else
{
excelRange =
string.Format(CultureInfo.InvariantCulture, "A{1}:{0}{2}",
finalColLetter, intRowNum, ws.UsedRange.Rows.Count + dt.Rows.Count);
}
intRowNum = intRowNum + dt.Rows.Count + dt.Rows.Count;
ws.Range[excelRange, Type.Missing].NumberFormat = "@";
ws.Range[excelRange, Type.Missing].Value2 = rawData;
ws.Range[excelRange, Type.Missing].EntireColumn.AutoFit();
//ws.get_Range(excelRange, Type.Missing).AutoFit();
// Mark the first row as BOLD
var j = 2;
for (var i = 0; i < dt.Rows.Count; i++)
{
((Range)ws.Rows[1, Type.Missing]).Font.Bold = true;
((Range)ws.Rows[1, Type.Missing]).Font.Color = ColorTranslator.ToOle(Color.LightGray);
((Range)ws.Rows[1, Type.Missing]).Interior.Color = Color.Blue;
((Range)ws.Rows[j, Type.Missing]).Borders.Color = ColorTranslator.ToOle(Color.Black);
((Range)ws.Rows[j, Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
var value = dt.Rows[i]["PASS or FAIL"].ToString();
if (value == "PASS")
{
((Range)ws.Columns[1, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.LightGreen);
((Range)ws.Columns[2, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.LightGreen);
((Range)ws.Columns[3, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.LightGreen);
((Range)ws.Columns[4, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.LightGreen);
}
if (value == "FAIL")
{
((Range)ws.Columns[1, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.Red);
((Range)ws.Columns[2, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.Red);
((Range)ws.Columns[3, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.Red);
((Range)ws.Columns[4, Type.Missing].Rows[j, Type.Missing]).Interior.Color =
ColorTranslator.ToOle(Color.Red);
}
j++;
// if (text.Contains("Passed"))
// {
// ((Range)ws.Columns[5, Type.Missing]).Rows[2, Type.Missing].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
// }
// if (text.Contains("Failed"))
// {
// ((Range)ws.Columns[5, Type.Missing]).Rows[3, Type.Missing].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// }
}
return finalColLetter + "," + intRowNum;
}
public void ExportDataToExcel_New(string saveFileFullPath, string fileName, string[] tableName, string[] sheetName, DataSet ds)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, 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));
for (int i = 0; i < tableName.Length; i++)
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[i].Rows[j][k].ToString();
}
_IniRow++;
}
}
ExcelHelper excelHelper = new ExcelHelper();
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(saveFileFullPath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
/// <summary>
/// Validates the affected mapped range to check if the data is going to be imported
/// in any of the available range
/// </summary>
/// <param name="currentSheet">Current active sheet</param>
/// <param name="currentRange">Target range</param>
/// <returns>True if the data is in affected range</returns>
private bool ValidateAffectedMappedRange(_Worksheet currentSheet, Range currentRange)
{
bool isContinue = true;
if (currentSheet != null && currentRange != null && this.currentWorkbookMap != null
&& this.currentWorkbookMap.SelectedLayerMap != null)
{
Dictionary<string, string> allNamedRange = new Dictionary<string, string>();
// Get all Layer details into dictionary.
this.currentWorkbookMap.LocalLayerMaps.ForEach(item =>
{
allNamedRange.Add(item.RangeDisplayName, item.RangeAddress);
});
Dictionary<string, string> affectedMappedNamedRanges = currentSheet.GetAffectedNamedRanges(currentRange, allNamedRange);
foreach (LayerMap layerMap in this.currentWorkbookMap.LocalLayerMaps)
{
if (this.currentWorkbookMap.SelectedLayerMap.MapType == LayerMapType.WWT)
{
// Checks if the affected range is in any of the existing ranges for WWT layer
if (affectedMappedNamedRanges.ContainsKey(layerMap.RangeDisplayName) && layerMap.RangeName.IsValid())
{
Ribbon.ShowError(Properties.Resources.MappedWWTRangeError);
isContinue = false;
break;
}
}
else
{
// Checks if the affected range is in any of the existing ranges for local in WWT layer and
// the range is not the current range for selected layer
if (affectedMappedNamedRanges.ContainsKey(layerMap.RangeDisplayName) && layerMap.RangeName.IsValid() && !layerMap.RangeDisplayName.Equals(this.currentWorkbookMap.SelectedLayerMap.RangeDisplayName))
{
isContinue = Ribbon.ShowWarningWithResult(Properties.Resources.MappedLocalInWWTRangeError);
break;
}
}
}
}
else
{
isContinue = false;
}
return isContinue;
}
private static void WriteAttendanceStats(_Worksheet newWorksheet, int semester, int class_id)
{
var students = StudentsRepository.GetAllStudents(class_id);
int nothing = 0;
int neizvineni = 0;
List <Student> studentsNeizv = new List <Student>();
if (semester % 20 == 1)
{
nothing = students.Where(s => s.Attendances.Where(a => a.Date1.Month >= 2 && a.Date1.Month <= 6).Count() == 0).Count();
studentsNeizv = students.Where(s =>
s.Attendances
.Where(a =>
(a.Att_type == 0 || a.Att_type == 1) && (a.Date1.Month >= 2 && a.Date1.Month <= 6))
.Count() > 0).ToList();
neizvineni = studentsNeizv.Count;
}
else
{
nothing = students.Where(s => s.Attendances.Where(a => a.Date1.Month >= 9 || a.Date1.Month == 1).Count() == 0).Count();
studentsNeizv = students.Where(s =>
s.Attendances
.Where(a =>
(a.Att_type == 0 || a.Att_type == 1) && (a.Date1.Month >= 9 || a.Date1.Month == 1))
.Count() > 0).ToList();
neizvineni = studentsNeizv.Count;
}
x += 2;
int header = x;
newWorksheet.Range[newWorksheet.Cells[x, 1], newWorksheet.Cells[x, 2]].Merge();
newWorksheet.Cells[x, 1] = "Отсъствия";
newWorksheet.Range[newWorksheet.Cells[x, 1], newWorksheet.Cells[x, 2]].Font.Bold = true;
x++;
newWorksheet.Cells[x, 1] = "Без отсъствия";
newWorksheet.Cells[x, 2] = nothing.ToString();
x++;
newWorksheet.Cells[x, 1] = "С неизвинени отсъствия";
newWorksheet.Cells[x, 2] = neizvineni.ToString();
x++;
newWorksheet.Cells[x, 1] = "До 3 часа";
newWorksheet.Cells[x, 2] = studentsNeizv
.Where(s =>
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 3).Count();
x++;
newWorksheet.Cells[x, 1] = "До 10 часа";
newWorksheet.Cells[x, 2] = studentsNeizv
.Where(s =>
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 3 &&
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 10).Count();
x++;
newWorksheet.Cells[x, 1] = "До 15 часа";
newWorksheet.Cells[x, 2] = studentsNeizv
.Where(s =>
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 10 &&
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 15).Count();
x++;
newWorksheet.Cells[x, 1] = "До 25 часа";
newWorksheet.Cells[x, 2] = studentsNeizv
.Where(s =>
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 15 &&
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) <= 25).Count();
x++;
newWorksheet.Cells[x, 1] = "До 10 часа";
newWorksheet.Cells[x, 2] = studentsNeizv
.Where(s =>
s.Attendances.Where(a => a.Att_type == 0).Count() + (s.Attendances.Where(a => a.Att_type == 1).Count() / 3) > 25).Count();
newWorksheet.Range[newWorksheet.Cells[header, 1], newWorksheet.Cells[x, 2]].Borders.LineStyle
= Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//var att = AttendanceRepository.GetAttendanceBetweenDates(DateTime.Now.AddYears(-2), DateTime.Now.AddYears(2), class_id);
//if (semester == 2) att = att.Where(a => a.Date1.Month >= 2 && a.Date1.Month <= 6).ToList();
//else att = att.Where(a => a.Date1.Month >= 9 || a.Date1.Month == 1).ToList();
}
private void Generate_Click(object sender, RoutedEventArgs e)
{
List <string> NCProg_Tools = new List <string>();
Microsoft.Office.Interop.Excel.Application oXL;
_Workbook oWB;
_Worksheet oSheetProjectSummary = null;
_Worksheet oSheetProjectSummaryFull = null;
_Worksheet oSheetNCProgSummary = null;
_Worksheet oSheetNewNCProgSummary = null;
_Worksheet oSheetToolpathDetails = null;
_Worksheet oSheetToolList = null;
_Worksheet oSheetNewToolList = null;
Dictionary <string, string> VarsListProjectSummary = new Dictionary <string, string>();
Dictionary <string, string> VarsListProjectSummaryFull = new Dictionary <string, string>();
Dictionary <string, string> VarsListNCProgSummary = new Dictionary <string, string>();
Dictionary <string, string> VarsListToolpathDetails = new Dictionary <string, string>();
Dictionary <string, string> VarsListToolList = new Dictionary <string, string>();
bool Has_ProjectSummary = false;
bool Has_ProjectSummaryFull = false;
bool Has_NCProgSummary = false;
bool Has_ToolList = false;
bool Has_ToolpathDetails = false;
string Project_Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)");
string NCProgDetails = "";
string MergedModelList = "";
int FileQty = 0;
if (listNCProgsSelected.Items.Count == 0)
{
//No NC Program selected, do nothing...
MessageBox.Show("Please select at least one NC Program");
}
else
{
List <ToolInfo> ToolData = new List <ToolInfo>();
List <ToolpathInfo> ToolpathData = new List <ToolpathInfo>();
List <ToolInfo> ProjectToolData = new List <ToolInfo>();
List <ToolpathInfo> ProjectToolpathData = new List <ToolpathInfo>();
ProjectInfo Project = new ProjectInfo();
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
//Get the template from the option page
string Path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\ExcellSetupSheet\\Template.ini";
string TemplateFile = "";
if (File.Exists(Path))
{
const Int32 BufferSize = 128;
using (var fileStream = File.OpenRead(Path))
using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize))
{
String line;
while ((line = streamReader.ReadLine()) != null)
{
TemplateFile = line;
}
}
}
else
{
Path = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Substring(8, System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Length - 8);
string directory = System.IO.Path.GetDirectoryName(Path);
TemplateFile = directory + "\\Tool_List.xlsx";
}
if (!Directory.Exists(Project_Path + "\\Excel_Setupsheet\\"))
{
Directory.CreateDirectory(Project_Path + "\\Excel_Setupsheet\\");
}
string[] fileArrayXLSX = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xlsx");
string[] fileArrayXLS = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xls");
foreach (string File in fileArrayXLSX)
{
if (File.IndexOf("~$") < 0)
{
FileQty = FileQty + 1;
}
}
foreach (string File in fileArrayXLS)
{
if (File.IndexOf("~$") < 0)
{
FileQty = FileQty + 1;
}
}
if (FileQty > 0)
{
File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx");
TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx";
}
else
{
File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx");
TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx";
}
//Get a new workbook.
oWB = (_Workbook)(oXL.Workbooks.Open(TemplateFile));
List <string> ModelList = PowerMILLAutomation.GetListOf(PowerMILLAutomation.enumEntity.MachinableModels);
PowerMILLAutomation.GetModelsLimits(ModelList, out double ModelsMinX, out double ModelsMinY, out double ModelsMinZ, out double ModelsMaxX, out double ModelsMaxY, out double ModelsMaxZ);
Project = new ProjectInfo();
Project.MachModelsMaxX = ModelsMaxX.ToString();
Project.MachModelsMaxY = ModelsMaxY.ToString();
Project.MachModelsMaxZ = ModelsMaxZ.ToString();
Project.MachModelsMinX = ModelsMinX.ToString();
Project.MachModelsMinY = ModelsMinY.ToString();
Project.MachModelsMinZ = ModelsMinZ.ToString();
Project.Name = PowerMILLAutomation.ExecuteEx("print $project_pathname(1)");
Project.Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)");
Project.OrderNumber = PowerMILLAutomation.ExecuteEx("print $project.orderNumber");
Project.Programmer = PowerMILLAutomation.ExecuteEx("print $project.programmer");
Project.PartName = PowerMILLAutomation.ExecuteEx("print $project.partname");
Project.Customer = PowerMILLAutomation.ExecuteEx("print $project.customer");
Project.Date = DateTime.Now.ToString();
Project.Notes = PowerMILLAutomation.ExecuteEx("print $project.notes");
Project.ExcelTemplate = TemplateFile;
foreach (string Model in ModelList)
{
MergedModelList = MergedModelList + Environment.NewLine + Model;
}
Project.ModelsList = MergedModelList;
Project.CombinedNCTPList = "";
foreach (Worksheet worksheet in oWB.Worksheets)
{
if (worksheet.Name == "Project_Summary")
{
oSheetProjectSummary = worksheet;
//Extract template keywords and cell adresses
VarsListProjectSummary = WriteFiles.ExtractTemplateData(oSheetProjectSummary);
Has_ProjectSummary = true;
}
else if (worksheet.Name == "Project_Summary_Full")
{
oSheetProjectSummaryFull = worksheet;
//Extract template keywords and cell adresses
VarsListProjectSummaryFull = WriteFiles.ExtractTemplateData(oSheetProjectSummaryFull);
Has_ProjectSummaryFull = true;
}
else if (worksheet.Name == "NCProg_Summary")
{
oSheetNCProgSummary = worksheet;
//Extract template keywords and cell adresses
VarsListNCProgSummary = WriteFiles.ExtractTemplateData(oSheetNCProgSummary);
Has_NCProgSummary = true;
}
else if (worksheet.Name == "Toolpath_Details")
{
oSheetToolpathDetails = worksheet;
//Extract template keywords and cell adresses
VarsListToolpathDetails = WriteFiles.ExtractTemplateData(oSheetToolpathDetails);
Has_ToolpathDetails = true;
}
else if (worksheet.Name == "ToolList")
{
oSheetToolList = worksheet;
//Extract template keywords and cell adresses
VarsListToolList = WriteFiles.ExtractTemplateData(oSheetToolList);
Has_ToolList = true;
}
}
List <string> NCProg_Toolpaths = new List <string>();
foreach (String NCProg in listNCProgsSelected.Items)
{
if (NCProgDetails == "")
{
NCProgDetails = NCProg;
}
else
{
NCProgDetails = NCProgDetails + Environment.NewLine + NCProg;
}
NCProg_Toolpaths = PowerMILLAutomation.GetNCProgToolpathes(NCProg);
foreach (string Toolpath in NCProg_Toolpaths)
{
NCProgDetails = NCProgDetails + Environment.NewLine + " " + Toolpath;
}
Project.TotalTime = Project.TotalTime + double.Parse(PowerMILLAutomation.ExecuteEx("print $entity('ncprogram';'" + NCProg + "').Statistics.TotalTime"));
//Extract the PowerMILL parameters found in the template from the current NCProgram toolapths
WriteFiles.ExtractData(NCProg, out ToolData, out ToolpathData);
if (Has_ProjectSummaryFull)
{
foreach (ToolpathInfo Toolpath in ToolpathData)
{
ProjectToolpathData.Add(new ToolpathInfo
{
Name = Toolpath.Name,
Description = Toolpath.Description,
Notes = Toolpath.Notes,
ToolName = Toolpath.ToolName,
ToolNumber = Toolpath.ToolNumber,
ToolDiameter = Toolpath.ToolDiameter,
ToolType = Toolpath.ToolType,
ToolCutterLength = Toolpath.ToolCutterLength,
ToolHolderName = Toolpath.ToolHolderName,
ToolOverhang = Toolpath.ToolOverhang,
ToolNumberOfFlutes = Toolpath.ToolNumberOfFlutes,
ToolLengthOffset = Toolpath.ToolLengthOffset,
ToolRadOffset = Toolpath.ToolRadOffset,
ToolpathType = Toolpath.ToolpathType,
ToolTipRadius = Toolpath.ToolTipRadius,
ToolDescription = Toolpath.ToolDescription,
Thickness = Toolpath.Thickness,
AxialThickness = Toolpath.AxialThickness,
CutterComp = Toolpath.CutterComp,
Feed = Toolpath.Feed,
Speed = Toolpath.Speed,
IPT = Toolpath.IPT,
SFM = Toolpath.SFM,
PlungeFeed = Toolpath.PlungeFeed,
SkimFeed = Toolpath.SkimFeed,
Coolant = Toolpath.Coolant,
Stepover = Toolpath.Stepover,
DOC = Toolpath.DOC,
GeneralAxisType = Toolpath.GeneralAxisType,
Statistic_Time = Toolpath.Statistic_Time,
TPWorkplane = Toolpath.TPWorkplane,
Tolerance = Toolpath.Tolerance,
RapidHeight = Toolpath.RapidHeight,
SkimHeight = Toolpath.SkimFeed,
ToolpathMinX = Toolpath.ToolpathMinX,
ToolpathMinY = Toolpath.ToolpathMinY,
ToolpathMinZ = Toolpath.ToolpathMinZ,
ToolpathMaxX = Toolpath.ToolpathMaxX,
ToolpathMaxY = Toolpath.ToolpathMaxY,
ToolpathMaxZ = Toolpath.ToolpathMaxZ,
FirstLeadInType = Toolpath.FirstLeadInType,
SecondLeadInType = Toolpath.SecondLeadInType,
FirstLeadOutType = Toolpath.FirstLeadOutType,
SecondLeadOutType = Toolpath.SecondLeadOutType,
CuttingDistance = Toolpath.CuttingDistance,
NCProgName = NCProg
});
}
}
if (Has_NCProgSummary)
{
//Write the Excel document
int Index = oSheetNCProgSummary.Index;
oSheetNCProgSummary.Copy(oSheetNCProgSummary, Type.Missing);
if (NCProg.Length > 31)
{
oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 30);
}
else
{
oWB.Sheets[Index].Name = NCProg;
}
oSheetNewNCProgSummary = oWB.Sheets[Index];
if (Has_ToolpathDetails)
{
WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, true, Project_Path, oSheetNewNCProgSummary, NCProgDetails);
}
else
{
WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails);
}
}
if (Has_ToolpathDetails)
{
//Write the Excel document
WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetToolpathDetails, VarsListToolpathDetails, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails);
}
if (Has_ToolList)
{
//Write the Excel document
int Index = oSheetToolList.Index;
oSheetToolList.Copy(oSheetToolList, Type.Missing);
if (NCProg.Length > 25)
{
oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 24) + "-Tools";
}
else
{
oWB.Sheets[Index].Name = NCProg + "-Tools";
}
oWB.Sheets[Index].Name = NCProg + "-Tools";
oSheetNewToolList = oWB.Sheets[Index];
WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewToolList, VarsListToolList, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails);
}
}
Project.CombinedNCTPList = NCProgDetails;
if (Has_ProjectSummary)
{
WriteFiles.CreateExcelFile("None", ToolpathData, ToolData, Project, oSheetProjectSummary, VarsListProjectSummary, oWB, true, Project_Path, oSheetProjectSummary, NCProgDetails);
}
if (Has_ProjectSummaryFull)
{
WriteFiles.CreateExcelFile("None", ProjectToolpathData, ToolData, Project, oSheetProjectSummaryFull, VarsListProjectSummaryFull, oWB, true, Project_Path, oSheetProjectSummaryFull, NCProgDetails);
}
if (Has_NCProgSummary)
{
oSheetNCProgSummary.Delete();
}
if (Has_ToolpathDetails)
{
oSheetToolpathDetails.Delete();
}
if (Has_ToolList)
{
oSheetToolList.Delete();
}
oXL.DisplayAlerts = true;
oWB.Sheets[1].Activate();
oWB.Save();
MessageBox.Show("SetupSheet exported successfully");
}
}
static void Main(string[] args)
{
Application xlApp = new Application();
Workbook xlWorkBook = xlApp.Workbooks
.Open(@"C:\FF\211439_1_19_2020_copy.xlsx");
// Read Defensive Data
AllDefensiveRecords.ReadExcel(xlWorkBook);
// Read Kicker Data
KickerRecord.ReadExcel(xlWorkBook);
// Read Offensive Data
AllOffensiveRecords.ReadExcel(xlWorkBook);
// Read Schedule
ScheduleRecord.ReadExcel(xlWorkBook);
// Read Team Players
TeamPlayersRecord.ReadExcel(xlWorkBook);
// Read Teams
Team.ReadExcel(xlWorkBook);
xlWorkBook.Close();
// For each team summarize offensive record
// For each tema summarize defensive record
foreach (Team t in Team.Teams)
{
t.SummarizeRecord(true);
t.SummarizeRecord(false);
}
// Find average def values
foreach (Team t in Team.Teams)
{
t.DefenseSummary();
}
// Find average offensive coef
foreach (Team t in Team.Teams)
{
t.OffenseSummary();
}
// Calculate team counts
foreach (Team t in Team.Teams)
{
t.PosCounts();
}
// For each team find pos / metric coeficients
foreach (Team t in Team.Teams)
{
t.CalculatePosCoef();
}
// For each player summarize coeficients
Player.Init();
foreach (Player p in Player.AllPlayers)
{
p.FindPrevCoef();
}
// For each team find team players expected coeficients
foreach (Team t in Team.Teams)
{
t.UpdatePlayerCoef();
}
// For each team update schedule
foreach (Team t in Team.Teams)
{
t.UpdateSchedule();
}
// For each player find expected points
foreach (Player p in Player.AllPlayers)
{
p.FindExpectedPoints();
}
xlApp = new Application();
xlApp.Workbooks.Add();
_Worksheet workSheet = xlApp.ActiveSheet;
workSheet.Name = "Coeficients";
int col = 1;
workSheet.Cells[1, col++] = "Name";
workSheet.Cells[1, col++] = "Position";
foreach (string m in Enum.GetNames(typeof(Metric)))
{
workSheet.Cells[1, col++] = m;
}
col = 1;
int row = 2;
foreach (Player p in Player.AllPlayers)
{
workSheet.Cells[row, col++] = p.Name;
workSheet.Cells[row, col++] = p.Position.ToString();
foreach (Metric m in Enum.GetValues(typeof(Metric)))
{
workSheet.Cells[row, col++] = p.realCoef[m];
}
row++;
col = 1;
}
xlApp.Worksheets.Add();
workSheet = xlApp.ActiveSheet;
workSheet.Name = "Weeks";
col = 1;
workSheet.Cells[1, col++] = "Name";
workSheet.Cells[1, col++] = "Position";
workSheet.Cells[1, col++] = "Team";
workSheet.Cells[1, col++] = "Drafted";
workSheet.Cells[1, col++] = "ADP";
workSheet.Cells[1, col++] = "Tier";
for (int i = 1; i < 17; i++)
{
workSheet.Cells[1, col++] = "Week " + i;
}
col = 1;
row = 2;
foreach (Player p in Player.AllPlayers)
{
workSheet.Cells[row, col++] = p.Name;
workSheet.Cells[row, col++] = p.Position.ToString();
workSheet.Cells[row, col++] = p.Team.ShortName;
col += 3;
for (int i = 1; i < 17; i++)
{
if (p.expectedPoints.ContainsKey(i))
{
workSheet.Cells[row, col++] = p.expectedPoints[i];
}
else
{
workSheet.Cells[row, col++] = 0;
}
}
row++;
col = 1;
}
workSheet.SaveAs(@"C:\FF\testNew.xlsx");
xlApp.Quit();
}
private void LoadXL_Button_Click(object sender, EventArgs e)
{
OpenFileDialog log = new OpenFileDialog();
string filePath = "";
if (log.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = log.FileName;
//Order: Name, price, location, quantity, max quantity
//StreamReader reader = new StreamReader(filePath);
DataTypes.ShoppingList list = new DataTypes.ShoppingList();
//Excel stuff
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
list.SetListName("Loaded Excel List");
int rowCount = xlRange.Rows.Count;
for (int i = 1; i <= rowCount; i++)
{
DataTypes.ListItem item;
string name = "";
string location = "";
double price = 0;
int quantity = 0;
int maxQuantity = 0;
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
name = Convert.ToString(xlRange.Cells[i, 1].Value2);
}
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
{
price = Convert.ToDouble(xlRange.Cells[i, 2].Value2);
}
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
{
location = Convert.ToString(xlRange.Cells[i, 3].Value2);
}
if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
{
quantity = Convert.ToInt32(xlRange.Cells[i, 4].Value2);
}
if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
{
maxQuantity = Convert.ToInt32(xlRange.Cells[i, 5].Value2);
}
item = new DataTypes.ListItem(name, location, quantity, maxQuantity, (float)price);
list.AddItem(item);
}
//reader.Close();
parent.currList = list;
parent.FullListBox.Items.Clear();
parent.FullListBox.Items.AddRange(parent.currList.GetNameList().ToArray());
}
}
/// <summary>
/// 读取并保存Excel中的信息到数据库中
/// </summary>
private void ReadAndSaveExcelInfo()
{
string strTmpTable, strTmpField, strTmpValue;
string val1 = "";
string val2 = "";
string val3 = "";
string val4 = "";
string val5 = "";
string val6 = "";
string val7 = "";
string val8 = "";
string val9 = "";
string val10 = "";
string val11 = "";
string val12 = "";
string val13 = "";
string val14 = "";
string val15 = "";
string val16 = "";
int num = 0;
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
Excel.Range range = worksheet.get_Range("A2", "P65535");//A2、O65535表示到Excel从第A列的第2行到第O列的第65535-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行
System.Array values = (System.Array)range.Formula;
num = values.GetLength(0);
//(OWC11.XlBorderWeight.xlThin); //边框细线
//((Excel.Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).Borders.Weight = 25; //单元格高度
//((Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 20; //单元格列宽度
//((Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).MergeCells(true); //合并单元格
//((Range)worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 3])).Font.Bold(true); //字体粗体
//if (values.GetValue(1, 1).ToString().Trim() != "车证号" && values.GetValue(1, 2).ToString().Trim() != "车号" && values.GetValue(1, 3).ToString().Trim() != "合同号")
//{
// MessageBox.Show("预报选择错误，请重新选择Excel文件: '汽车衡预报模板'！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
// return;
//}
for (int i = 1; i <= num; i++)
{
//if (values.GetValue(i, 1).ToString().Trim() == "" && values.GetValue(i, 2).ToString().Trim() == "" && values.GetValue(i, 5).ToString().Trim() == "")
//{
// num = i;
//}
if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "")
{
break;
}
else
{
val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期
val2 = values.GetValue(i, 2).ToString().Trim(); //订单号
val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号
// val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码
// val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称
val6 = values.GetValue(i, 4).ToString().Trim(); //批号
val7 = values.GetValue(i, 5).ToString().Trim(); //收货数量
val8 = values.GetValue(i, 6).ToString().Trim(); //计量单位
val9 = values.GetValue(i, 7).ToString().Trim(); //工厂
val10 = values.GetValue(i, 8).ToString().Trim(); //库存地点
// val11 = values.GetValue(i, 11).ToString().Trim(); //移动类型
val12 = values.GetValue(i, 9).ToString().Trim(); //上工序批次
// val13 = values.GetValue(i, 13).ToString().Trim(); //特殊库存标志
//val14 = values.GetValue(i, 14).ToString().Trim(); //销售订单号
//val15 = values.GetValue(i, 15).ToString().Trim(); //销售订单行项目号
val16 = values.GetValue(i, 10).ToString().Trim(); //抬头文本
string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName();
string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment();
strTmpTable = "dt_carweight_weight";
strTmpField = "fs_weightno,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_STOVENO,FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,fd_grossdatetime";
strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','" + val6 + "','" + val7 + "','" + val9 + "','"
+ val10 + "',sysdate";
CoreClientParam ccp = new CoreClientParam();
ccp.ServerName = "ygjzjl.base.QueryData";
ccp.MethodName = "insertDataInfo";
ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue };
this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
}
}
}
public void createOneWayAnova(List <Variable> variables)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("One-Way ANOVA");
sheet.Cells[1, 1] = "ANOVA Summary";
sheet.Cells[2, 1] = "Total Sample Size";
sheet.Cells[3, 1] = "Grand Mean";
sheet.Cells[4, 1] = "Pooled Std Dev";
sheet.Cells[5, 1] = "Pooled Variance";
sheet.Cells[6, 1] = "Number of Samples";
sheet.Cells[7, 1] = "Confidence Level";
sheet.Cells[9, 1] = "ANOVA Sample Stats";
sheet.Cells[10, 1] = "Sample Size";
sheet.Cells[11, 1] = "Sample Mean";
sheet.Cells[12, 1] = "Sample Std Dev";
sheet.Cells[13, 1] = "Sample Variance";
sheet.Cells[14, 1] = "Pooling Weight";
sheet.Cells[17, 1] = "One-Way ANOVA Table";
sheet.Cells[18, 1] = "Between Variation";
sheet.Cells[19, 1] = "Within Variation";
sheet.Cells[20, 1] = "Total Variation";
sheet.Cells[23, 1] = "Confidence Interval Tests";
sheet.Cells[16, 2] = "Sum of";
sheet.Cells[17, 2] = "Squares";
sheet.Cells[16, 3] = "Degrees of";
sheet.Cells[17, 3] = "Freedom";
sheet.Cells[16, 4] = "Mean";
sheet.Cells[17, 4] = "Squares";
sheet.Cells[17, 5] = "F-Ratio";
sheet.Cells[17, 6] = "p-Value";
sheet.Cells[22, 2] = "Difference";
sheet.Cells[23, 2] = " of Means";
int col = 1;
foreach (Variable variable in variables)
{
col++;
var range = variable.getRange().Address(true, true, true);
sheet.Cells[9, col] = variable.name;
sheet.WriteFunction(10, col, "COUNT(" + range + ")");
sheet.WriteFunction(11, col, "AVERAGE(" + range + ")");
sheet.WriteFunction(12, col, "STDEV.S(" + range + ")");
sheet.WriteFunction(13, col, "VAR.S(" + range + ")");
}
sheet.WriteFunction(2, 2, "SUM(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ")");
sheet.WriteFunction(3, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + "," + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + ")/" + AddressConverter.CellAddress(2, 2, false, false));
sheet.Cells[6, 2] = variables.Count;
sheet.Cells[7, 2] = model.confidenceLevel;
((Range)sheet.Cells[7, 2]).NumberFormat = "0.00%";
col = 1;
foreach (Variable variable in variables)
{
col++;
sheet.WriteFunction(14, col, "(" + AddressConverter.CellAddress(10, col, false, false) + "-1)/(B2-B6)");
}
sheet.WriteFunction(5, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(13, 2, false, false) + ":" + AddressConverter.CellAddress(13, col, false, false) + "," + AddressConverter.CellAddress(14, 2, false, false) + ":" + AddressConverter.CellAddress(14, col, false, false) + ")");
sheet.WriteFunction(4, 2, "SQRT(" + AddressConverter.CellAddress(5, 2, false, false) + ")");
sheet.WriteFunction(18, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ",(" + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + "-B3)^2)");
sheet.WriteFunction(19, 2, "(B2-" + variables.Count + ")*B5");
sheet.WriteFunction(20, 2, "B18+B19");
sheet.Cells[18, 3] = variables.Count - 1;
sheet.WriteFunction(19, 3, "B2-" + variables.Count);
sheet.WriteFunction(20, 3, "C18+C19");
sheet.WriteFunction(18, 4, "B18/C18");
sheet.WriteFunction(19, 4, "B19/C19");
sheet.WriteFunction(18, 5, "D18/D19");
sheet.WriteFunction(18, 6, "F.DIST.RT(E18,C18,C19)");
int row = 24;
int c = 0;
for (int i = 0; i < variables.Count; i++)
{
for (int j = i + 1; j < variables.Count; j++)
{
c++;
Variable var1 = variables[i];
Variable var2 = variables[j];
sheet.Cells[row, 1] = var1.name + " - " + var2.name;
sheet.WriteFunction(row, 2, AddressConverter.CellAddress(11, i + 2, false, false) + "-" + AddressConverter.CellAddress(11, j + 2, false, false));
col = 3;
if (model.noCorrection)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.bonferroni)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.scheffe)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
row++;
}
}
col = 3;
if (model.noCorrection)
{
sheet.Cells[22, col] = "No Correction";
sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.bonferroni)
{
sheet.Cells[22, col] = "Bonferroni";
sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.scheffe)
{
sheet.Cells[22, col] = "Scheffe";
sheet.get_Range(AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)).Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.get_Range(AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range(AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
}
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
sheet.get_Range("B1", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.get_Range("A1", "B1").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("A9", AddressConverter.CellAddress(9, variables.Count + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("A17", "F17").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("A23", "B17").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("B3", "B5").NumberFormat = "0.0000";
sheet.get_Range("B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)).NumberFormat = "0.000";
sheet.get_Range("B18", "B20").NumberFormat = "0.0000";
sheet.get_Range("D18", "E19").NumberFormat = "0.0000";
sheet.get_Range("B24", AddressConverter.CellAddress(23 + c, 2, false, false)).NumberFormat = "0.0000";
sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).NumberFormat = "0.000000";
sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
}
public static MailMessage Read1MailFromExcel(_Worksheet excelSheet, int rowIndex, Action<String> ErrorOutput)
{
if (excelSheet.Cells[rowIndex, 1].Value2 != null)
{
MailMessage newMail = new MailMessage();
//加入寄件者
newMail.From = new MailAddress(((String)excelSheet.Cells[rowIndex, 1].Value2).Trim());
//加入收件者
if (excelSheet.Cells[rowIndex, 2].Value2 != null)
{
String[] Tos = ((String)excelSheet.Cells[rowIndex, 2].Value2).Split(',', ';', '\n');
foreach (var to in Tos)
{
MailAddress adress = null;
try
{
adress = new MailAddress(to.Trim());
newMail.To.Add(adress);
}
catch
{
if (ErrorOutput != null)
ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to);
}
}
}
//加入副本
if (excelSheet.Cells[rowIndex, 3].Value2 != null)
{
String[] CCs = ((String)excelSheet.Cells[rowIndex, 3].Value2).Split(',', ';', '\n');
foreach (var to in CCs)
{
MailAddress adress = null;
try
{
adress = new MailAddress(to.Trim());
newMail.CC.Add(adress);
}
catch
{
if (ErrorOutput != null)
ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to);
}
}
}
//加入密件副本
if (excelSheet.Cells[rowIndex, 4].Value2 != null)
{
String[] BCCs = ((String)excelSheet.Cells[rowIndex, 4].Value2).Split(',', ';', '\n');
foreach (var to in BCCs)
{
MailAddress adress = null;
try
{
adress = new MailAddress(to.Trim());
newMail.Bcc.Add(adress);
}
catch
{
if (ErrorOutput != null)
ErrorOutput(rowIndex + " Something wrong with Mail Adress : " + to);
}
}
}
//信件標題
if (excelSheet.Cells[rowIndex, 5].Value2 != null)
{
String Subject = excelSheet.Cells[rowIndex, 5].Value2;
newMail.Subject = Subject;
}
else
if (ErrorOutput != null)
ErrorOutput(rowIndex + " Subject is null!");
//信件內容
if (excelSheet.Cells[rowIndex, 6].Value2 != null)
{
String Body = excelSheet.Cells[rowIndex, 6].Value2;
newMail.Body = Body;
}
else
if (ErrorOutput != null)
ErrorOutput(rowIndex + " Body is null!");
//附件
String[] Attachments = ((String)excelSheet.Cells[rowIndex, 7].Value2)?.Split(',', ';', '\n');
if (excelSheet.Cells[rowIndex, 7].Value2 != null)
{
//[] Attachments = ((String)excelSheet.Cells[rowIndex, 7].Value2).Split(',', ';', '\n');
Attachments = (from filepath in Attachments
select filepath.Trim()).ToArray();
foreach (var fileName in Attachments)
if (File.Exists(@fileName))
{
newMail.Attachments.Add(new Attachment(File.Open(@fileName, FileMode.Open, FileAccess.Read, FileShare.Read), Path.GetFileName(@fileName)));
}
else if (File.Exists(fileName))
{
newMail.Attachments.Add(new Attachment(File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read), Path.GetFileName(fileName)));
}
else
{
if (ErrorOutput != null)
ErrorOutput(rowIndex + " File not found : " + fileName + "!");
}
}
return newMail;
}
else
{
return null;
}
}
/// <summary>
/// Gets layer data for WWT layer
/// </summary>
/// <param name="workSheet">Active worksheet</param>
private void GetLayerDataForWWT(_Worksheet workSheet)
{
if (workSheet != null)
{
object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false);
if (layerData != null && layerData.Length > 0)
{
// Gets the range from the excel for data row and columns
Range currentRange = workSheet.GetRange(ThisAddIn.ExcelApplication.ActiveCell, layerData.GetLength(0), layerData.GetLength(1));
if (currentRange != null)
{
if (ValidateAffectedMappedRange(workSheet, currentRange))
{
string address = currentRange.Address;
if (currentRange != null)
{
currentRange.Select();
InsertRows(currentRange);
// Gets the new range for with the active cell address
Range newRange = workSheet.Application.Range[address];
// Creates named range for the new range
CreateRangeForLayer(newRange);
newRange.SetValue(layerData);
SetFormatForDateColumns(workSheet);
}
}
}
}
}
}
private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
{
double n = (_sheet.Cells[2, 2] as Range).Value;
double p = (_sheet.Cells[3, 2] as Range).Value;
double alpha = (double)model.alpha;
int tail = 1;
if (input.Equals("equal"))
{
alpha = alpha / 200.0;
tail = 2;
}
else
{
alpha = alpha / 100.0;
tail = 1;
}
double p0 = Convert.ToDouble(model.Null);
double error = Math.Sqrt((p0 * (1 - p0)) / n);
var row = rowIn;
_sheet.Cells[row, 1] = "Hypothesized mean";
_sheet.Cells[row++, 2] = model.Null;
_sheet.Cells[row, 1] = "Alternative Hypothesis";
if (input.Equals("equal"))
{
_sheet.Cells[row++, 2] = "'=/= " + model.Null;
}
else
{
_sheet.Cells[row++, 2] = "> " + model.Null;
}
_sheet.Cells[row, 1] = "Alpha";
_sheet.Cells[row++, 2] = (double)model.alpha / 100.0;
_sheet.Cells[row, 1] = "Standard Error";
_sheet.Cells[row++, 2] = error;
_sheet.Cells[row, 1] = "Z-Test Statistic";
double z = (p - p0) / error;
_sheet.Cells[row++, 2] = z;
_sheet.Cells[row, 1] = "p-Value";
double pValue = _sheet.Application.WorksheetFunction.NormSDist(z);
_sheet.Cells[row++, 2] = pValue;
_sheet.Cells[row, 1] = "Null Hypothesis";
if (pValue <= alpha)
{
_sheet.Cells[row, 2] = "Reject";
}
else
{
_sheet.Cells[row, 2] = "Accept";
}
}
private void axWebBrowser2_NavigateComplete2(object sender, AxSHDocVw.DWebBrowserEvents2_NavigateComplete2Event e)
{
/// return;
object o = e.pDisp;
oWebBrowser = e.pDisp;
try
{
Object oDocument = o.GetType().InvokeMember("Document", BindingFlags.GetProperty, null, o, null);
Object oApplication = o.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, oDocument, null);
Excel.Application eApp = (Excel.Application)oApplication;
eApp.UserControl = true;
//Inputexcel(eApp);
//textexcel();
#region 方法2
//Object refmissing = System.Reflection.Missing.Value;
//object[] args = new object[4];
//args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS;
//args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER;
//args[2] = refmissing;
//args[3] = refmissing;
//object axWebBrowser = this.webBrowser1.ActiveXInstance;
//axWebBrowser.GetType().InvokeMember("ExecWB",
// BindingFlags.InvokeMethod, null, axWebBrowser, args);
//object Application = axWebBrowser.GetType().InvokeMember("Document",
// BindingFlags.GetProperty, null, axWebBrowser, null);
//Excel.Workbook wbb = (Excel.Workbook)oApplication;
//Excel.ApplicationClass excel = wbb.Application as Excel.ApplicationClass;
//Excel.Workbook wb = excel.Workbooks[1];
//Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
//ws.Cells.Font.Name = "Verdana";
//ws.Cells.Font.Size = 14;
//ws.Cells.Font.Bold = true;
//Excel.Range range = ws.Cells;
//Excel.Range oCell = range[10, 10] as Excel.Range;
//oCell.Value2 = "你好";
#endregion
#region inster tx
//object objBooks = eApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, eApp, null);
////添加一个新的Workbook
//object objBook = objBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks, null);
////获取Sheet集
//object objSheets = objBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook, null);
////获取第一个Sheet对象
//object[] Parameters = new Object[1] { 1 };
//object objSheet = objSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets, Parameters);
//Parameters = new Object[2] { 1, 1 + 1 };
//object objCells = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, Parameters);
////向指定单元格填写内容值
//Parameters = new Object[1] { "name" };
//objCells.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objCells, Parameters);
#endregion
#region 一、首先简要回顾一下如何操作Excel表
Workbooks workbooks = eApp.Workbooks;
Excel.ApplicationClass excel = workbooks.Application as Excel.ApplicationClass;
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)workbooks.get_Item(1);
Excel.Workbook wb = excel.Workbooks[1];
//_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
int c = workbooks.Count;
_Workbook workbook = workbooks.Add(ZFCEPath);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
Range range1 = worksheet.get_Range("A1", Missing.Value);
const int nCells = 2345;
range1.Value2 = nCells;
#endregion
ExcelExit();
}
catch (Exception ex)
{
ExcelExit();
throw;
}
}
private void PrintCategories2(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
{
double n = (_sheet.Cells[2, 2] as Range).Value;
double n1 = (_sheet.Cells[2, 3] as Range).Value;
double p = (_sheet.Cells[3, 2] as Range).Value;
double p1 = (_sheet.Cells[3, 3] as Range).Value;
double alpha = (double)model.alpha;
int tail = 1;
if (input.Equals("equal"))
{
alpha = alpha / 200.0;
tail = 2;
}
else
{
alpha = alpha / 100.0;
tail = 1;
}
var row = rowIn;
_sheet.Cells[row, 1] = "Pooled Proportion";
double pooled = ((int)(n * p) + (int)(n1 * p1)) / (n + n1);
double error = Math.Sqrt((pooled * (1 - pooled)) * (1.0 / n + 1.0 / n1));
_sheet.Cells[row++, 2] = pooled;
_sheet.Cells[row, 1] = "Difference Between Proportions";
_sheet.Cells[row++, 2] = p - p1;
_sheet.Cells[row, 1] = "Hypothesized mean";
_sheet.Cells[row++, 2] = 0;
_sheet.Cells[row, 1] = "Alternative Hypothesis";
if (input.Equals("equal"))
{
_sheet.Cells[row++, 2] = "'=/= " + 0;
}
else
{
_sheet.Cells[row++, 2] = "> " + 0;
}
_sheet.Cells[row, 1] = "Alpha";
_sheet.Cells[row++, 2] = (double)model.alpha / 100.0;
_sheet.Cells[row, 1] = "Standard Error";
_sheet.Cells[row++, 2] = error;
_sheet.Cells[row, 1] = "Test Statistic";
double z = (p - p1) / error;
_sheet.Cells[row++, 2] = z;
_sheet.Cells[row, 1] = "p-Value";
double pValue = _sheet.Application.WorksheetFunction.TDist(z, n - 1, tail);
//double pValue = _sheet.Application.WorksheetFunction.NormSDist(z);
_sheet.Cells[row++, 2] = pValue;
_sheet.Cells[row, 1] = "Null Hypothesis";
if (pValue <= alpha)
{
_sheet.Cells[row, 2] = "Reject";
}
else
{
_sheet.Cells[row, 2] = "Accept";
}
}
public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate)
{
PreExitExcel();
string filename1 = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, 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));
for (int i = 0; i < tableNames.Length; i++)
{
_IniRow = irow[i];
switch (tableNames[i].ToString())
{
case "AnnuityOpenPeriod":
case "AnnuityUrgent":
case "AnnuityStatusUrgent":
_SName = "AnnuityFinal";
break;
case "AnnuityRpt":
_SName = "AnnuityRpt";
break;
case "AnnuityStatus":
_SName = "AnnuityStatus";
break;
case "EBusinessRpt":
_SName = "EBusinessRpt Grouped";
break;
default: ;
break;
}
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName];
for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString();
}
_IniRow++;
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate);
_ExcelApp.Rows.RowHeight = "15";
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
public void createHypothese(List <Variable> variables, DataSet dataSet)
{
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test");
if (variables.Count == 1)
{
foreach (Variable variable in variables)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variable.name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
string ran = variable.Range.ToString();
Array dist = dataSet.getWorksheet().Range[ran].Value;
double count = 0;
foreach (var item in dist)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count = count + 1;
}
}
double n = (worksheet.Cells[2, 2] as Range).Value;
double prop = count / n;
worksheet.Cells[3, 2] = prop;
if (model.equal)
{
PrintCategories(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories(worksheet, 5, "greater", dataSet);
}
}
}
else if (variables.Count == 2)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variables[0].name;
worksheet.Cells[1, 3] = variables[1].name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
string ran = variables[0].Range.ToString();
string ran1 = variables[1].Range.ToString();
Array dist = dataSet.getWorksheet().Range[ran].Value;
Array dist1 = dataSet.getWorksheet().Range[ran1].Value;
double count = 0;
double count1 = 0;
foreach (var item in dist)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count = count + 1;
}
}
foreach (var item in dist1)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count1 = count1 + 1;
}
}
double n = (worksheet.Cells[2, 2] as Range).Value;
double n1 = (worksheet.Cells[2, 3] as Range).Value;
double prop = count / n;
double prop1 = count1 / n1;
worksheet.Cells[3, 2] = prop;
worksheet.Cells[3, 3] = prop1;
if (model.equal)
{
PrintCategories2(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories2(worksheet, 5, "greater", dataSet);
}
}
}
public static bool ExportForDataGridview(System.Data.DataTable dt, string fileName, bool isShowExcle)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return(false);
}
app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return(false);
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + dt.Columns.Count / 26);
char L = (char)(64 + dt.Columns.Count % 26);
if (dt.Columns.Count < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
asCaption[i] = dt.Columns[i].ColumnName;
}
ranCaption.Value2 = asCaption;
//数据
object[] obj = new object[dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count - 1; r++)
{
for (int l = 0; l < dt.Columns.Count; l++)
{
if (dt.Rows[r][l].GetType() == typeof(DateTime))
{
obj[l] = dt.Rows[r][l].ToString();
}
else
{
obj[l] = dt.Rows[r][l].ToString();
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
return(true);
}
public PercentPoorEntireStateDeckArea(string networkId, string simulationId, _Worksheet oSheet, BridgeAnalysis analysis)
: base(networkId, simulationId, oSheet, analysis)
{
}
public static void OutputExcelChk_Microsoft(DataGridView dgvCarPlay, string Title, string savePath)
{
Microsoft.Office.Interop.Excel.Application o = null;
try
{
o = new ApplicationClass();
}
catch (Exception exception)
{
MessageBox.Show("Excel无法启动，请确保已安装Excel 2003 及以上版本！");
Record.execFileRecord("导出报表", exception.ToString());
return;
}
try
{
int num = 4;
int num2 = 1;
int num3 = 0;
int num4 = 2;
_Workbook workbook = o.Workbooks.Add(true);
_Worksheet activeSheet = (_Worksheet)workbook.ActiveSheet;
activeSheet.Name = Title;
num3 = num2;
foreach (DataGridViewColumn column in dgvCarPlay.Columns)
{
if (column.Visible && (column.CellType != typeof(DataGridViewCheckBoxCell)))
{
num3++;
activeSheet.Cells[num, num3] = column.HeaderText;
activeSheet.get_Range(activeSheet.Cells[num, num3], activeSheet.Cells[num, num3]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
if (column.CellType == typeof(DataGridViewTextBoxCell))
{
activeSheet.get_Range(activeSheet.Cells[num, num3], activeSheet.Cells[num + dgvCarPlay.Rows.Count, num3]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
}
}
}
foreach (DataGridViewRow row in (IEnumerable)dgvCarPlay.Rows)
{
if ((row.Cells["colSelect"].Value != null) && "true".Equals(row.Cells["colSelect"].Value.ToString().ToLower()))
{
num++;
num3 = num2;
foreach (DataGridViewColumn column2 in dgvCarPlay.Columns)
{
if (column2.Visible && (column2.CellType != typeof(DataGridViewCheckBoxCell)))
{
num3++;
activeSheet.Cells[num, num3] = "'" + row.Cells[column2.Index].Value;
}
}
continue;
}
}
int num5 = num + 1;
num3 = num2;
foreach (DataGridViewColumn column3 in dgvCarPlay.Columns)
{
if (column3.Visible)
{
num3++;
}
}
num5 = num + 2;
activeSheet.Cells[num5, 2] = "合计:";
activeSheet.get_Range(activeSheet.Cells[num5, 2], activeSheet.Cells[num5, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
activeSheet.Cells[num5, 3] = ((num - 4)).ToString() + "条记录";
activeSheet.get_Range(activeSheet.Cells[num5, 3], activeSheet.Cells[num5, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
activeSheet.Cells[2, num4] = Title;
num4 = 1;
activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.Bold = true;
activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.Size = 0x16;
activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num4]).Font.ColorIndex = 14;
activeSheet.get_Range(activeSheet.Cells[4, num4], activeSheet.Cells[num5, num3]).Select();
activeSheet.get_Range(activeSheet.Cells[4, num4], activeSheet.Cells[num5, num3]).Columns.AutoFit();
activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num3]).Select();
activeSheet.get_Range(activeSheet.Cells[2, num4], activeSheet.Cells[2, num3]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
if (string.IsNullOrEmpty(savePath))
{
savePath = @"C:\Documents and Settings\Administrator\My Documents\Report.xls";
}
workbook.SaveCopyAs(savePath);
workbook.Close(false, null, null);
o.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(o);
Marshal.ReleaseComObject(activeSheet);
workbook = null;
o = null;
activeSheet = null;
GC.Collect();
MessageBox.Show("数据已经成功导出到：" + savePath, "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception exception2)
{
MessageBox.Show(exception2.Message);
}
}
public static string GetMkbDataFromFile(string mkbFilePath)
{
CultureInfo oldCi = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
_excelProcessesBeforeStartWork = Process.GetProcessesByName("EXCEL");
// Стартуем Excel-приложение
_oxl = new Application();
_oxb = _oxl.Workbooks.Open(mkbFilePath, 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);
object[,] ksgData = GetKsgData();
try
{
_ows = (_Worksheet)_oxb.Sheets["МКБ 10"];
}
catch
{
throw new Exception("Лист с именем 'МКБ 10' не найден. Вероятно, указанный файл имеет неверный формат.");
}
int rowCnt = 2;
int shag = 10000;
while (shag > 0)
{
while (((Range)_ows.Cells[rowCnt, 1]).Value2 != null)
{
rowCnt += shag;
}
rowCnt -= shag;
shag /= 10;
}
var data = (object[, ])_ows.get_Range(_ows.Cells[2, 1], _ows.Cells[rowCnt, 3]).Value2;
var sb = new StringBuilder();
for (int i = 1; i < rowCnt; i++)
{
string mkbCode = data[i, 1]?.ToString();
if (string.IsNullOrEmpty(mkbCode))
{
continue;
}
sb.AppendFormat("Code={0}{4}Name={1}{4}KsgCode={2}{4}KsgName={3}{5}",
mkbCode,
data[i, 2],
data[i, 3],
GetKsgNameByCode(ksgData, data[i, 3]?.ToString()),
DbEngine.DataSplitStr,
DbEngine.ObjSplitStr);
}
return(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("В процессе загрузки кодов МКБ произошла ошибка:\r\n" + ex);
}
finally
{
QuitExcel();
Thread.CurrentThread.CurrentCulture = oldCi;
}
}
/// <summary>
/// Activates a worksheet on the current active workbook via its name.
/// </summary>
/// <param name="name">The name of the workbook</param>
/// <returns>true if the worksheet was found, false otherways</returns>
public bool activateWorksheet(string name)
{
foreach (_Worksheet worksheet in excel.ActiveWorkbook.Sheets)
{
if (worksheet.Name == name)
{
worksheet.Activate();
this.worksheet = worksheet;
return true;
}
}
return false;
}
private static void QuitExcel()
{
if (_oxl != null)
{
if (_oxb != null)
{
_oxb.Close(false, Missing.Value, Missing.Value);
if (_ows != null)
{
Marshal.ReleaseComObject(_ows);
GC.GetTotalMemory(true);
_ows = null;
}
if (_owr != null)
{
Marshal.ReleaseComObject(_owr);
GC.GetTotalMemory(true);
_owr = null;
}
Marshal.ReleaseComObject(_oxb);
GC.GetTotalMemory(true);
_oxb = null;
}
_oxl.Quit();
Marshal.ReleaseComObject(_oxl);
GC.GetTotalMemory(true);
_oxl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
Thread.Sleep(100);
}
_excelProcessesAfterStartWork = Process.GetProcessesByName("EXCEL");
//
// Find created process and kill it, if standart close is not successful
//
for (int i = 0; i < _excelProcessesAfterStartWork.Length; i++)
{
bool flag = true;
for (int j = 0; j < _excelProcessesBeforeStartWork.Length; j++)
{
if (_excelProcessesBeforeStartWork[j].Id == _excelProcessesAfterStartWork[i].Id)
{
flag = false;
break;
}
}
if (flag)
{
WinExec("taskkill /F /PID " + _excelProcessesAfterStartWork[i].Id, 0);
}
}
}
//读取数据
private void btRead_Click(object sender, EventArgs e)
{
int MAXLINE = 5000;
int i = 0, j = 0, k = 0, m = 0;//m为总行数
int fileCount = lvFile.Items.Count;
string DataTag;
int eCount = 0;//有效工作簿数
int sCount = 0;//当前表中工作簿数
Point point;
Object missing = Type.Missing;
int iCount = lbContent.Items.Count;
//重点区域，范围型读取单元格区域
RangeSelector mainRange = new RangeSelector(tbMainRange.Text);
//预判断块读取还是固定位置读取，初始化总数组大小
if (mainRange.getWidth() > 0)
myArray = new String[MAXLINE, mainRange.getWidth() + iCount + 1];//最多千行
else
myArray = new String[MAXLINE, iCount + 1];//最多千行
//開啟一個新的應用程式
myExcel = new Excel.Application();
for (i = 0; i < fileCount; i++)
{
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks.Open(lvFile.Items[i].SubItems[2].Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//設定活頁簿焦點
myBook.Activate();
//判断所有工作簿
sCount = myBook.Worksheets.Count;
for (k = 1; k <= sCount; k++)
{
//大表判断条件
if (cbSheetSelect.Text != "全部" && Int16.Parse(cbSheetSelect.Text) != k) continue;
//选择当前表
mySheet = (Worksheet)myBook.Worksheets[k];
//設工作表焦點
mySheet.Activate();
//特征值判断
if (tbSheetPos.Text != "")
{
point = pointPos(tbSheetPos.Text);
if (mySheet.Cells[point.Y, point.X].Value != tbSheetCont.Text) continue;
}
eCount++;
//备注列判断
if (tbDataTag.Text == "") DataTag = lvFile.Items[i].SubItems[0].Text; //未设置备注默认使用文件名
else
{
Point tagpos = pointPos(tbDataTag.Text);
DataTag = Convert.ToString(mySheet.Cells[tagpos.Y, tagpos.X].Value);
}
string mainStart = tbMainStart.Text;
string mainEnd = tbMainEnd.Text;
//判断选择哪种模式
if (mainRange.Count() > 1)
{
mainRange = new RangeSelector(tbMainRange.Text);//重新恢复原区域值
//重点区域起始位置判断
Point nowPos = mainRange.getCurPos();
for (j = 0; j < mainRange.Count(); j++)
{
string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (mainStart == "") break;
if (myCell == mainStart) break;
mainRange.acc();
}
//mainRange.lineacc(); //移到关键字下一行
mainRange.SetStartVal(mainRange.getCurPos());
//读取内容
while (m < MAXLINE) //最大读取行数上限估计
{
nowPos = mainRange.getCurPos();
string lineFirstCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (lineFirstCell == null|| lineFirstCell=="") break; //首字为空
if (lineFirstCell == tbMainEnd.Text) break; //符合结束字符串
if (mainRange.pos > mainRange.Count()) break;//读取完了就退出
for (j = 0; j < mainRange.getWidth(); j++)//读取一行
{
point = mainRange.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
mainRange.acc();
}
myArray[m, j] = DataTag;
m++;
}
}
else
{
//准备读取单元格相关信息，固定位置读取单元格
if (iCount >= 1)
{
List<Array> ListOfLine = new List<Array>(); //所有的读取行集合
String[] myLine = new String[iCount]; //单行对象
RangeSelector[] rsContentA = new RangeSelector[iCount];
for (j = 0; j < iCount; j++)
{
rsContentA[j] = new RangeSelector(lbContent.Items[j].ToString());
}
j = 0;
foreach (RangeSelector cont in rsContentA)
{
cont.acc();
point = cont.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
j++;
if (j > iCount) break;//xxxxxxx
}
myArray[m, j - 1] = DataTag;
m++;
}
}
}
//关闭当前活页簿
myBook.Close();
System.Windows.Forms.Application.DoEvents();
}
myExcel.Quit();
}
public static string GetServiceDataFromFile(string servicesFilePath)
{
CultureInfo oldCi = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
_excelProcessesBeforeStartWork = Process.GetProcessesByName("EXCEL");
// Стартуем Excel-приложение
_oxl = new Application();
_oxb = _oxl.Workbooks.Open(servicesFilePath, 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);
try
{
_ows = (_Worksheet)_oxb.Sheets["Группировщик детальный"];
}
catch
{
throw new Exception("Лист с именем 'Группировщик детальный' не найден. Вероятно, указанный файл имеет неверный формат.");
}
int rowCnt = 2;
int shag = 10000;
while (shag > 0)
{
while (((Range)_ows.Cells[rowCnt, 1]).Value2 != null)
{
rowCnt += shag;
}
rowCnt -= shag;
shag /= 10;
}
var data = (object[, ])_ows.get_Range(_ows.Cells[2, 6], _ows.Cells[rowCnt, 14]).Value2;
var sb = new StringBuilder();
for (int i = 1; i < rowCnt; i++)
{
string service = data[i, 2]?.ToString();
if (string.IsNullOrEmpty(service))
{
continue;
}
sb.AppendFormat("{0};{1};{2};{3}^",
service,
data[i, 1],
data[i, 8],
data[i, 9]);
}
return(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("В процессе загрузки данных с услугами произошла ошибка:\r\n" + ex);
}
finally
{
QuitExcel();
Thread.CurrentThread.CurrentCulture = oldCi;
}
}
private void CreateExcel(DateTime date)
{
DateTime dtNow = DateTime.Now;
bool createdNew = false;
using (Mutex mutex = new Mutex(false, "Global\\CreateExcel", out createdNew))
{
var allowEveryoneRule = new MutexAccessRule(new SecurityIdentifier(WellKnownSidType.WorldSid, null), MutexRights.FullControl, AccessControlType.Allow);
var securitySettings = new MutexSecurity();
securitySettings.AddAccessRule(allowEveryoneRule);
mutex.SetAccessControl(securitySettings);
bool hasHandle = false;
try
{
try
{
hasHandle = mutex.WaitOne();
if (hasHandle == false)
throw new TimeoutException("Timeout waiting for exclusive access");
}
catch (AbandonedMutexException)
{ hasHandle = true; }
objExcelSummary = new Application();
objWorkbookSummary = objExcelSummary.Workbooks.Add(System.Reflection.Missing.Value);
string spacer =
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString() +
Convert.ToChar(32).ToString();
// DELETE EXTRA SHEETS
//((Worksheet)objWorkbookSummary.Sheets[3]).Delete();
//((Worksheet)objWorkbookSummary.Sheets[2]).Delete();
objSummary = ((Worksheet)objWorkbookSummary.Sheets[1]);
objSummary.Name = "CALLS";
objSummary.PageSetup.PrintGridlines = false;
objSummary.PageSetup.CenterHorizontally = true;
objSummary.PageSetup.CenterVertically = false;
objSummary.PageSetup.FitToPagesWide = 1;
objSummary.PageSetup.FitToPagesTall = false;
objSummary.PageSetup.Zoom = false;
//objSummary.PageSetup.Zoom = 100;
objSummary.PageSetup.TopMargin = objExcelSummary.Application.InchesToPoints(0.50);
objSummary.PageSetup.BottomMargin = objExcelSummary.Application.InchesToPoints(0.25);
objSummary.PageSetup.LeftMargin = objExcelSummary.Application.InchesToPoints(0.25);
objSummary.PageSetup.RightMargin = objExcelSummary.Application.InchesToPoints(0.25); //0.25
objSummary.PageSetup.Orientation = XlPageOrientation.xlLandscape;
objSummary.PageSetup.HeaderMargin = objExcelSummary.Application.InchesToPoints(0.00);
objSummary.PageSetup.FooterMargin = objExcelSummary.Application.InchesToPoints(0.00);
//objSummary.PageSetup.RightFooter = "Page &P of &N";
//objSummary.PageSetup.RightFooter = "Run date for " + date.ToString("MMM/dd");
objSummary.PageSetup.RightFooter = "Run-Date: " + dtNow.ToString("MMM") + " " + dtNow.Day + GetOrdinal(dtNow.Day) + ", " +
dtNow.Year + " for " + date.ToString("MMM") + " " + date.Day + GetOrdinal(date.Day) + ", " + date.Year + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(32) +
Convert.ToChar(13).ToString() + "Page &P of &N" + Convert.ToChar(32) + Convert.ToChar(32) + Convert.ToChar(32) +
Convert.ToChar(13).ToString();
}
finally
{
if (hasHandle)
mutex.ReleaseMutex();
}
}
}
public static void Export(List <PatientClass> patientList, DbEngine dbEngine)
{
CultureInfo oldCi = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
// Стартуем Excel-приложение
_oxl = new Application();
// Создаем новую книгу
_oxb = _oxl.Workbooks.Add(Missing.Value);
int len = _oxb.Sheets.Count;
for (int i = len; i > 1; i--)
{
((_Worksheet)_oxb.Sheets[i]).Delete();
}
_ows = (_Worksheet)_oxb.Sheets[1];
_ows.Cells.WrapText = true;
_ows.Cells.VerticalAlignment = 2;
_ows.Cells.HorizontalAlignment = 2;
_owr = _ows.get_Range("A1", "Q1");
_owr.MergeCells = true;
_owr.Font.Bold = true;
_owr.Font.Size = 14;
_owr.RowHeight = 30;
_owr.HorizontalAlignment = 3;
_ows.Cells[1, 1] = "Список пациентов на " + ConvertEngine.GetRightDateString(DateTime.Now);
_owr = _ows.get_Range("R1", "AB1");
_owr.MergeCells = true;
_owr.Font.Bold = true;
_owr.Font.Size = 14;
_owr.RowHeight = 30;
_owr.HorizontalAlignment = 3;
_ows.Cells[1, 18] = "Данные по операциям";
_owr = _ows.get_Range("A2", "A2");
_owr.ColumnWidth = 15;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "ФИО пациента";
_owr = _ows.get_Range("B2", "B2");
_owr.ColumnWidth = 4;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Возраст";
_owr = _ows.get_Range("C2", "C2");
_owr.ColumnWidth = 10;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Дата рождения";
_owr = _ows.get_Range("D2", "D2");
_owr.ColumnWidth = 14;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Адрес";
_owr = _ows.get_Range("E2", "E2");
_owr.ColumnWidth = 14;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Место работы";
_owr = _ows.get_Range("F2", "F2");
_owr.ColumnWidth = 9;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Телефон";
_owr = _ows.get_Range("G2", "G2");
_owr.ColumnWidth = 9;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Тип стационара";
_owr = _ows.get_Range("H2", "H2");
_owr.ColumnWidth = 9;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Код МКБ";
_owr = _ows.get_Range("I2", "I2");
_owr.ColumnWidth = 9;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Код услуги";
_owr = _ows.get_Range("J2", "J2");
_owr.ColumnWidth = 20;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Название услуги";
_owr = _ows.get_Range("K2", "K2");
_owr.ColumnWidth = 13;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Нозология";
_owr = _ows.get_Range("L2", "L2");
_owr.ColumnWidth = 10;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Лечащий врач";
_owr = _ows.get_Range("M2", "M2");
_owr.ColumnWidth = 9;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Дата поступления";
_owr = _ows.get_Range("N2", "N2");
_owr.ColumnWidth = 10;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Дата выписки";
_owr = _ows.get_Range("O2", "O2");
_owr.ColumnWidth = 7;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "№ истории болезни";
_owr = _ows.get_Range("P2", "P2");
_owr.ColumnWidth = 5;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Количество операций";
_owr = _ows.get_Range("Q2", "Q2");
_owr.ColumnWidth = 25;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Диагноз";
_owr = _ows.get_Range("R2", "R2");
_owr.ColumnWidth = 22;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Название операции";
_owr = _ows.get_Range("S2", "S2");
_owr.ColumnWidth = 10;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Дата операции";
_owr = _ows.get_Range("T2", "T2");
_owr.ColumnWidth = 6;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Время начала операции";
_owr = _ows.get_Range("U2", "U2");
_owr.ColumnWidth = 6;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Время окончания операции";
_owr = _ows.get_Range("V2", "V2");
_owr.ColumnWidth = 10;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Список хирургов";
_owr = _ows.get_Range("W2", "W2");
_owr.ColumnWidth = 12;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Список ассистентов";
_owr = _ows.get_Range("X2", "X2");
_owr.ColumnWidth = 8;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Анестезист";
_owr = _ows.get_Range("Y2", "Y2");
_owr.ColumnWidth = 8;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Анестезистка";
_owr = _ows.get_Range("Z2", "Z2");
_owr.ColumnWidth = 8;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Операц. мед. сестра";
_owr = _ows.get_Range("AA2", "AA2");
_owr.ColumnWidth = 8;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Санитар";
_owr = _ows.get_Range("AB2", "AB2");
_owr.ColumnWidth = 85;
_owr.Font.Bold = true;
_owr.HorizontalAlignment = 3;
_owr.Value2 = "Ход операции";
int rowCnt = 3;
for (int i = 0; i < patientList.Count; i++)
{
_ows.Cells[rowCnt, 1] = patientList[i].GetFullName();
_ows.Cells[rowCnt, 2] = patientList[i].Age;
_ows.Cells[rowCnt, 3] = ConvertEngine.GetRightDateString(patientList[i].Birthday, false);
_ows.Cells[rowCnt, 4] = patientList[i].GetAddress();
_ows.Cells[rowCnt, 5] = patientList[i].WorkPlace;
_ows.Cells[rowCnt, 6] = patientList[i].Phone;
_ows.Cells[rowCnt, 7] = patientList[i].TypeOfKSG;
_ows.Cells[rowCnt, 8] = patientList[i].MKB;
_ows.Cells[rowCnt, 9] = patientList[i].ServiceCode;
_ows.Cells[rowCnt, 10] = patientList[i].ServiceName;
_ows.Cells[rowCnt, 11] = patientList[i].Nosology;
_ows.Cells[rowCnt, 12] = patientList[i].DoctorInChargeOfTheCase;
_ows.Cells[rowCnt, 13] = ConvertEngine.GetRightDateString(patientList[i].DeliveryDate, true);
if (patientList[i].ReleaseDate.HasValue)
{
_ows.Cells[rowCnt, 14] = ConvertEngine.GetRightDateString(patientList[i].ReleaseDate.Value, true);
}
_ows.Cells[rowCnt, 15] = patientList[i].NumberOfCaseHistory;
_ows.Cells[rowCnt, 16] = patientList[i].Operations.Count.ToString();
_ows.Cells[rowCnt, 17] = patientList[i].Diagnose;
if (patientList[i].Operations.Count == 0)
{
rowCnt++;
}
else
{
foreach (OperationClass operationInfo in patientList[i].Operations)
{
_ows.Cells[rowCnt, 18] = operationInfo.Name;
_ows.Cells[rowCnt, 19] = ConvertEngine.GetRightDateString(operationInfo.DataOfOperation);
_ows.Cells[rowCnt, 20] = ConvertEngine.GetRightTimeString(operationInfo.StartTimeOfOperation);
_ows.Cells[rowCnt, 21] = ConvertEngine.GetRightTimeString(operationInfo.EndTimeOfOperation);
_ows.Cells[rowCnt, 22] = ListToString(operationInfo.Surgeons);
_ows.Cells[rowCnt, 23] = ListToString(operationInfo.Assistents);
_ows.Cells[rowCnt, 24] = operationInfo.HeAnaesthetist;
_ows.Cells[rowCnt, 25] = operationInfo.SheAnaesthetist;
_ows.Cells[rowCnt, 26] = operationInfo.ScrubNurse;
_ows.Cells[rowCnt, 27] = operationInfo.Orderly;
_ows.Cells[rowCnt, 28] = operationInfo.OperationCourse;
rowCnt++;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (_oxl != null)
{
_oxl.Visible = true;
_oxl.UserControl = true;
if (_oxb != null)
{
Marshal.ReleaseComObject(_oxb);
_oxb = null;
}
if (_ows != null)
{
Marshal.ReleaseComObject(_ows);
_ows = null;
}
if (_owr != null)
{
Marshal.ReleaseComObject(_owr);
_owr = null;
}
Marshal.ReleaseComObject(_oxl);
_oxl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
Thread.CurrentThread.CurrentCulture = oldCi;
}
}
private void GetLayerDataForLocalInWWT(_Worksheet workSheet)
{
if (workSheet != null && this.currentWorkbookMap.SelectedLayerMap != null)
{
Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress];
Range validationRange = currentLayerRange;
if (currentLayerRange != null)
{
object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false);
if (layerData != null && layerData.Length > 0)
{
int difference = 0;
Range rowDifferenceRange = null, columnDifferenceRange = null;
string rowaddress = string.Empty, colAddress = string.Empty;
// 1. Gets the difference from the current layer range
// 2. Gets the range with the first row cell
// 3. Inserts rows/columns to range with difference
if (currentLayerRange.Columns.Count < layerData.GetLength(1))
{
difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1));
// Take the column next to last column of the current range as first column getting inserted.
Range firstColumnCell = (Range)currentLayerRange.Cells[1, currentLayerRange.Columns.Count + 1];
columnDifferenceRange = workSheet.GetRange(firstColumnCell, currentLayerRange.GetRowsCount(), difference);
}
// The range provides a combination of the current range and column difference range,
// which is used for affected range and formula validation.
if (difference > 0)
{
validationRange = validationRange.Resize[Type.Missing, validationRange.Columns.Count + difference];
}
int rowsCount = currentLayerRange.GetRowsCount();
if (rowsCount < layerData.GetLength(0))
{
difference = GetRangeDifference(rowsCount, layerData.GetLength(0));
// Take the row next to last row of the current range as first row getting inserted.
Range firstRowCell = (Range)currentLayerRange.Cells[rowsCount + 1, 1];
rowDifferenceRange = workSheet.GetRange(firstRowCell, difference, currentLayerRange.Columns.Count);
}
// The range provides a combination of the current range and row difference range
// which is used for affected range and formula validation.
if (difference > 0)
{
validationRange = validationRange.Resize[validationRange.GetRowsCount() + difference, Type.Missing];
}
if (ValidateLocalInWWTLayerData(workSheet, validationRange))
{
Range rowRange = null, colRange = null;
if (columnDifferenceRange != null)
{
// Insert the columns which are needed to get the latest data.
colAddress = columnDifferenceRange.Address;
InsertColumns(columnDifferenceRange);
colRange = workSheet.Application.Range[colAddress];
}
if (rowDifferenceRange != null)
{
// Insert the rows which are needed to get the latest data.
rowaddress = rowDifferenceRange.Address;
InsertRows(rowDifferenceRange);
rowRange = workSheet.Application.Range[rowaddress];
}
bool createLayerForRange = false;
if (rowRange != null)
{
// Any new rows added, add them to the layer range.
createLayerForRange = true;
currentLayerRange = workSheet.Application.get_Range(rowRange, currentLayerRange);
}
if (colRange != null)
{
// Any new columns added, add them to the layer range.
createLayerForRange = true;
currentLayerRange = workSheet.Application.get_Range(colRange, currentLayerRange);
}
ThisAddIn.ExcelApplication.SheetChange -= new AppEvents_SheetChangeEventHandler(OnSheetChange);
currentLayerRange.Cells.Clear();
ThisAddIn.ExcelApplication.SheetChange += new AppEvents_SheetChangeEventHandler(OnSheetChange);
currentLayerRange.Select();
// Only in case if the range size increased, update the layer properties.
if (createLayerForRange)
{
CreateRangeForLayer(currentLayerRange);
}
else
{
// Since Range is not getting changed in case of no more rows/columns added to them, call this method to update the layer properties alone.
SetLayerRangeProperties(this.currentWorkbookMap.SelectedLayerMap.RangeName);
}
bool isResizeRangeRequired = false;
// In case if the rows are less, resize the range, instead paste only the rows having data and rest of rows will be left as empty.
// Additional empty rows cannot be deleted, since there could be data in the columns which are not part of range which will be lost.
if (currentLayerRange.GetRowsCount() > layerData.GetLength(0))
{
difference = GetRangeDifference(currentLayerRange.GetRowsCount(), layerData.GetLength(0));
currentLayerRange = currentLayerRange.Resize[currentLayerRange.GetRowsCount() - difference, Type.Missing];
isResizeRangeRequired = true;
}
// In case if the columns are less, resize the range, instead paste only the columns having data and rest of columns will be left as empty.
// Additional empty columns cannot be deleted, since there could be data in the rows which are not part of range which will be lost.
if (currentLayerRange.Columns.Count > layerData.GetLength(1))
{
difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1));
currentLayerRange = currentLayerRange.Resize[Type.Missing, currentLayerRange.Columns.Count - difference];
isResizeRangeRequired = true;
}
if (isResizeRangeRequired)
{
CreateRangeForLayer(currentLayerRange);
currentLayerRange.Select();
}
currentLayerRange.SetValue(layerData);
SetFormatForDateColumns(workSheet);
}
}
}
}
}
public static bool FillForms2()
{
try
{
//暂时支持40个点 11.14
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F2.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, 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);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
sheet = (_Worksheet)book.Worksheets.get_Item(1);
sheet.Activate();
int l1, l2, l3, l4, l5, l6;
l1 = _projectData[0].F2.LandPointCodeList.Length;
l2 = _projectData[0].F2.LandPointTypeList.Length;
//l3 = _projectData[0].F2.LandBoundaryExplain.Length;
l4 = _projectData[0].F2.LandBoundaryLocation.Length;
l5 = _projectData[0].F2.LandBoundaryType.Length;
l6 = _projectData[0].F2.LandPointDistance.Length;
if (l1 - 19 <= 0)
{
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < l4; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
}
else
{
return(false);
}
}
else if (l1 - 19 <= 19)
{
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(2);
sheet.Activate();
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√";
for (int n = 0; n < l1 - 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf");
}
else
{
return(false);
}
}
else
{
return(false);
} // 超过38个点暂时不支持 11.14
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception) { return(false); }
}
/// <summary>
/// Sets the Date format for the columns which are mapped to StartDate and EndDate.
/// </summary>
/// <param name="workSheet">Worksheet object</param>
private void SetFormatForDateColumns(_Worksheet workSheet)
{
Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress];
Range firstCell = null;
if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn != Common.Constants.DefaultColumnIndex)
{
foreach (Range area in currentLayerRange.Areas)
{
firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn + 1] as Range;
Range startDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1);
startDateColumn.NumberFormat = "m/d/yyyy h:mm";
}
}
if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn != Common.Constants.DefaultColumnIndex)
{
foreach (Range area in currentLayerRange.Areas)
{
firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn + 1] as Range;
Range endDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1);
endDateColumn.NumberFormat = "m/d/yyyy h:mm";
}
}
}
public static bool FillForms3()
{
try
{
//暂时支持40条界址线 11.14
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F3.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, 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);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
sheet = (_Worksheet)book.Worksheets.get_Item(1);
sheet.Activate();
int l1, l2, l3;
l1 = _projectData[0].F3.StartPointCodeList.Length;
l2 = _projectData[0].F3.InnerPointCodeList.Length;
l3 = _projectData[0].F3.EndPointCodeList.Length;
if (l1 == l2 && l2 == l3)
{
if (l1 - 21 <= 0)
{
for (int n = 0; n < l1; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
}
else if (l1 - 21 <= 21)
{
for (int n = 0; n < 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(2);
sheet.Activate();
for (int n = 0; n < l1 - 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf");
}
else
{
return(false);
} // 超过42条线暂时不支持 11.14
}
else
{
return(false);
}
}
catch (Exception) { return(false); }
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
/// <summary>
/// Validates if the range is affecting any of the existing range of layers
/// and checks if the range has formula
/// </summary>
/// <param name="workSheet">Current active worksheet</param>
/// <param name="range">Current range</param>
/// <returns>Returns true if the layer is valid</returns>
private bool ValidateLocalInWWTLayerData(_Worksheet workSheet, Range range)
{
bool isValid = false;
if (ValidateAffectedMappedRange(workSheet, range))
{
if (range.ValidateFormula())
{
isValid = Ribbon.ShowWarningWithResult(Properties.Resources.RangeHasFormulaWarning);
}
else
{
isValid = true;
}
}
return isValid;
}
/// <summary>
/// 效率慢
/// </summary>
/// <returns></returns>
public static bool FillForms()
{
try
{
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Form.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Form.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, 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);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
for (int formNum = 1; formNum < 3; formNum++)
{
sheet = (_Worksheet)book.Worksheets.get_Item(formNum);
sheet.Activate();
if (formNum == 1)
{
sheet.Cells[2, 9] = _projectData[0].F1.TableID;
sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode;
sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization;
sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf");
}
else if (formNum == 2)
{
sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行
sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType;
sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType;
sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode;
sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress;
sheet.Cells[7, 3] = _projectData[0].F1.PowerType;
sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter;
sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper;
sheet.Cells[8, 3] = _projectData[0].F1.Location;
sheet.Cells[9, 3] = _projectData[0].F1.PrincipalCertificateCode;
sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType;
sheet.Cells[10, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone;
sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName;
sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType;
sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone;
sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern;
sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode;
sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber;
sheet.Cells[18, 5] = _projectData[0].F1.MapScale;
sheet.Cells[19, 5] = _projectData[0].F1.MapCode;
sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth;
sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast;
sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth;
sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest;
sheet.Cells[24, 3] = _projectData[0].F1.Rank;
sheet.Cells[24, 9] = _projectData[0].F1.Price;
sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor;
sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode;
sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor;
sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode;
sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea;
sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea;
sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea;
sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea;
string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime;
sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止
sheet.Cells[31, 3] = _projectData[0].F1.CommonUse;
//sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf");
}
}
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception) { return(false); }
}
public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, 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));
for (int i = 0; i < tableName.Length; i++)
{
if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog")
{
_IniRow = 2;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
else
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate);
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
private void PutLessonData(String data, _Worksheet worksheet, int currentRow, ref int currentColumn)
{
currentColumn++;
worksheet.Cells[currentRow, currentColumn] = data;
}
//获取Sheet Name
public string GetFirstSheetName(string filePath)
{
string sheetName = string.Empty;
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(filePath, 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));
_ExcelWSheet = (Worksheet)_ExcelWBook.Sheets[1];
sheetName = _ExcelWSheet.Name;
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
return sheetName;
}
catch (Exception)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
return sheetName;
}
}
public void createSummaryStatistics(List <Variable> variables)
{
if (variables.Count == 0)
{
return;
}
_Worksheet worksheet = WorksheetHelper.NewWorksheet("One-Variable Summary");
int column = 1;
int row = 2;
if (model.mean)
{
worksheet.Cells[row++, column] = "Mean";
}
if (model.variance)
{
worksheet.Cells[row++, column] = "Variance";
}
if (model.standardDeviation)
{
worksheet.Cells[row++, column] = "Standard Deviation";
}
if (model.skewness)
{
worksheet.Cells[row++, column] = "Skewness";
}
if (model.kurtosis)
{
worksheet.Cells[row++, column] = "Kurtosis";
}
if (model.median)
{
worksheet.Cells[row++, column] = "Median";
}
if (model.meanAbsDeviation)
{
worksheet.Cells[row++, column] = "Mean Abs. Deviation";
}
if (model.mode)
{
worksheet.Cells[row++, column] = "Mode";
}
if (model.minimum)
{
worksheet.Cells[row++, column] = "Minimum";
}
if (model.maximum)
{
worksheet.Cells[row++, column] = "Maximum";
}
if (model.range)
{
worksheet.Cells[row++, column] = "Range";
}
if (model.count)
{
worksheet.Cells[row++, column] = "Count";
}
if (model.sum)
{
worksheet.Cells[row++, column] = "Sum";
}
if (model.firstQuartile)
{
worksheet.Cells[row++, column] = "First Quartile";
}
if (model.thirdQuartile)
{
worksheet.Cells[row++, column] = "Third Quartile";
}
if (model.interquartileRange)
{
worksheet.Cells[row++, column] = "Interquartile Range";
}
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column = 2;
foreach (Variable variable in variables)
{
worksheet.Cells[row++, column] = variable.name;
var range = variable.getRange().Address(true, true, true);
if (model.mean)
{
worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")";
}
if (model.variance)
{
worksheet.Cells[row++, column] = "=VAR.S(" + range + ")";
}
if (model.standardDeviation)
{
worksheet.Cells[row++, column] = "=STDEV.S(" + range + ")";
}
if (model.skewness)
{
worksheet.Cells[row++, column] = "=SKEW(" + range + ")";
}
if (model.kurtosis)
{
worksheet.Cells[row++, column] = "=KURT(" + range + ")";
}
if (model.median)
{
worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")";
}
if (model.meanAbsDeviation)
{
worksheet.Cells[row++, column] = "=AVEDEV(" + range + ")";
}
if (model.mode)
{
worksheet.Cells[row++, column] = "=MODE.SNGL(" + range + ")";
}
if (model.minimum)
{
worksheet.Cells[row++, column] = "=MIN(" + range + ")";
}
if (model.maximum)
{
worksheet.Cells[row++, column] = "=MAX(" + range + ")";
}
if (model.range)
{
worksheet.Cells[row++, column] = "=MAX(" + range + ") - MIN(" + range + ")";
}
if (model.count)
{
worksheet.Cells[row++, column] = "=COUNT(" + range + ")";
}
if (model.sum)
{
worksheet.Cells[row++, column] = "=SUM(" + range + ")";
}
if (model.firstQuartile)
{
worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",1)";
}
if (model.thirdQuartile)
{
worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",3)";
}
if (model.interquartileRange)
{
worksheet.Cells[row++, column] = "=QUARTILE.INC(" + range + ",3) - QUARTILE.INC(" + range + ",1)";
}
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column++;
}
}
private void LoadExcelData(Dictionary <string, Region> regions, Dictionary <string, Division> divisions)
{
string currentCell = String.Empty;
string filePath = "C:\\Users\\nsinotte\\Desktop\\Personal Projects\\SkyLine\\skyline-challenges\\SkylineChallenges-CSharp\\RestAPIProcessing\\US Census Bureau Regions and Divisions.xlsx";
Application file = new Application();
Workbook book = file.Workbooks.Open(filePath);
_Worksheet sheet = book.Sheets[1];
Range range = sheet.UsedRange;
for (int i = 3; i < 9; ++i)
{
currentCell = range[i, 3].Value2.ToString().Trim();
divisions.Add(currentCell, Division.NewEngland);
regions.Add(currentCell, Region.Northeast);
}
for (int i = 10; i < 13; ++i)
{
currentCell = range[i, 3].Value2.ToString().Trim();
divisions.Add(currentCell, Division.MidAtlantic);
regions.Add(currentCell, Region.Northeast);
}
for (int i = 15; i < 20; ++i)
{
currentCell = range[i, 3].Value2.ToString().Trim();
divisions.Add(currentCell, Division.EastNorthCentral);
regions.Add(currentCell, Region.Midwest);
}
for (int i = 21; i < 28; ++i)
{
currentCell = range[i, 3].Value2.ToString().Trim();
divisions.Add(currentCell, Division.WestNorthCentral);
regions.Add(currentCell, Region.Midwest);
}
for (int i = 3; i < 12; ++i)
{
currentCell = range[i, 7].Value2.ToString().Trim();
divisions.Add(currentCell, Division.SouthAtlantic);
regions.Add(currentCell, Region.South);
}
for (int i = 13; i < 17; ++i)
{
currentCell = range[i, 7].Value2.ToString().Trim();
divisions.Add(currentCell, Division.EastSouthCentral);
regions.Add(currentCell, Region.South);
}
for (int i = 18; i < 22; ++i)
{
currentCell = range[i, 7].Value2.ToString().Trim();
divisions.Add(currentCell, Division.WestSouthCentral);
regions.Add(currentCell, Region.South);
}
for (int i = 24; i < 32; ++i)
{
currentCell = range[i, 7].Value2.ToString().Trim();
divisions.Add(currentCell, Division.Mountain);
regions.Add(currentCell, Region.West);
}
for (int i = 33; i < 37; ++i)
{
currentCell = range[i, 7].Value2.ToString().Trim();
divisions.Add(currentCell, Division.Pacific);
regions.Add(currentCell, Region.West);
}
}
private void WriteToExcel(string filePath)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
_Workbook xlWorkbook = xlApp.Workbooks.Add(Type.Missing);
//report
_Worksheet xlReportSheet = xlWorkbook.Sheets[1];
xlReportSheet.Name = "Report";
int row = 1;
xlReportSheet.Cells[row++, 1] = $"Error Count: {_errorModels.Count}";
xlReportSheet.Cells[row, 1] = "File Name";
xlReportSheet.Cells[row, 2] = "Error Message";
row++;
foreach (ErrorModel error in _errorModels)
{
xlReportSheet.Cells[row, 1] = error.FileName;
xlReportSheet.Cells[row, 2] = error.ErrorMessage;
row++;
}
//data
var xlSheets = xlWorkbook.Sheets as Sheets;
_Worksheet xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlWorksheet.Name = "Data";
xlWorksheet.Cells[_lastRow, 1] = "Company";
xlWorksheet.Cells[_lastRow, 3] = "File Name";
xlWorksheet.Cells[_lastRow, 2] = "Amendment Files";
xlWorksheet.Cells[_lastRow, 4] = "Paragraph";
_lastRow++;
foreach (KeyValuePair <string, CompanyModel> entry in _companyModels)
{
xlWorksheet.Cells[_lastRow, 1] = entry.Key;
//append files
int paraRow = _lastRow;
foreach (FileModel file in entry.Value.FileModels)
{
xlWorksheet.Cells[paraRow, 3] = file.FileName;
foreach (string para in file.Paragraphs)
{
xlWorksheet.Cells[paraRow++, 4] = para;
}
}
//append amendments
int amendRow = _lastRow;
foreach (string amendment in entry.Value.AmendmentFileNames)
{
xlWorksheet.Cells[amendRow++, 2] = amendment;
}
_lastRow = Math.Max(paraRow, amendRow);
_lastRow++;
}
xlWorkbook.SaveAs(filePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkbook.Close();
xlApp.Quit();
}
public void Save(List<Week> weeks, _Worksheet worksheet)
{
// тут можно че-то добавить
_sealedExcelWriter.Save(weeks,worksheet);
}
public static void printLable(int id)
{
String sql = "select * from 标签 where ID={0}";
System.Data.DataTable dt = new System.Data.DataTable();
if (!mySystem.Parameter.isSqlOk)
{
OleDbDataAdapter da = new OleDbDataAdapter(String.Format(sql, id), mySystem.Parameter.connOle);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Fill(dt);
}
else
{
SqlDataAdapter da = new SqlDataAdapter(String.Format(sql, id), mySystem.Parameter.conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Fill(dt);
}
if (dt.Rows.Count == 0)
{
MessageBox.Show("无法找到标签信息");
return;
}
DataRow dr = dt.Rows[0];
string path = Directory.GetCurrentDirectory();
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = null;
wb = oXL.Workbooks.Open(path + @"/../../xls/cleancut/7 标签-清洁分切.xlsx");
_Worksheet my = wb.Worksheets[wb.Worksheets.Count];
//dr["生产指令"] = mySystem.Parameter.cleancutInstruction;
//dr["生产指令ID"] = mySystem.Parameter.cleancutInstruID;
//dr["膜代码"] = cb膜代码.Text;
//dr["批号-卷号 "] = tb批号.Text;
//dr["合格数量米"] = tb米.Text;
//dr["合格数量千克"] = tbKg.Text;
//dr["原膜代码"] = cb原膜代码.Text;
//dr["分切日期"] = dtp分切日期.Value.ToString("yyyy/MM/dd");
//dr["分切班次"] = cb白班.Checked ? "白班" : "夜班";
my.Select();
my.Cells[2, 2].Value = dr["膜代码"];
my.Cells[3, 2].Value = dr["批号_卷号"];
my.Cells[4, 2].Value = dr["合格数量米"] + "米； " + dr["合格数量千克"] + "Kg";
my.Cells[5, 2].Value = dr["原膜代码"];
my.Cells[6, 2].Value = String.Format("{0} {1}", Convert.ToDateTime(dr["分切日期"]).ToString("yyyy/MM/dd"),
dr["分切班次"].ToString() == "白班" ? "白班☑ 夜班□" : "白班□ 夜班☑");
my = wb.Worksheets[1];
my.Select();
oXL.Visible = false;
my.PrintOut();
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
public static MsExcel._Workbook Shashade(MsExcel.Application appExcel)
{
_Workbook ExcelBooks = null;
appExcel.Visible = false;
ExcelBooks = appExcel.Workbooks.Add();
string Sheet_Name = "站点数据";
_Worksheet ExcelSheets = TU.Add_Sheets(ExcelBooks, Sheet_Name);//添加一个sheet
ExcelSheets.Name = Sheet_Name;
System.Data.DataTable dalaopo = new System.Data.DataTable("dalaopo");
dalaopo.Columns.Add("站点", typeof(double)); //
dalaopo.Columns.Add("降水数据", typeof(double));
System.Data.DataTable jieguoTB = new System.Data.DataTable();
double[] n = new double[] { 129, 135, 128, 132, 99, 113, 157, 81, 70, 111, 78, 138, 103, 88, 110, 91 };
double[] T = new double[] { 5, 10, 20, 50 };
for (int i = 0; i < n.Length; i++)
{
List <double> gailvL = new List <double>();
for (int j = 0; j < T.Length; j++)
{
double gailv = 1 - 50 / (n[i] * T[j]);
gailvL.Add(gailv);
}
double[] gailvd = gailvL.ToArray();
string[] gailvS = MYTUW.TUW.doublearrTOstring(gailvd);
System.Data.DataTable jieguo = MYTUW.TUW.ArToDT1(gailvS);
jieguoTB.Merge(jieguo);
}
#region
/* for (int i = 1; i < RawDataStr_A.Length; i++)
* {
* char[] seperators = { ',' };
* string[] R_str1 = TU.ParseStringTo_Array<string>(RawDataStr_A[i], seperators);
*
* double zhandian = Convert.ToDouble(R_str1[0]);
* string jiangshuistr = Convert.ToString(R_str1[R_str1.Length -1]);
* double jiangshui= Convert.ToDouble(R_str1[R_str1.Length - 1]);
* if (Convert .ToDouble ( jiangshuistr) > 30000)
* {
* string housanwei = Convert.ToString(R_str1[R_str1.Length - 1]).Substring(Convert.ToString(R_str1[R_str1.Length - 1]).Length - 3);
* // Console.WriteLine(housanwei);
* if (Convert .ToDouble (housanwei)>500&& Convert.ToDouble(housanwei)!=700)
* {
* jiangshui = Convert.ToDouble(housanwei);
* }
* else
* {
* jiangshui = 1;
* }
*
* }
* dalaopo.Rows.Add(zhandian, jiangshui * 0.1);
*
*
* }
* var groupNew = from row in dalaopo.AsEnumerable()
* group row by new
* {
* zhandian = (row.Field<double>("站点"))
* }
* into groupRes
* orderby groupRes.Key.zhandian
* select groupRes;
* //把分完组的每个表格放入DS中
* DataSet RTB_Grp = new DataSet();
* foreach (var eachGroup in groupNew)
* {
* System.Data.DataTable TBMid1 = eachGroup.CopyToDataTable();
*
* RTB_Grp.Tables.Add(TBMid1);
* }
* //对每天的数据进行处理并放入 Traffic_TB中RTB_Grp.Tables .Count
* for (int i=0;i< RTB_Grp.Tables.Count; i++)
* {
* System.Data.DataTable meigezhandian = RTB_Grp.Tables[i];
* List<double> baoyurishu = new List<double>();
* List<double> quannianyuliang = new List<double>();
* for (int j=0;j < meigezhandian.Rows.Count;j++)
* {
* double yuliang = Convert.ToDouble(meigezhandian.Rows[j][1]);
* quannianyuliang.Add(yuliang);
* if (yuliang>50)
* {
* baoyurishu.Add(yuliang);
* }
* }
* double baoyutianshu = baoyurishu.Count;
* double baoyuliang = baoyurishu.Sum();
* double baoyuqiangdu = baoyuliang / baoyutianshu;
* double zongyuliang = quannianyuliang.Sum();
* double baoyugongxianlv = baoyuliang / zongyuliang;
* jieguoTB.Rows.Add(Convert.ToDouble(meigezhandian.Rows[0][0]), baoyutianshu,
* baoyuliang, baoyuqiangdu, baoyugongxianlv);
* }
*/
#endregion
MYTUW.TUW.DTToExcel(jieguoTB, ExcelSheets, 2, 1);
MYTUW.TUW.Delete_sheet(appExcel, ExcelBooks);
return(ExcelBooks);
}
protected void WriteCell(_Worksheet excelSheet, int curentCellRowIndex, int currentCellColIndex, object value)
{
excelSheet.Cells[curentCellRowIndex, currentCellColIndex] = value;
}
public static bool FillForm()
{
try
{
projname = _importProjectName;
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Forms.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Forms.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, 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);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
//填写完Form1
for (int formNum = 1; formNum < 3; formNum++)
{
sheet = (_Worksheet)book.Worksheets.get_Item(formNum);
sheet.Activate();
if (formNum == 1)
{
sheet.Cells[2, 9] = _projectData[0].F1.TableID;
sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode;
sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization;
sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf");
}
else if (formNum == 2)
{
sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行
sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType;
sheet.Cells[9, 3] = _projectData[0].F1.PrincipalName;
sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType;
sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode;
sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress;
sheet.Cells[7, 3] = _projectData[0].F1.PowerType;
sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter;
sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper;
sheet.Cells[8, 3] = _projectData[0].F1.Location;
sheet.Cells[10, 6] = _projectData[0].F1.PrincipalCertificateCode;
sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType;
sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone;
sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName;
sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType;
sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone;
sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern;
sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode;
sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber;
sheet.Cells[18, 5] = _projectData[0].F1.MapScale;
sheet.Cells[19, 5] = _projectData[0].F1.MapCode;
sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth;
sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast;
sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth;
sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest;
sheet.Cells[24, 3] = _projectData[0].F1.Rank;
sheet.Cells[24, 9] = _projectData[0].F1.Price;
sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor;
sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode;
sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor;
sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode;
sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea;
sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea;
sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea;
sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea;
string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime;
sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止
sheet.Cells[31, 3] = _projectData[0].F1.CommonUse;
//sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf");
}
}
//填写Form2
sheet = (_Worksheet)book.Worksheets.get_Item(3);
sheet.Activate();
int l1, l2, l3, l4, l5, l6;
l1 = _projectData[0].F2.LandPointCodeList.Length;
l2 = _projectData[0].F2.LandPointTypeList.Length;
//l3 = _projectData[0].F2.LandBoundaryExplain.Length;
l4 = _projectData[0].F2.LandBoundaryLocation.Length;
l5 = _projectData[0].F2.LandBoundaryType.Length;
l6 = _projectData[0].F2.LandPointDistance.Length;
_Form2Number = l1 / 19;
if (_Form2Number == 0 || l1 == 19)
{
_Form2Number = 1;
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
if (_projectData[0].F2.LandPointTypeList[0] == -1)
{
}
else
{
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
}
for (int n = 0; n < l4; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
if (_projectData[0].F2.LandPointTypeList[n + 1] == -1)
{
}
else
{
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
}
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
if (_projectData[0].F2.LandBoundaryType[n] == -1)
{
}
else
{
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
}
if (_projectData[0].F2.LandBoundaryLocation[n] == -1)
{
}
else
{
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
}
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
}
else
{
return(false);
}
}
else if (_Form2Number == 1 && l1 > 19)
{
_Form2Number = 2;
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(4);
sheet.Activate();
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√";
for (int n = 0; n < l1 - 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf");
}
else
{
return(false);
}
}
else
{
return(false);
} // 超过38个点暂时不支持 11.14
//填写Form3
sheet = (_Worksheet)book.Worksheets.get_Item(5);
sheet.Activate();
l1 = _projectData[0].F3.StartPointCodeList.Length;
l2 = _projectData[0].F3.InnerPointCodeList.Length;
l3 = _projectData[0].F3.EndPointCodeList.Length;
_Form3Number = l1 / 21;
if (_Form3Number == 0 || l1 == 21)
{
_Form3Number = 1;
for (int n = 0; n < l1; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
}
else if (_Form3Number == 1 && l1 > 21)
{
_Form3Number = 2;
for (int n = 0; n < 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(6);
sheet.Activate();
for (int n = 0; n < l1 - 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf");
}
else
{
return(false);
} // 超过42条线暂时不支持 11.14
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception e)
{
var s = e.ToString();
return(false);
}
}
private void Proceso( )
{
string country = "";
string city = "";
int i = 2;
Process.GetCurrentProcess().PriorityBoostEnabled = true; // Aftësimi i përforcimit të prioritetit të procesit aktual
Process.GetCurrentProcess().PriorityClass = ProcessPriorityClass.RealTime; // Caltimi i prioritetit të procesit aktual në RealTime
// Of course this only affects the main thread rather than child threads.
Thread.CurrentThread.Priority = ThreadPriority.Highest;
if (File.Exists(excelFile)) // kontrollim nëse skedari ekziston, masë parandaluese
// në rast se rastësisht skedari fshihet ose disku ose usb memoria dëmtohet ose largohet.
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
//Krimi i objekteve COM. Krijon një objekt COM për çdo gjë që referencohet
xlWorkbook = xlApp.Workbooks.Open(excelFile);
xlWorksheet = xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
rowCount = xlRange.Rows.Count;
setTotalCount();
city = "";
//qarkullimi nëpër rreshta dhe qeliza për lexim dhe shkrim të vlerës sonë të fituar
//excel nuk është i bazuar nga zero!! i fillon nga 2 që të anashkalohet titulli
for (i = 2; i <= rowCount - 2; i++)
{
var cell1 = xlRange.Cells[i, 1]; // nxjerrja e të dhënës në formë objekti COM në rreshtin i dhe shtyllën 1
city = cell1.Value2.ToString(); // konvertimi i formës COM në string
if (city.Length > 0) // Nëse Emërtim i qytetit mungon atëherë anashkalohet
{
try
{
country = LookForCountry(city); // Kërkon për emrin e shtetit
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
break;
}
if (country.Length > 0)
{
processed++;
unprocessed = rowCount - processed;
percentage = (processed / rowCount);
}
setProcessed();
setUnprocessed();
setPercentage();
xlRange.Cells[i, 2] = country; // Nëse gjendet e vëmë emrin e shtetit në qelizën e dytë të rreshtit i.
}
}
}
Process.GetCurrentProcess().PriorityClass = ProcessPriorityClass.Normal; // Pas kryerjes së punës e kthejmë
//vlerën e prioritet në prioritet normal
// Of course this only affects the main thread rather than child threads.
Thread.CurrentThread.Priority = ThreadPriority.Normal;
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // marrja e shtegut të Desktop.
xlWorkbook.SaveAs(Path.Combine(path, "Exported_" + naked + ".xlsx")); // ruajtja e materialit të përpunuar në emër të ri
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//mbyllja dhe lirimi i objetkit
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//mbyllja dhe lirimi i objektit
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
