private static void WriteTransfer(List <VerifyView> list, ISheet sheet) { var serial = 1; var line = 1; var modelRow = sheet.GetRow(serial); ExcelClass.GetCell(sheet.GetRow(0), 1).SetCellValue("流水编号"); foreach (var item in list) { var row = sheet.GetRow(line) ?? sheet.CreateRow(line); line++; row.Height = modelRow.Height; var cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(serial++); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(item.PrintNumber); ExcelClass.GetCell(row, 2, modelRow).SetCellValue(item.SheetName); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(item.SheetTime.ToLongDateString()); ExcelClass.GetCell(row, 4, modelRow).SetCellValue(item.Money); ExcelClass.GetCell(row, 5, modelRow).SetCellValue(item.Count); ExcelClass.GetCell(row, 6, modelRow).SetCellValue(item.Remarks); ExcelClass.GetCell(row, 7, modelRow).SetCellValue(item.subName + "-" + item.sName); ExcelClass.GetCell(row, 8, modelRow).SetCellValue(item.Details); ExcelClass.GetCell(row, 9, modelRow).SetCellValue(item.Price.HasValue?item.Price.Value:.0); } }
public static IWorkbook SaveSheets(Dictionary <string, List <SheetView> > dict) { IWorkbook workbook = ExcelClass.GetAbsolutePath(System.Configuration.ConfigurationManager.AppSettings["REPORT"].ToString()).OpenExcel(); if (workbook != null) { var sheet = workbook.GetSheetAt(0); if (sheet != null) { var row = sheet.GetRow(3); if (row != null) { var modelrow = row; var serial = 1; var rowNumber = 3; foreach (var entry in dict) { if (entry.Value.First().Type == SheetType.Errand) { } } } } } return(workbook); }
public void WriteAllDeals() { using (ExcelClass xls = new ExcelClass()) { try { xls.OpenDocument(_quotesFileName, false); int i = FirstRow; foreach (KeyValuePair <DateTime, Deal> pair in _deals) { string sDate = xls.GetCellStringValue(DateCol, i); while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date.AddDays(-1) == pair.Key) { SetDealStops (xls, ref i, pair.Value, NewDirectionDealCol, NewOpenDealCol, NewReverseDealCol, NewProfitLostCol); break; } i++; sDate = xls.GetCellStringValue(DateCol, i); } } } finally { xls.CloseDocumentSave(); } } }
public void ReadQuotes(DateTime fromDate, DateTime toDate) { using (ExcelClass xls = new ExcelClass()) { try { _quotes = new Dictionary <DateTime, Quote>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date > fromDate && date < toDate) { decimal open = StringFunctions.ParseDecimal(xls.GetCellStringValue(OpenCol, i)); decimal close = StringFunctions.ParseDecimal(xls.GetCellStringValue(CloseCol, i)); decimal high = StringFunctions.ParseDecimal(xls.GetCellStringValue(HighCol, i)); decimal low = StringFunctions.ParseDecimal(xls.GetCellStringValue(LowCol, i)); ulong volume = ulong.Parse(xls.GetCellStringValue(VolumeCol, i)); Quote quote = new Quote(date, open, close, high, low, volume, i); _quotes.Add(date, quote); } i++; sDate = xls.GetCellStringValue(DateCol, i); } } finally { xls.CloseDocument(false); } } }
private void Export_Library_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Файлы excel|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { List <BookClass> listBook = DbModel.init().Books.Include(b => b.publisher).Include(b => b.type).Include(b => b.Authors).ToList(); string[,] values = new string[listBook.Count + 1, 6]; values[0, 0] = "Тип"; values[0, 1] = "Класс"; values[0, 2] = "Название"; values[0, 3] = "Автор"; values[0, 4] = "Издательство"; values[0, 5] = "Год публикации"; { for (int i = 0; i < listBook.Count; i++) { { values[i + 1, 0] = listBook[i].type.type; values[i + 1, 1] = listBook[i].classnum.ToString(); values[i + 1, 2] = listBook[i].name_book; values[i + 1, 3] = listBook[i].Authors.ToString(); values[i + 1, 4] = listBook[i].publisher.ToString(); values[i + 1, 5] = listBook[i].publishing_year.ToString(); } } } ExcelClass.saveExcel(dialog.FileName, "Книги", values); } }
private void Export_Trainess_Click_1(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Файлы excel|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { List <TrainessClass> listTrainesses = DbModel.init().Trainesses.ToList(); string[,] values = new string[listTrainesses.Count + 1, 5]; values[0, 0] = "Класс"; values[0, 1] = "Фамилия"; values[0, 2] = "Имя"; values[0, 3] = "Отчество"; for (int i = 0; i < listTrainesses.Count; i++) { values[i + 1, 0] = listTrainesses[i].classTrainess.ToString(); values[i + 1, 1] = listTrainesses[i].family_name; values[i + 1, 2] = listTrainesses[i].first_name; values[i + 1, 3] = listTrainesses[i].middle_name; } ExcelClass.saveExcel(dialog.FileName, "Обучающиеся", values); } }
private void Export_Issuance_Click(object sender, EventArgs e) { SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Файлы excel|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { List <IssuanceClass> listIssuance = DbModel.init().Issuances.Include(i => i.trainess).Include(i => i.literature).Include(i => i.literature.book).ToList(); string[,] values = new string[listIssuance.Count + 1, 6]; values[0, 0] = "Класс"; values[0, 1] = "Фамилия"; values[0, 2] = "Имя"; values[0, 3] = "Издание"; values[0, 4] = "Номер"; values[0, 5] = "Дата выдачи"; { for (int i = 0; i < listIssuance.Count; i++) { { values[i + 1, 0] = listIssuance[i].trainess.classTrainess.ToString(); values[i + 1, 1] = listIssuance[i].trainess.family_name; values[i + 1, 2] = listIssuance[i].trainess.first_name; values[i + 1, 3] = listIssuance[i].literature.book.name_book; values[i + 1, 4] = listIssuance[i].literature.InventiryNum; values[i + 1, 5] = listIssuance[i].date_of_issue.ToString("F"); } } } ExcelClass.saveExcel(dialog.FileName, "Выдача", values); } }
/// <summary> /// 作用:生成评估公司汇总表 /// 作者:汪建龙 /// 编写时间:2017年1月17日14:29:22 /// </summary> /// <param name="head">表格头信息</param> /// <param name="ReportTypes"></param> /// <param name="list"></param> /// <param name="sheet"></param> public static void WriteCompany(string head, List <ReportType> ReportTypes, List <BillRecordView> list, ISheet sheet) { if (list == null || sheet == null) { return; } var line = 0; IRow row = sheet.GetRow(line); if (row == null) { return; } ICell cell = row.GetCell(0); if (cell == null) { cell = row.CreateCell(0); } cell.SetCellValue(head); line = 4; IRow modelRow = sheet.GetRow(line); var inComes = list.Where(e => e.Budget == Budget.Income).OrderBy(e => e.SerialNumber).ToList(); foreach (var item in inComes) { row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } line++; cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(item.Summary); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(item.Money); ExcelClass.GetCell(row, 2, modelRow).SetCellValue(item.Time.HasValue ? item.Time.Value.ToString("yyyy-MM-dd") : "未获取时间信息"); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(string.Format("{0}({1})", item.Remark, item.Remark2)); } line = 4; var expenses = list.Where(e => e.Budget == Budget.Expense).OrderBy(e => e.SerialNumber).ToList(); List <BillRecordView> temp = null; foreach (var item in ReportTypes) { row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } line++; cell = ExcelClass.GetCell(row, 4, modelRow); cell.SetCellValue(item.Name); temp = expenses.Where(e => !string.IsNullOrEmpty(e.TName) && e.TName.ToLower() == item.Name.ToLower()).ToList(); ExcelClass.GetCell(row, 5, modelRow).SetCellValue(temp.Sum(e => e.Money)); ExcelClass.GetCell(row, 6, modelRow).SetCellValue(string.Join(";", temp.Select(e => string.Format("{0}({1})", e.Remark, e.Remark2)).ToArray())); } }
private void ReadDeals(ref Dictionary <DateTime, Deal> someDeals, DateTime fromDate, DateTime toDate, string directionDealCol, string openDealCol, string reverseDealCol) { using (ExcelClass xls = new ExcelClass()) { try { someDeals = new Dictionary <DateTime, Deal>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; Deal deal = null; bool isFirstDeal = true; while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date > fromDate && date < toDate) { string sDir = xls.GetCellStringValue(directionDealCol, i); string sOpen = xls.GetCellStringValue(openDealCol, i); string sReverse = xls.GetCellStringValue(reverseDealCol, i); if (!string.IsNullOrEmpty(sDir) && !string.IsNullOrEmpty(sOpen) && !string.IsNullOrEmpty(sReverse)) { decimal?reverse = StringFunctions.TryParseDecimal(sReverse); if (isFirstDeal) { decimal?open = StringFunctions.TryParseDecimal(sOpen); deal = new Deal(sDir, date.AddDays(-1), open); deal.SetStopReverse(date, reverse); isFirstDeal = false; } else { if (deal.IsSameDirection(sDir)) { deal.SetStopReverse(date, reverse); } else { decimal?open = StringFunctions.TryParseDecimal(sOpen); someDeals.Add(deal.OpenDate, deal); deal = deal.Reverse(date.AddDays(-1), open); deal.SetStopReverse(date, reverse); } } } } i++; sDate = xls.GetCellStringValue(DateCol, i); } } finally { xls.CloseDocument(false); } } }
/// <summary> /// 下载银行明细 /// </summary> /// <param name="dict"></param> /// <param name="title"></param> /// <returns></returns> public static IWorkbook DownloadBank(Dictionary <string, Dictionary <string, double> > dict, string title) { IWorkbook workbook = ExcelClass.GetAbsolutePath(System.Configuration.ConfigurationManager.AppSettings["BBank"].ToString()).OpenExcel(); if (workbook != null) { var sheet = workbook.GetSheetAt(0); for (var i = 0; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); if (row != null) { for (var j = 0; j <= row.LastCellNum; j++) { var cell = row.GetCell(j); if (cell != null) { var str = cell.ToString(); if (str.IndexOf("{") > -1 && str.IndexOf("}") > -1) { str = str.Replace("{", "").Replace("}", ""); var value = string.Empty; if (str.ToLower() == "head".ToLower()) { value = title; } else { var array = str.Split(','); if (array.Length == 1) { if (dict.ContainsKey(str)) { value = Math.Round(dict[str].Sum(e => e.Value), 2).ToString(); } } else { if (dict.ContainsKey(array[0])) { var entry = dict[array[0]]; if (entry.ContainsKey(array[1])) { value = Math.Round(entry[array[1]], 2).ToString(); } } } } cell.SetCellValue(value); } } } } } } return(workbook); }
private void WriteEvection(Sheet item, IRow modelRow, int serial, ref int line, ref ISheet sheet) { var row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } var eline = line; var tline = line; var cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(serial); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(item.PrintNumber); ExcelClass.GetCell(row, 2, modelRow).SetCellValue(item.CheckNumber); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(item.Name); ExcelClass.GetCell(row, 4, modelRow).SetCellValue(item.Time.ToLongDateString()); ExcelClass.GetCell(row, 5, modelRow).SetCellValue(item.Count); ExcelClass.GetCell(row, 6, modelRow).SetCellValue(item.Money); ExcelClass.GetCell(row, 7, modelRow).SetCellValue(item.Remarks); ExcelClass.GetCell(row, 8, modelRow).SetCellValue(item.Type.GetDescription()); ExcelClass.GetCell(row, 9, modelRow).SetCellValue(item.Status.GetDescription()); ExcelClass.GetCell(row, 10, modelRow).SetCellValue(item.Checkers); ExcelClass.GetCell(row, 11, modelRow).SetCellValue(item.CheckTime.HasValue ? item.CheckTime.Value.ToLongDateString() : "未审核"); ExcelClass.GetCell(row, 12, modelRow).SetCellValue("/"); if (item.Evection != null) { ExcelClass.GetCell(row, 13, modelRow).SetCellValue(item.Evection.Place); ExcelClass.GetCell(row, 14, modelRow).SetCellValue(item.Evection.Reason); ExcelClass.GetCell(row, 15, modelRow).SetCellValue(item.Evection.Persons); ExcelClass.GetCell(row, 16, modelRow).SetCellValue(item.Evection.Hotel); ExcelClass.GetCell(row, 17, modelRow).SetCellValue(item.Evection.Mark); ExcelClass.GetCell(row, 18, modelRow).SetCellValue(item.Evection.Other); if (item.Evection.Errands != null && item.Evection.Errands.Count > 0) { foreach (var errand in item.Evection.Errands) { WriteErrand(errand, modelRow, eline, ref sheet); } } if (item.Evection.TCosts != null && item.Evection.TCosts.Count > 0) { foreach (var traffic in item.Evection.TCosts) { WrirteTraffic(traffic, modelRow, tline++, ref sheet); } } } if (eline > tline)//出差人员多行 { } else//交通多行 { } line = eline > tline ? eline : tline; }
// データをエクセルへエクスポート private void btnExport_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt = (DataTable)dgvBatchNo.DataSource; ExcelClass xl = new ExcelClass(); xl.ExportToExcel(dt); //xl.ExportToCsv(dt, System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\ipqcdb.csv"); }
public ActionResult ExcelOutput() { var repodata = repo.All(); var data = from s1 in repodata select new { s1.銀行名稱, s1.銀行代碼, s1.分行代碼, s1.帳戶名稱, s1.帳戶號碼, s1.客戶資料.客戶名稱 }; var l_ExcelClass = new ExcelClass(); DataTable l_datatable = l_ExcelClass.ToDataTable(data); string OutputPath = l_ExcelClass.ExcelOutput(l_datatable, "CustomerAccount"); return(File(Server.MapPath(OutputPath), "application/vnd.ms-excel")); }
//[HttpPost] public ActionResult ExcelOutput() { var repodata = repo.All(); var data = from s1 in repodata select new { s1.客戶名稱, s1.統一編號, s1.電話, s1.傳真, s1.地址, s1.Email }; var l_ExcelClass = new ExcelClass(); DataTable l_datatable = l_ExcelClass.ToDataTable(data); string OutputPath = l_ExcelClass.ExcelOutput(l_datatable, "Customer"); return(File(Server.MapPath(OutputPath), "application/vnd.ms-excel")); }
public ActionResult ExcelOutput() { var repodata = repo.All(); var data = from s1 in repodata select new { s1.職稱, s1.姓名, s1.Email, s1.手機, s1.電話, s1.客戶資料.客戶名稱 }; var l_ExcelClass = new ExcelClass(); DataTable l_datatable = l_ExcelClass.ToDataTable(data); string OutputPath = l_ExcelClass.ExcelOutput(l_datatable, " ContactPerson"); return(File(Server.MapPath(OutputPath), "application/vnd.ms-excel")); }
private static void WriteReception(List <Sheet> list, ISheet sheet) { var serial = 1; var line = 2; var modelrow = sheet.GetRow(serial); foreach (var item in list) { var row = sheet.GetRow(line) ?? sheet.CreateRow(line); line++; row.Height = modelrow.Height; var cell = ExcelClass.GetCell(row, 0, modelrow); cell.SetCellValue(serial++); ExcelClass.GetCell(row, 1, modelrow).SetCellValue(item.CheckNumber); ExcelClass.GetCell(row, 2, modelrow).SetCellValue(item.Name); ExcelClass.GetCell(row, 3, modelrow).SetCellValue(item.Time.ToLongDateString()); ExcelClass.GetCell(row, 4, modelrow).SetCellValue(item.Money); ExcelClass.GetCell(row, 5, modelrow).SetCellValue(item.Count); ExcelClass.GetCell(row, 6, modelrow).SetCellValue(item.Remarks); if (item.Reception != null) { ExcelClass.GetCell(row, 7, modelrow).SetCellValue(item.Reception.RTime.ToLongDateString()); ExcelClass.GetCell(row, 8, modelrow).SetCellValue(item.Reception.CityName); ExcelClass.GetCell(row, 9, modelrow).SetCellValue(item.Reception.Amount); ExcelClass.GetCell(row, 10, modelrow).SetCellValue(item.Reception.Persons); if (item.Reception.Items != null && item.Reception.Items.Count > 0) { var rline = line - 1; foreach (var entry in item.Reception.Items) { row = sheet.GetRow(rline) ?? sheet.CreateRow(rline); rline++; ExcelClass.GetCell(row, 11, modelrow).SetCellValue(entry.Content); ExcelClass.GetCell(row, 12, modelrow).SetCellValue(entry.Coin); ExcelClass.GetCell(row, 13, modelrow).SetCellValue(entry.Way.GetDescription()); // ExcelClass.GetCell(row, 14, modelrow).SetCellValue(entry.Average); ExcelClass.GetCell(row, 15, modelrow).SetCellValue(entry.Memo); } if (rline > line) { row = sheet.GetRow(rline - 1); for (var i = 0; i < 11; i++) { ExcelClass.GetCell(row, i, modelrow); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(line - 1, rline - 1, i, i)); } } line = rline; } } } }
public void LoadDataFromExcelFile() { CheckDataFile = Visibility.Visible; IsFileDataButtonAvailable = false; if (!string.IsNullOrEmpty(ExcelFilePathLocation)) { int starCol = int.Parse(ProductCode.StartRow); int endCol = int.Parse(ProductCode.EndRow); string[] rows = { ProductCode.StartColum, ProductName.StartColum, ProductQuantity.StartColum, ProductPrice.StartColum, ProductWeight.StartColum }; PI.PIProdducts = ExcelClass.GetBaseProductInfo(rows, starCol, endCol, ExcelFilePathLocation); } }
public bool test() { try { _driver.FindElement(By.XPath(ExcelClass.GetCellData(xlpath, "Sheet1", 1, 3).ToString())); //_driver.FindElement(By.XPath("/html/body/h1")); return(false); } catch (Exception e) { return(true); } }
private void отобразитьВExcelToolStripMenuItem_Click(object sender, EventArgs e) { ExcelClass InformationAboutElements = new ExcelClass(); Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold); try { int CurrentCell=0; InformationAboutElements.NewDocument(); InformationAboutElements.AddNewPageAtTheStart("Параметры элемента УСП"); InformationAboutElements.SelectCells("A1", Type.Missing); InformationAboutElements.SetColumnWidth(20); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.WriteDataToCell("Параметр"); InformationAboutElements.SelectCells("B1", Type.Missing); InformationAboutElements.SetColumnWidth(30); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.WriteDataToCell("Значение"); for (int i = 0; i < DataInformTable.RowCount; i++) { CurrentCell = i + 2; InformationAboutElements.SelectCells(("A" + CurrentCell.ToString()), Type.Missing); InformationAboutElements.WriteDataToCell(DataInformTable[0,i].Value.ToString()); InformationAboutElements.SetHorisontalAlignment(2); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.SelectCells(("B" + CurrentCell.ToString()), Type.Missing); InformationAboutElements.WriteDataToCell(DataInformTable[1, i].Value.ToString()); InformationAboutElements.SetHorisontalAlignment(2); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); } } catch (Exception ex) { MessageBox.Show(ex.Message,"Error"); } finally { InformationAboutElements.Visible = true; InformationAboutElements.Dispose(); HeadFont.Dispose(); } }
public bool test() { try { // _driver.FindElement(By.XPath("//*[@id='main']")); _driver.FindElement(By.XPath(ExcelClass.GetCellData(xlpath, "Sheet1", 1, 4).ToString())); return(false); } catch (Exception e) { return(true); } }
private void WriteErrand(Errand item, IRow modelRow, int line, ref ISheet sheet) { var row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } var cell = ExcelClass.GetCell(row, 19, modelRow); cell.SetCellValue(item.Users); ExcelClass.GetCell(row, 20, modelRow).SetCellValue(string.Format("{0}-{1}", item.StartTime.ToShortDateString(), item.EndTime.ToShortDateString())); ExcelClass.GetCell(row, 21, modelRow).SetCellValue(item.Days); ExcelClass.GetCell(row, 22, modelRow).SetCellValue(item.Days * item.Peoples * 40); }
private static void SetRomanIssuer(LittleTableViewer props) { string shortFileName = TradeInstrument.GetIssuerCode(props.Instrument); string fullPath = SetFile(shortFileName); using (ExcelClass xls = new ExcelClass()) { xls.OpenDocument(fullPath, false); int firstFreeRow = int.Parse(xls.GetCellStringValue(1, 1)); xls.SetCellValue(9, firstFreeRow, Deal.GetDirection(props.IsLong)); xls.SetCellValue(10, firstFreeRow, props.OpenValue.ToString()); xls.SetCellValue(11, firstFreeRow, props.StopValue.ToString()); xls.SetCellValue(12, firstFreeRow, props.Profit.ToString()); xls.CloseDocumentSave(); } }
public IWorkbook GetWorkbook(List <Sheet> list) { IWorkbook workbook = ExcelClass.GetAbsolutePath(System.Configuration.ConfigurationManager.AppSettings["REPORT"].ToString()).OpenExcel(); if (workbook != null) { ISheet sheet = workbook.GetSheetAt(0); if (sheet != null) { IRow row = sheet.GetRow(3); if (row != null) { var modelRow = row; var serial = 1; var rowNumber = 3; foreach (var item in list) { if (item.Type == SheetType.Daily && item.Substances != null) { foreach (var entry in item.Substancs_Views) { WriteDialy(item, entry, modelRow, rowNumber++, serial++, ref sheet); } //foreach (var entry in item.Substances) //{ // WriteDialy(item, entry, modelRow, rowNumber++, serial++, ref sheet); //} } else { var startrow = rowNumber; WriteEvection(item, modelRow, serial++, ref rowNumber, ref sheet); if (startrow != rowNumber) { for (var i = 0; i < 19; i++) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow, rowNumber - 1, i, i)); } } } } } } } return(workbook); }
/// <summary> /// 作用:下载每个月汇总表格 /// 作者:汪建龙 /// 编写时间:2017年1月17日17:03:56 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="reportTypes"></param> /// <param name="sheets"></param> /// <param name="billList1"></param> /// <param name="billList2"></param> /// <returns></returns> public static IWorkbook DownloadCollect(int year, int month, List <ReportType> reportTypes, List <Sheet> sheets, List <BillRecordView> billList1, List <BillRecordView> billList2) { IWorkbook workbook = ExcelClass.GetAbsolutePath(System.Configuration.ConfigurationManager.AppSettings["Collect"].ToString()).OpenExcel(); if (workbook == null) { return(null); } ISheet sheet1 = workbook.GetSheetAt(0); ISheet sheet2 = workbook.GetSheetAt(1); ISheet sheet3 = workbook.GetSheetAt(2); WriteCompany(string.Format("杭州智拓{0}年{1}月收支汇总(一)", year, month), reportTypes, billList1, sheet1); WriteCompany(string.Format("杭州智拓{0}年{1}月收支汇总(二)", year, month), reportTypes, billList2, sheet2); WriteReport(string.Format("杭州智拓{0}年{1}月收支汇总(三)", year, month), reportTypes, sheets, sheet3); return(workbook); }
private void SetDealStops(ExcelClass xls, ref int iRow, Deal deal, string directionCol, string openCol, string reverseCol, string profitLossCol) { foreach (KeyValuePair <DateTime, decimal?> stop in deal.Stops) { string sDate = xls.GetCellStringValue(DateCol, iRow); DateTime date = StringFunctions.GetDate(sDate, DateFormat); while (stop.Key != date) { iRow++; sDate = xls.GetCellStringValue(DateCol, iRow); date = StringFunctions.GetDate(sDate, DateFormat); } xls.SetCellValue(directionCol, iRow, deal.DirectionStr); xls.SetCellValue(openCol, iRow, deal.OpenValue.ToString()); xls.SetCellValue(reverseCol, iRow, stop.Value.ToString()); iRow++; } xls.SetCellValue(profitLossCol, iRow, deal.ProfitProcentStr); }
/// <summary> /// 作用:日常报销填写 /// </summary> /// <param name="list"></param> /// <param name="sheet"></param> private static void WriteDaily(List <Sheet> list, ISheet sheet) { var serial = 1; //当前报销单的序号数 var line = 1; //当前写的行数 var modelRow = sheet.GetRow(serial); foreach (var item in list) { var row = sheet.GetRow(line) ?? sheet.CreateRow(line); line++; row.Height = modelRow.Height; var cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(serial++); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(item.CheckNumber); ExcelClass.GetCell(row, 2, modelRow).SetCellValue(item.Name); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(item.Time.ToLongDateString()); ExcelClass.GetCell(row, 4, modelRow).SetCellValue(item.Money); ExcelClass.GetCell(row, 5, modelRow).SetCellValue(item.Count); ExcelClass.GetCell(row, 6, modelRow).SetCellValue(item.Remarks); if (item.Substancs_Views != null && item.Substancs_Views.Count > 0) { var sline = line - 1; foreach (var subView in item.Substancs_Views) { row = sheet.GetRow(sline) ?? sheet.CreateRow(sline); sline++; ExcelClass.GetCell(row, 7, modelRow).SetCellValue(subView.Title); ExcelClass.GetCell(row, 8, modelRow).SetCellValue(subView.Details); ExcelClass.GetCell(row, 9, modelRow).SetCellValue(subView.Price); } if (sline > line) { row = sheet.GetRow(sline - 1); for (var i = 0; i < 7; i++) { ExcelClass.GetCell(row, i, modelRow); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(line - 1, sline - 1, i, i)); } } line = sline; } } }
private void WrirteTraffic(Traffic first, IRow modelRow, int line, ref ISheet sheet) { var row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } var cell = ExcelClass.GetCell(row, 23, modelRow); cell.SetCellValue(first.Type.GetDescription()); ExcelClass.GetCell(row, 24, modelRow).SetCellValue(first.Cost); ExcelClass.GetCell(row, 25, modelRow).SetCellValue(first.KiloMeters); ExcelClass.GetCell(row, 26, modelRow).SetCellValue(first.Plate); ExcelClass.GetCell(row, 27, modelRow).SetCellValue(first.Toll); ExcelClass.GetCell(row, 28, modelRow).SetCellValue(first.Petrol); ExcelClass.GetCell(row, 29, modelRow).SetCellValue(first.Driver.GetDescription()); ExcelClass.GetCell(row, 30, modelRow).SetCellValue(first.CarPetty); }
private static void SetQuote(Quote quote, string code) { string fullPath = SetFile(code); using (ExcelClass xls = new ExcelClass()) { xls.OpenDocument(fullPath, false); int firstFreeRow = int.Parse(xls.GetCellStringValue(1, 1)); xls.SetCellValue(1, firstFreeRow, quote.Date.ToShortDateString()); xls.SetCellValue(2, firstFreeRow, quote.Open.ToString()); xls.SetCellValue(3, firstFreeRow, quote.Close.ToString()); xls.SetCellValue(4, firstFreeRow, quote.Low.ToString()); xls.SetCellValue(5, firstFreeRow, quote.High.ToString()); xls.SetCellValue(6, firstFreeRow, quote.Volume.ToString()); xls.SetCellValue(7, firstFreeRow, quote.Lot.ToString()); xls.SetCellValue(1, 1, (++firstFreeRow).ToString()); xls.CloseDocumentSave(); } }
/// <summary> /// 作用:写入日常报销汇总表 /// 作者:汪建龙 /// 编写时间:2017年1月17日16:56:19 /// </summary> /// <param name="head"></param> /// <param name="reportTypes"></param> /// <param name="list"></param> /// <param name="sheet"></param> public static void WriteReport(string head, List <ReportType> reportTypes, List <Sheet> list, ISheet sheet) { if (list == null || reportTypes == null) { return; } var line = 0; IRow row = sheet.GetRow(line) ?? sheet.CreateRow(line); ICell cell = row.GetCell(0) ?? row.CreateCell(0); cell.SetCellValue(head); line = 3; var modelrow = sheet.GetRow(line); var dailys = list.Where(e => e.Type == SheetType.Daily).ToList(); var errands = list.Where(e => e.Type == SheetType.Errand).ToList(); var substance_views = new List <SubstancsView>(); foreach (var item in dailys) { if (item.Substancs_Views != null && item.Substancs_Views.Count > 0) { substance_views.AddRange(item.Substancs_Views); } } List <SubstancsView> temp; foreach (var item in reportTypes) { temp = substance_views.Where(e => e.Name.ToLower() == item.Name.ToLower()).ToList(); row = sheet.GetRow(line) ?? sheet.CreateRow(line); line++; cell = ExcelClass.GetCell(row, 0, modelrow); cell.SetCellValue(item.Name); ExcelClass.GetCell(row, 1, modelrow).SetCellValue(temp.Sum(e => e.Price)); ExcelClass.GetCell(row, 2, modelrow).SetCellValue(string.Join(";", temp.Select(e => e.Details).ToArray())); } row = sheet.GetRow(line) ?? sheet.CreateRow(line); cell = ExcelClass.GetCell(row, 0, modelrow); cell.SetCellValue("差旅费"); ExcelClass.GetCell(row, 1, modelrow).SetCellValue(errands.Sum(e => e.Money)); }
public static IWorkbook WriteCoProjects(List <ProjectView> list) { IWorkbook workbook = ParameterManager.COProject.OpenExcel(); if (workbook != null) { var sheet = workbook.GetSheetAt(0); if (sheet != null) { var modelrow = sheet.GetRow(1); IRow row = null; var serial = 1; foreach (var item in list) { row = sheet.GetRow(serial) ?? sheet.CreateRow(serial); serial++; var cell = ExcelClass.GetCell(row, 0, modelrow); if (cell != null) { cell.SetCellValue(item.ID); } ExcelClass.GetCell(row, 1, modelrow).SetCellValue(item.Year); ExcelClass.GetCell(row, 2, modelrow).SetCellValue(item.GroupName); ExcelClass.GetCell(row, 3, modelrow).SetCellValue(item.Serial); ExcelClass.GetCell(row, 4, modelrow).SetCellValue(item.Number); ExcelClass.GetCell(row, 5, modelrow).SetCellValue(item.CityName); ExcelClass.GetCell(row, 6, modelrow).SetCellValue(item.Town); ExcelClass.GetCell(row, 7, modelrow).SetCellValue(item.Name); ExcelClass.GetCell(row, 8, modelrow).SetCellValue(item.TypeChars.Substring(0, 1)); ExcelClass.GetCell(row, 9, modelrow).SetCellValue(item.TypeChars + item.TypeName); ExcelClass.GetCell(row, 10, modelrow).SetCellValue(item.RealName); ExcelClass.GetCell(row, 11, modelrow).SetCellValue(item.Participation); ExcelClass.GetCell(row, 12, modelrow).SetCellValue(item.Mark); ExcelClass.GetCell(row, 13, modelrow).SetCellValue(item.Percent.HasValue ? item.Percent.Value : 0); } } } return(workbook); }
/// <summary> /// 作用:下载合同统计表 /// 作者:汪建龙 /// 编写时间:2017年1月16日19:33:54 /// </summary> /// <param name="list"></param> /// <returns></returns> public static IWorkbook DownContractWorkbook(List <Contract> list) { IWorkbook workbook = ExcelClass.GetAbsolutePath(System.Configuration.ConfigurationManager.AppSettings["Contract"].ToString()).OpenExcel(); if (workbook == null) { return(null); } ISheet sheet = workbook.GetSheetAt(0); if (sheet == null) { return(null); } var line = 1; IRow modelrow = sheet.GetRow(1); foreach (var item in list) { var row = sheet.GetRow(line); if (row == null) { row = sheet.CreateRow(line); } line++; var cell = ExcelClass.GetCell(row, 0, modelrow); cell.SetCellValue(item.ID); ExcelClass.GetCell(row, 1, modelrow).SetCellValue(item.Number); ExcelClass.GetCell(row, 2, modelrow).SetCellValue(item.Name); ExcelClass.GetCell(row, 3, modelrow).SetCellValue(item.Company); ExcelClass.GetCell(row, 4, modelrow).SetCellValue(item.ZtopCompany.GetDescription()); ExcelClass.GetCell(row, 5, modelrow).SetCellValue(item.StartTime.ToLongDateString()); ExcelClass.GetCell(row, 6, modelrow).SetCellValue(item.EndTime.HasValue ? item.EndTime.Value.ToLongDateString() : "/"); ExcelClass.GetCell(row, 7, modelrow).SetCellValue(item.Money); ExcelClass.GetCell(row, 8, modelrow).SetCellValue(item.PerformanceBond); ExcelClass.GetCell(row, 9, modelrow).SetCellValue(item.Invoices != null ? item.Invoices.Where(e => e.State == InvoiceState.Have).Sum(e => e.Money):.0); ExcelClass.GetCell(row, 10, modelrow).SetCellValue(item.BillContracts != null ? item.BillContracts.Sum(e => e.Price) : .0); } return(workbook); }
private void ReadDeals(ref Dictionary<DateTime, Deal> someDeals, DateTime fromDate, DateTime toDate, string directionDealCol, string openDealCol, string reverseDealCol) { using (ExcelClass xls = new ExcelClass()) { try { someDeals = new Dictionary<DateTime, Deal>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; Deal deal = null; bool isFirstDeal = true; while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date > fromDate && date < toDate) { string sDir = xls.GetCellStringValue(directionDealCol, i); string sOpen = xls.GetCellStringValue(openDealCol, i); string sReverse = xls.GetCellStringValue(reverseDealCol, i); if (!string.IsNullOrEmpty(sDir) && !string.IsNullOrEmpty(sOpen) && !string.IsNullOrEmpty(sReverse)) { double? reverse = StringFunctions.TryParse(sReverse); if (isFirstDeal) { double? open = StringFunctions.TryParse(sOpen); deal = new Deal(sDir, date.AddDays(-1), open); deal.SetStopReverse(date, reverse); isFirstDeal = false; } else { if (deal.IsSameDirection(sDir)) { deal.SetStopReverse(date, reverse); } else { double? open = StringFunctions.TryParse(sOpen); someDeals.Add(deal.OpenDate, deal); deal = deal.Reverse(date.AddDays(-1), open); deal.SetStopReverse(date, reverse); } } } } i++; sDate = xls.GetCellStringValue(DateCol, i); } } finally { xls.CloseDocument(false); } } }
private static void ReadPage(string fileName, Dictionary <string, string> kodes) { ExcelClass xls = new ExcelClass(); try { xls.OpenDocument(fileName, false); try { string date = GetDate(xls.GetCellStringValue(2, 2)); string fileDate = Path.GetFileNameWithoutExtension(fileName); if (string.IsNullOrEmpty(date)) { date = fileDate; } else { if (date != fileDate) { MessageBox.Show(date + '\n' + fileDate + '\n' + "Даты разные!"); } } int iRow = 9; string name = xls.GetCellStringValue(2, iRow); while (!string.IsNullOrEmpty(name)) { string orientation = xls.GetCellStringValue(3, iRow); if (kodes.ContainsKey(name)) { string buyCost = xls.GetCellStringValue(4, iRow); string stopCost = xls.GetCellStringValue(5, iRow); string profitLoss = xls.GetCellStringValue(6, iRow); SetRow(kodes, date, name, orientation, buyCost, stopCost, profitLoss); } else { if (orientation != "ЛОНГ" || orientation != "Л0НГ" || orientation != "шорт") { break; } MessageBox.Show(name); } iRow++; name = xls.GetCellStringValue(2, iRow); } } finally { xls.CloseDocument(false); } } finally { xls.Dispose(); } }
private static void SetKodesList(Dictionary<string, string> dict) { OpenFileDialog xlsBooks = new OpenFileDialog { Title = "Выберите файл Excel с кодами", DefaultExt = "xlsx", Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*" }; if (xlsBooks.ShowDialog() != DialogResult.OK) return; ExcelClass xls = new ExcelClass(); try { xls.OpenDocument(xlsBooks.FileName, false); try { const int iCol = 1; int iRow = 1; string key = xls.GetCellStringValue(iCol, iRow); while (!string.IsNullOrEmpty(key)) { dict.Add(key, xls.GetCellStringValue(2, iRow)); iRow++; key = xls.GetCellStringValue(iCol, iRow); } } finally { xls.CloseDocument(false); } } finally { xls.Dispose(); } }
/// <summary> /// Обработка Excel документов /// </summary> /// <param name="nameColName"></param> /// <param name="titleColName"></param> /// <param name="docColName"></param> /// <param name="yearColName"></param> public static void SelectXlsFiles(string nameColName, string titleColName, string docColName, string yearColName, ProgressBar bar, int type, Label status, Label nFiles) { _nameColName = nameColName; _titleColName = titleColName; _docColName = docColName; _yearColName = yearColName; string mess = ""; OpenFileDialog xlsBooks = new OpenFileDialog(); xlsBooks.Title = "Выберите файлы Excel с позициями"; xlsBooks.Multiselect = true; xlsBooks.DefaultExt = "xlsx"; xlsBooks.Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"; if (xlsBooks.ShowDialog() != DialogResult.OK) return; _userLog = new StreamWriter(_USER_LOG_FULL_PATH, false, Encoding.UTF8); _userLog.WriteLine(DateTime.Now.ToLongTimeString()); _userLog.WriteLine("Начало работы"); _logger = new Logger(); _logger.WriteLine("----------------------------------------- NEW SESSION ----------------------------------------------"); ExcelClass xls = new ExcelClass(); try { bool cancel = false; for (int i = 0; i < xlsBooks.FileNames.Length; i++) { status.Text = "Чтение файлов"; nFiles.Text = i.ToString(); Application.DoEvents(); List<Position> instruments = new List<Position>(); int iRow = _FIRST_ROW; GroupElement group; bool exist; bool groupExist = false; try { xls.OpenDocument(xlsBooks.FileNames[i], false); _userLog.WriteLine("Открыт файл " + xlsBooks.FileNames[i]); _logger.WriteLine("Открыт файл " + xlsBooks.FileNames[i]); string shortFileName = GetShortFileName(xlsBooks.FileNames[i]); _logger.WriteLine("Короткое имя группы - " + shortFileName); string fullName = xls.GetCellStringValue("A", 1); _logger.WriteLine("Полный путь группы - " + fullName); group = new GroupElement(shortFileName, GetGroupParams(xls), fullName); int oldId; exist = GroupElement.Exist(group.Name, out oldId); if (exist) { group = new GroupElement(oldId, group.Name, group.FullName); groupExist = true; _userLog.WriteLine("Группа уже существует. Найдена группа \"" + group.Name + "\""); _logger.WriteLine("Группа уже существует. Найдена группа \"" + group.Name + "\""); } while (!xls.CellIsNullOrVoid(_nameColName, iRow) || !xls.CellIsNullOrVoid(_titleColName, iRow) || !xls.CellIsNullOrVoid(_docColName, iRow)) { ProcessOneRow2(xls, iRow, ref mess, instruments, group, shortFileName, type); iRow++; } } finally { xls.CloseDocument(); } if (instruments.Count > 0) { if (!exist) { group.WriteToDb(); group.AddGeneralFolders(@"Справочники цеха 254\"); _userLog.WriteLine("Группа записана в БД"); _logger.WriteLine("Группа \"" + group.Name + "\" записана в БД"); } } bar.Maximum = instruments.Count; bar.Value = 0; status.Text = "Запись в БД"; foreach (Position pos in instruments) { int oldPosId; if (Position.Exist(pos.Title, out oldPosId)) { int dbGroupId = Position.GetGroupId(oldPosId); if (group.Id != dbGroupId) { DialogResult result = MessageBox.Show("Позиция \"" + pos.Title + "\" уже существует и находится в другой группе (\"" + GroupElement.GetName(dbGroupId) + "\"). Перенести в группу из электронной таблицы (\"" + group.Name + "\") ?", "Внимание!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); switch (result) { case DialogResult.Cancel: cancel = true; goto Cancel; case DialogResult.Yes: pos.SetNewGroup(oldPosId, group.Id); break; } } string message; Dictionary<int, string> differentParams; if (pos.IsSimilarPositionParams(oldPosId, out message, out differentParams)) { _userLog.WriteLine(message); _logger.WriteLine(message); DialogResult result = MessageBox.Show(message + Environment.NewLine + "Обновить параметры?", "Внимание!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); switch (result) { case DialogResult.Cancel: cancel = true; goto Cancel; case DialogResult.Yes: Position.EditParametrs(oldPosId, differentParams); break; } } } else { string message; pos.IsSimilarGroupParams(group.GetParamCodes(), out message); _userLog.WriteLine(message); pos.WriteToDb2(); } bar.Increment(1); Application.DoEvents(); } _userLog.WriteLine("Записано " + instruments.Count + " позиций"); _userLog.Flush(); _userLog.WriteLine(); _logger.WriteLine("Записано " + instruments.Count + " позиций"); } Cancel: if (cancel) { _userLog.WriteLine("***Операция отменена пользователем"); _userLog.Flush(); _userLog.WriteLine(); _logger.WriteLine("***Операция отменена пользователем"); } } finally { xls.Dispose(); _userLog.Close(); } Process.Start(_USER_LOG_FULL_PATH); MessageBox.Show("Готово!"); }
private void вExcelToolStripMenuItem_Click(object sender, EventArgs e) { ExcelClass InformationAboutElements = new ExcelClass(); HashCode.HashCode.CheckFileByHash("требование-накладная.xlt"); if (System.IO.File.Exists(Program.PathString + "\\требование-накладная.xlt")) { try { int TagOfObject = 0; InformationAboutElements.OpenDocument(Program.PathString + "\\требование-накладная.xlt"); InformationAboutElements.Visible = false; //перевод данных в Excel //органзация InformationAboutElements.SelectCells("D8", Type.Missing); InformationAboutElements.WriteDataToCell(textBox1.Text.ToString()); //номер требование накладной InformationAboutElements.SelectCells("C19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1OneA.Text.ToString()); //Дата составления InformationAboutElements.SelectCells("F19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1One.Text.ToString()); //Код вида операций InformationAboutElements.SelectCells("I19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1two.Text.ToString()); //структурное подразделение InformationAboutElements.SelectCells("K19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1three.Text.ToString()); //вид деятельности InformationAboutElements.SelectCells("N19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1four.Text.ToString()); //шифр получателя InformationAboutElements.SelectCells("Q19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1five.Text.ToString()); //шифр потребителя InformationAboutElements.SelectCells("S19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1FiveA.Text.ToString()); //вид деятельности InformationAboutElements.SelectCells("U19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1Six.Text.ToString()); //Учетная ед. выпуска продукции (работ. услуг) InformationAboutElements.SelectCells("X19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1Seven.Text.ToString()); //Порядковый но-мер, по склад. картотеке InformationAboutElements.SelectCells("AA19", Type.Missing); InformationAboutElements.WriteDataToCell(PanelN1sevenA.Text.ToString()); //Через кого InformationAboutElements.SelectCells("C23", Type.Missing); InformationAboutElements.WriteDataToCell(textBox2.Text.ToString()); //Затребовал InformationAboutElements.SelectCells("C25", Type.Missing); InformationAboutElements.WriteDataToCell(textBox3.Text.ToString()); //Разрешил InformationAboutElements.SelectCells("W25", Type.Missing); InformationAboutElements.WriteDataToCell(textBox4.Text.ToString()); //Отпустил дата InformationAboutElements.SelectCells("C111", Type.Missing); InformationAboutElements.WriteDataToCell(dateTimePicker1.Text.ToString()); //Отпустил фамилия InformationAboutElements.SelectCells("H111", Type.Missing); InformationAboutElements.WriteDataToCell(textBox245.Text.ToString()); //Получил фамилия InformationAboutElements.SelectCells("V111", Type.Missing); InformationAboutElements.WriteDataToCell(textBox246.Text.ToString()); //Получил дата InformationAboutElements.SelectCells("R111", Type.Missing); InformationAboutElements.WriteDataToCell(dateTimePicker2.Text.ToString()); for (int i = 0; i < 5; i++) for (int j = 0; j < ((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls.Count; j++) { TagOfObject = (Convert.ToInt32(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Tag)); if (TagOfObject == 0) { //Корресп. Счет, суб-счет, код аналит. Учета InformationAboutElements.SelectCells("A" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 1) { //Материальные ценности наименование (9) InformationAboutElements.SelectCells("C" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 2) { //код материала InformationAboutElements.SelectCells("C" + (i * 4 + 38).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 3) { //заводской номер InformationAboutElements.SelectCells("F" + (i * 4 + 38).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 4) { //Ед. изм. Наимен. Код InformationAboutElements.SelectCells("I" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 5) { //Ед. изм. Наимен. Код InformationAboutElements.SelectCells("I" + (i * 4 + 38).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 6) { //затребовано InformationAboutElements.SelectCells("K" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 7) { //отпущено InformationAboutElements.SelectCells("M" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 8) { //Цена (руб., коп.) InformationAboutElements.SelectCells("O" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 9) { //Сумма без расчета НДС, руб. коп. InformationAboutElements.SelectCells("Q" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 10) { //Корреспон. счет, суб-счет, код аналит. учета InformationAboutElements.SelectCells("S" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 11) { //Обесп. Серийн. № машины InformationAboutElements.SelectCells("U" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 12) { //Некомп-плектный остаток InformationAboutElements.SelectCells("W" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 13) { //Код издел. ШР, БЛ, № с/з InformationAboutElements.SelectCells("Y" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 14) { //Признак раздела InformationAboutElements.SelectCells("AA" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 15) { //Код основ. Матер. InformationAboutElements.SelectCells("AC" + (i * 4 + 36).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } } for (int i = 5; i < 16; i++) for (int j = 0; j < ((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls.Count; j++) { TagOfObject = (Convert.ToInt32(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Tag)); if (TagOfObject == 0) { //Корресп. Счет, суб-счет, код аналит. Учета InformationAboutElements.SelectCells("A" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 1) { //Материальные ценности наименование (9) InformationAboutElements.SelectCells("C" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 2) { //код материала InformationAboutElements.SelectCells("C" + ((i - 5) * 4 + 67).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 3) { //заводской номер InformationAboutElements.SelectCells("F" + ((i - 5) * 4 + 67).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 4) { //Ед. изм. Наимен. Код InformationAboutElements.SelectCells("I" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 5) { //Ед. изм. Наимен. Код InformationAboutElements.SelectCells("I" + ((i - 5) * 4 + 67).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 6) { //затребовано InformationAboutElements.SelectCells("K" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 7) { //отпущено InformationAboutElements.SelectCells("M" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 8) { //Цена (руб., коп.) InformationAboutElements.SelectCells("O" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 9) { //Сумма без расчета НДС, руб. коп. InformationAboutElements.SelectCells("Q" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 10) { //Корреспон. счет, суб-счет, код аналит. учета InformationAboutElements.SelectCells("S" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 11) { //Обесп. Серийн. № машины InformationAboutElements.SelectCells("U" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 12) { //Некомп-плектный остаток InformationAboutElements.SelectCells("W" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 13) { //Код издел. ШР, БЛ, № с/з InformationAboutElements.SelectCells("Y" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 14) { //Признак раздела InformationAboutElements.SelectCells("AA" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } else if (TagOfObject == 15) { //Код основ. Матер. InformationAboutElements.SelectCells("AC" + ((i - 5) * 4 + 65).ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panelN" + (i + 2).ToString()), true)[0])).Controls[j].Text.ToString()); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { InformationAboutElements.Visible = true; InformationAboutElements.Dispose(); } } }
private void ExcelExport(System.Windows.Forms.Control control,ref ExcelClass TZExcel) { for (int i = 0; i < control.Controls.Count; i++) { if (control.Controls[i].Controls.Count == 0) { if ((control.Controls[i].GetType() == typeof(DateTimePicker)) || (control.Controls[i].GetType() == typeof(TextBox)) || (control.Controls[i].GetType() == typeof(ComboBox))) { TZExcel.SelectCells(control.Controls[i].Name, Type.Missing); TZExcel.WriteDataToCell(control.Controls[i].Text.ToString()); } } else if (control.Controls[i].Controls.Count > 0) { ExcelExport(control.Controls[i],ref TZExcel); } } }
public void WriteAllDeals() { using (ExcelClass xls = new ExcelClass()) { try { xls.OpenDocument(_quotesFileName, false); int i = FirstRow; foreach (KeyValuePair<DateTime, Deal> pair in _deals) { string sDate = xls.GetCellStringValue(DateCol, i); while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date.AddDays(-1) == pair.Key) { SetDealStops (xls, ref i, pair.Value, NewDirectionDealCol, NewOpenDealCol, NewReverseDealCol, NewProfitLostCol); break; } i++; sDate = xls.GetCellStringValue(DateCol, i); } } } finally { xls.CloseDocumentSave(); } } }
private void bExcelExportTo_Click(object sender, EventArgs e) { SaveFileDialog xlsF = new SaveFileDialog(); xlsF.Title = "Выберите файлы Excel с позициями"; xlsF.DefaultExt = "xlsx"; xlsF.Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"; if (xlsF.ShowDialog() != DialogResult.OK) return; ExcelClass xls = new ExcelClass(); try { xls.NewDocument(); xls.SaveDocument(xlsF.FileName); xls.OpenDocument(xlsF.FileName, false); try { string workshop = tbWorkshop.Text; List<string> tps = new List<string>(); Dictionary<string, string> param = new Dictionary<string, string>(); param.Add("T5_CE", workshop); SqlOracle.Sel( "select distinct t5_tp from table_5 where t5_ce = :T5_CE order by t5_tp", param, out tps); label16.Text = tps.Count.ToString(); int itp = 1; int rowN = 2; foreach (string tp in tps) { label10.Text = itp.ToString(); xls.SetCellValue("A", rowN, tp); List<int> opers = new List<int>(); param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); SqlOracle.Sel( "select distinct t5_no from table_5 where t5_tp like :T5_TP order by t5_no", param, out opers); label17.Text = opers.Count.ToString(); int ioper = 1; foreach (int oper in opers) { label11.Text = ioper.ToString(); xls.SetCellValue("B", rowN, oper.ToString()); List<int> pers = new List<int>(); param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); param.Add("T5_NO", oper.ToString()); SqlOracle.Sel( "select distinct t5_np from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO order by t5_np", param, out pers); label18.Text = pers.Count.ToString(); int iper = 1; foreach (int per in pers) { label12.Text = iper.ToString(); xls.SetCellValue("C", rowN, per.ToString()); if (oper == 35 && per == 30) { } param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); param.Add("T5_NO", oper.ToString()); param.Add("T5_NP", per.ToString()); int btm; SqlOracle.Sel( "select distinct t5_bt from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP", param, out btm); xls.SetCellValue("D", rowN, btm.ToString()); byte[] vo = SqlOracle.GetBytes( "select distinct t5_vo from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP", param); if (vo.Length > 1) { for (int i = 0; i < vo.Length/2; i++) { int i1 = vo[i*2]; int i2 = vo[i*2 + 1]; if (i1 == 0 && i2 == 0) { continue; } string h1 = Convert.ToString(i1, 16); string h2 = Convert.ToString(i2, 16); if (h1.Length < 2) { h1 = "0" + h1; } string h = h2 + h1; int nn = int.Parse(h, NumberStyles.AllowHexSpecifier); param = new Dictionary<string, string>(); param.Add("T2", nn.ToString()); string pr; SqlOracle.Sel("select t2_r1 from table_2 where t2_nn = :T2", param, out pr); string name; SqlOracle.Sel("select t2_nm from table_2 where t2_nn = :T2", param, out name); string title; SqlOracle.Sel("select t2_oboz from table_2 where t2_nn = :T2", param, out title); int gr; SqlOracle.Sel("select t2_ng from table_2 where t2_nn = :T2", param, out gr); if (pr != null) { xls.SetCellValue("E", rowN, pr); xls.SetCellValue("F", rowN, name); xls.SetCellValue("G", rowN, title); xls.SetCellValue("H", rowN, gr.ToString()); xls.SetCellValue("I", rowN, nn.ToString()); } else { xls.SetCellValue("E", rowN, "Инструмент не найден в базе"); xls.SetCellValue("I", rowN, nn.ToString()); } rowN++; } } iper++; Application.DoEvents(); rowN++; } ioper++; rowN++; } itp++; rowN++; } } finally { xls.CloseDocumentSave(); } } finally { xls.Dispose(); } }
private static Dictionary<string, string> GetGroupParams(ExcelClass xls) { _logger.WriteLine("Получение параметров группы"); Dictionary<string, string> parametrs = new Dictionary<string, string>(); int iCol = 1; string sKey = xls.GetCellStringValue(iCol, _ATTR_TITLE_ROW); string sVal = xls.GetCellStringValue(iCol, _ATTR_NAME_ROW); string psevdoYear = _yearColName; if (string.IsNullOrEmpty(psevdoYear)) { psevdoYear = _nameColName; } while (!string.IsNullOrEmpty(sKey)) { if (_nameColName != GetChar(iCol) && _titleColName != GetChar(iCol) && _docColName != GetChar(iCol) && psevdoYear != GetChar(iCol)) { if (sKey.Length > 2) { sKey = sKey.Substring(0, GroupElement.NParamNameChar); } int i = 1; while (parametrs.ContainsKey(sKey)) { sKey = sKey.Substring(0, GroupElement.NParamNameChar - 1); sKey += i; i++; } parametrs.Add(sKey, sVal); _logger.WriteLine(string.Format("\"{0}\" - \"{1}\"", sKey, sVal)); } iCol++; sKey = xls.GetCellStringValue(iCol, _ATTR_TITLE_ROW); sVal = xls.GetCellStringValue(iCol, _ATTR_NAME_ROW); } if (parametrs.Count > 0) { return parametrs; } return null; }
private void показатьВExcelToolStripMenuItem_Click(object sender, EventArgs e) { ExcelClass InformationAboutElements = new ExcelClass(); Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold); try { string currentName = ""; int countCell = 2; InformationAboutElements.NewDocument(); InformationAboutElements.AddNewPageAtTheStart("Параметры элемента УСП"); InformationAboutElements.SelectCells("A1", Type.Missing); InformationAboutElements.SetColumnWidth(20); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.WriteDataToCell("Параметр"); InformationAboutElements.SelectCells("B1", Type.Missing); InformationAboutElements.SetColumnWidth(30); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.WriteDataToCell("Значение"); for (int i = 1; i < 28; i++) { for (int j = 0; j < this.panel1.Controls.Count; j++) { if ((String.Compare(this.panel1.Controls[j].Tag.ToString(), i.ToString()) == 0)) { if ((String.Compare(this.panel1.Controls[j].Text, "") != 0)&&(String.Compare(this.panel1.Controls[j].Text, "0") != 0)) { currentName = ((System.Windows.Forms.Label)(this.Controls.Find(("label" + i.ToString()), true)[0])).Text; InformationAboutElements.SelectCells((string)(("B" + countCell.ToString())), Type.Missing); InformationAboutElements.WriteDataToCell(this.panel1.Controls[j].Text); InformationAboutElements.SetHorisontalAlignment(2); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); InformationAboutElements.SelectCells((string)(("A" + countCell.ToString())), Type.Missing); InformationAboutElements.WriteDataToCell(currentName); InformationAboutElements.SetHorisontalAlignment(2); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick); countCell++; } } } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { InformationAboutElements.Visible = true; InformationAboutElements.Dispose(); HeadFont.Dispose(); } }
private static void SetXls(string url, string site, ImageClass imageClass, string origName) { string[] imaName = GetPictureDate(url).Split('.'); string fileName = ""; for (int i = 0; i < imaName.Length - 1; i++) { fileName += imaName[i] + '.'; } fileName += "xlsx"; string directory = Path.Combine(Application.StartupPath, new Uri(site).Host, "rAndreevLists"); if (!File.Exists(Path.Combine(directory, fileName))) { imageClass.RecognizeToExcel(directory, fileName); ExcelClass xls = new ExcelClass(); try { xls.OpenDocument(Path.Combine(directory, fileName), false); xls.AddPicture(origName, "H1"); } finally { xls.CloseDocumentSave(); xls.Dispose(); } } }
public void ReadQuotes(DateTime fromDate, DateTime toDate) { using (ExcelClass xls = new ExcelClass()) { try { _quotes = new Dictionary <DateTime, Quote>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date > fromDate && date < toDate) { double open = double.Parse(xls.GetCellStringValue(OpenCol, i)); double close = double.Parse(xls.GetCellStringValue(CloseCol, i)); double high = double.Parse(xls.GetCellStringValue(HighCol, i)); double low = double.Parse(xls.GetCellStringValue(LowCol, i)); ulong volume = ulong.Parse(xls.GetCellStringValue(VolumeCol, i)); Quote quote = new Quote(date, open, close, high, low, volume, i); _quotes.Add(date, quote); } i++; sDate = xls.GetCellStringValue(DateCol, i); } } finally { xls.CloseDocument(false); } } }
private void AddToExcel(string[,] texts, string excelPath, string excelName) { ExcelClass xls = new ExcelClass(); try { xls.NewDocument(); string fullPath = Path.Combine(excelPath, excelName); xls.SaveDocument(fullPath); xls.OpenDocument(fullPath, false); for (int i = 0; i < texts.GetLength(0); i++) { for (int j = 0; j < texts.GetLength(1); j++) { xls.SetCellValue(j + 1, i + 1, texts[i, j].Trim()); } } xls.SetAutoFit("B:B"); xls.SetAutoFit("C:C"); xls.SetAutoFit("D:D"); xls.SetAutoFit("E:E"); xls.SetAutoFit("F:F"); } finally { xls.CloseDocumentSave(); xls.Dispose(); } }
public void WriteSimpleDeals() { using (ExcelClass xls = new ExcelClass()) { try { _simpleDeals = new Dictionary<DateTime, Deal>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; int iOpen = i; Deal deal = null; Deal romanDeal = null; bool isCurrentDeal = false; bool isFirstDeal = true; double? currentReverse = 0.0; double? stop; double? lastStop = 0; DateTime prevDay = new DateTime(1, 1, 1); foreach (KeyValuePair<DateTime, Quote> quote in _quotes) { DateTime date = quote.Key; if (!isCurrentDeal) { if (!_deals.ContainsKey(prevDay)) { prevDay = date; continue; } romanDeal = _deals[prevDay]; isCurrentDeal = true; deal = new Deal(romanDeal.IsLong, romanDeal.OpenDate, romanDeal.OpenValue); } if (!isFirstDeal) { stop = romanDeal.GetStop(date); if (stop == null) { deal.Close(prevDay, lastStop); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); isCurrentDeal = false; } else { deal.SetStopReverse(date, stop); if (deal.IsLong && quote.Value.Close < stop || !deal.IsLong && quote.Value.Close > stop) { Deal newDeal = deal.Reverse(date, quote.Value.Close); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); deal = newDeal; isCurrentDeal = false; } } lastStop = stop; } prevDay = date; isFirstDeal = false; } } finally { xls.CloseDocumentSave(); } } }
private void SetDealStops(ExcelClass xls, ref int iRow, Deal deal, string directionCol, string openCol, string reverseCol, string profitLossCol) { foreach (KeyValuePair<DateTime, double?> stop in deal.Stops) { string sDate = xls.GetCellStringValue(DateCol, iRow); DateTime date = StringFunctions.GetDate(sDate, DateFormat); while (stop.Key != date) { iRow++; sDate = xls.GetCellStringValue(DateCol, iRow); date = StringFunctions.GetDate(sDate, DateFormat); } xls.SetCellValue(directionCol, iRow, deal.DirectionStr); xls.SetCellValue(openCol, iRow, deal.OpenValue.ToString()); xls.SetCellValue(reverseCol, iRow, stop.Value.ToString()); iRow++; } xls.SetCellValue(profitLossCol, iRow, deal.ProfitProcentStr); }
private static void SetRow(Dictionary <string, string> kodes, string date, string name, string orientation, string buyCost, string stopCost, string profitLoss) { string file = GetFilename(name, kodes); ExcelClass xls = new ExcelClass(); try { xls.OpenDocument(file, false); try { string csvDate; int iRow; string lastNum = xls.GetCellStringValue(11, 1); if (string.IsNullOrEmpty(lastNum)) { iRow = 2; csvDate = xls.GetCellStringValue(3, iRow); while (!string.IsNullOrEmpty(csvDate)) { iRow++; csvDate = xls.GetCellStringValue(3, iRow); } iRow--; } else { iRow = int.Parse(lastNum) - 1; } csvDate = GetCsvDate(xls.GetCellStringValue(3, iRow)); while (true) { if (csvDate == date) { SetNumValue(xls, 11, iRow, orientation); SetNumValue(xls, 12, iRow, buyCost); SetNumValue(xls, 13, iRow, stopCost); SetNumValue(xls, 14, iRow, profitLoss); xls.SetCellValue(11, 1, iRow.ToString()); break; } iRow--; if (iRow == 0) { MessageBox.Show("Нет такой даты (" + date + ") в " + name); break; } csvDate = GetCsvDate(xls.GetCellStringValue(3, iRow)); } } finally { xls.CloseDocument(true); } } finally { xls.Dispose(); } }
void ExportDGVToExcel(DataGridView dgv) { ExcelClass InformationAboutElements = new ExcelClass(); Font HeadFont = new Font(" Times New Roman ", 12.0f, FontStyle.Bold); int iterator = 0; try { char[] alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray(); int CurrentCell = 0; InformationAboutElements.NewDocument(); InformationAboutElements.AddNewPageAtTheStart("Данные"); for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible == true) { InformationAboutElements.SelectCells(alpha[iterator] + (1).ToString(), Type.Missing); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin); InformationAboutElements.WriteDataToCell(dgv.Columns[i].HeaderText); for (int j = 0; j < dgv.Rows.Count; j++) { InformationAboutElements.SelectCells(alpha[iterator] + (j + 2).ToString(), Type.Missing); InformationAboutElements.SetFont(HeadFont, 0); InformationAboutElements.SetBorderStyle(0, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin); InformationAboutElements.setAutoFit(alpha[iterator] + (j + 2).ToString()); InformationAboutElements.WriteDataToCell(dgv[i, j].Value.ToString()); } if (dgv[i, 0].Value.ToString().Length > dgv.Columns[i].HeaderText.Length) { InformationAboutElements.setAutoFit(alpha[iterator] + (2).ToString()); } else { InformationAboutElements.setAutoFit(alpha[iterator] + (1).ToString()); } iterator++; } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { InformationAboutElements.Visible = true; InformationAboutElements.Dispose(); HeadFont.Dispose(); } }
/// <summary> /// Выгрузка данных в Excel; /// </summary> /// <returns></returns> public void createXLS() { ExcelClass TZExcel = new ExcelClass(); HashCode.HashCode.CheckFileByHash("техническое задание.xlt"); if (System.IO.File.Exists(Program.PathString + "\\техническое задание.xlt")) { try { TZExcel.OpenDocument(Program.PathString + "\\техническое задание.xlt"); TZExcel.Visible = false; if ((BMPIsLoad == 1) || (BMPIsLoad == 3)) { if (this.SCETCH1.Image != null) { using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH1.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp); TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing); TZExcel.WritePictureToCell(tempFile.FilePath.ToString()); } } } else if ((BMPIsLoad == 0) || (BMPIsLoad == 2)) { if (this.SCETCH1.Image != null) { TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing); TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH1 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'")); } } if ((BMPIsLoad == 2) || (BMPIsLoad == 3)) { if (this.SCETCH2.Image != null) { using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH2.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp); TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing); TZExcel.WritePictureToCell(tempFile.FilePath.ToString()); } } } else if ((BMPIsLoad == 0) || (BMPIsLoad == 1)) { if (this.SCETCH2.Image != null) { TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing); TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH2 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'")); } } for (int i = 0; i < this.tabPage1.Controls.Count; i++) { if (this.tabPage1.Controls[i].Controls.Count == 0) { if ((this.tabPage1.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage1.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage1.Controls[i].GetType() == typeof(ComboBox))) { TZExcel.SelectCells(this.tabPage1.Controls[i].Name, Type.Missing); TZExcel.WriteDataToCell(this.tabPage1.Controls[i].Text.ToString()); } } else if (this.tabPage1.Controls[i].Controls.Count > 0) { ExcelExport(this.tabPage1.Controls[i], ref TZExcel); } } for (int i = 0; i < this.tabPage2.Controls.Count; i++) { if (this.tabPage2.Controls[i].Controls.Count == 0) { if ((this.tabPage2.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage2.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage2.Controls[i].GetType() == typeof(ComboBox))) { TZExcel.SelectCells(this.tabPage2.Controls[i].Name, Type.Missing); TZExcel.WriteDataToCell(this.tabPage2.Controls[i].Text.ToString()); } } else if (this.tabPage2.Controls[i].Controls.Count > 0) { ExcelExport(this.tabPage2.Controls[i], ref TZExcel); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { TZExcel.Visible = true; TZExcel.Dispose(); } } }
private static void SetNumValue(ExcelClass xls, int iCol, int iRow, string value) { double d; string s; if (double.TryParse(value, out d)) { s = value; } else { s = GetNum(value); } xls.SetCellValue(iCol, iRow, s); }
private static List<Operation> GetOperations(string fileName) { List<Operation> opers = new List<Operation>(); ExcelClass xls = new ExcelClass(); try { int i = 1; try { xls.OpenDocument(fileName, false); string guid = xls.GetCellStringValue(1, 1); string kod = xls.GetCellStringValue(2, 1); string text = xls.GetCellStringValue(3, 1); while (!string.IsNullOrEmpty(guid)) { int kodI = int.Parse(kod); opers.Add(new Operation(kodI, guid, text)); i++; guid = xls.GetCellStringValue(1, i); kod = xls.GetCellStringValue(2, i); text = xls.GetCellStringValue(3, i); } } finally { xls.CloseDocument(); } _logger.WriteLine("Записано " + i + " позиций"); } finally { xls.Dispose(); } if (opers.Count <= 0) { return null; } return opers; }
private static void ProcessOneRow2(ExcelClass xls, int iRow, ref string message, List<Position> positions, GroupElement group, string fileName, int posType) { string name = xls.GetCellStringValue(_nameColName, iRow); string title = xls.GetCellStringValue(_titleColName, iRow); if (string.IsNullOrEmpty(title)) { message += fileName + " - в " + iRow + " строке позиция без обозначения!" + Environment.NewLine; _userLog.WriteLine("*** Ошибка! *** " + message); _logger.WriteError(message); return; } string doc, year = ""; if (_docColName == _yearColName) { doc = xls.GetCellStringValue(_docColName, iRow); string[] split = doc.Split('-'); if (split.Length > 0) { doc = ""; for (int i = 0; i < split.Length - 1; i++) { doc += split[i]; } year = split[split.Length - 1]; } } else if (string.IsNullOrEmpty(_yearColName.Trim())) { doc = xls.GetCellStringValue(_docColName, iRow); year = ""; } else { doc = xls.GetCellStringValue(_docColName, iRow); year = xls.GetCellStringValue(_yearColName, iRow); } Position pos = null; switch (posType) { case 2: pos = new Materials(name, title, group, GetPositionParams(xls, iRow), doc, year); break; case 1: pos = new BuyInstrument(name, title, group, GetPositionParams(xls, iRow), doc, year); break; case 0: pos = new Machine(name, title, group, GetPositionParams(xls, iRow), doc, year); break; case 3: pos = new AssembleInstrument(name, title, group, GetPositionParams(xls, iRow), doc, year); break; case 4: pos = new MeasureInstrument(name, title, group, GetPositionParams(xls, iRow), doc, year); break; } positions.Add(pos); }
/// <summary> /// Выгрузка данных в Excel; /// </summary> /// <returns></returns> private void вExcelToolStripMenuItem_Click(object sender, EventArgs e) { ExcelClass InformationAboutElements = new ExcelClass(); HashCode.HashCode.CheckFileByHash("акт на списание.xlt"); if (System.IO.File.Exists(Program.PathString + "\\" + "акт на списание.xlt")) { try { HashCode.HashCode.CheckFileByHash("акт на списание.xlt"); InformationAboutElements.OpenDocument(Program.PathString + "\\акт на списание.xlt"); InformationAboutElements.Visible = false; //Акт № InformationAboutElements.SelectCells("J8", Type.Missing); InformationAboutElements.WriteDataToCell(J8.Text.ToString()); //Должность оформляющего InformationAboutElements.SelectCells("U4", Type.Missing); InformationAboutElements.WriteDataToCell(U4.Text.ToString()); //Расшифровка подписи оформляющего InformationAboutElements.SelectCells("U6", Type.Missing); InformationAboutElements.WriteDataToCell(U6.Text.ToString()); //Организация InformationAboutElements.SelectCells("D12", Type.Missing); InformationAboutElements.WriteDataToCell(D12.Text.ToString()); //Структурное подразделение InformationAboutElements.SelectCells("F14", Type.Missing); InformationAboutElements.WriteDataToCell(F14.Text.ToString()); // Дата приказа InformationAboutElements.SelectCells("J26", Type.Missing); InformationAboutElements.WriteDataToCell(J26.Text.ToString()); // Год осмотра предметов InformationAboutElements.SelectCells("S26", Type.Missing); InformationAboutElements.WriteDataToCell(S26.Text.ToString()); // Форма по ОКПО InformationAboutElements.SelectCells("Z10", Type.Missing); InformationAboutElements.WriteDataToCell(Z10.Text.ToString()); //Общее количество предметов InformationAboutElements.SelectCells("F86", Type.Missing); InformationAboutElements.WriteDataToCell(F86.Text.ToString()); //Номера и акты выбытия InformationAboutElements.SelectCells("F89", Type.Missing); InformationAboutElements.WriteDataToCell(F89.Text.ToString()); //Утиль InformationAboutElements.SelectCells("F112", Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.TextBox)(this.tabPage2.Controls.Find("F112", true)[0])).Text.ToString()); //Панели for (int i = 0; i < 6; i++) { for (int j = 0; j < ((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panel" + i.ToString()), true)[0])).Controls.Count; j++) { InformationAboutElements.SelectCells(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panel" + i.ToString()), true)[0])).Controls[j].Name.ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage1.Controls.Find(("panel" + i.ToString()), true)[0])).Controls[j].Text.ToString()); } } for (int i = 7; i < 16; i++) { for (int j = 0; j < ((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panel" + i.ToString()), true)[0])).Controls.Count; j++) { InformationAboutElements.SelectCells(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panel" + i.ToString()), true)[0])).Controls[j].Name.ToString(), Type.Missing); InformationAboutElements.WriteDataToCell(((System.Windows.Forms.Panel)(this.tabPage2.Controls.Find(("panel" + i.ToString()), true)[0])).Controls[j].Text.ToString()); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { InformationAboutElements.Visible = true; InformationAboutElements.Dispose(); } } }
private static Dictionary<string, string> GetPositionParams(ExcelClass xls, int iRow) { Dictionary<string, string> parametrs = new Dictionary<string, string>(); int iCol = 1; string sKey = xls.GetCellStringValue(iCol, _ATTR_TITLE_ROW); string sVal = xls.GetCellStringValue(iCol, iRow); string psevdoYear = _yearColName; if (string.IsNullOrEmpty(psevdoYear)) { psevdoYear = _nameColName; } while (!string.IsNullOrEmpty(sKey)) { if (_nameColName != GetChar(iCol) && _titleColName != GetChar(iCol) && _docColName != GetChar(iCol) && psevdoYear != GetChar(iCol)) { if (sKey.Length > 2) { sKey = sKey.Substring(0, GroupElement.NParamNameChar); } int i = 1; while (parametrs.ContainsKey(sKey)) { sKey = sKey.Substring(0, GroupElement.NParamNameChar - 1); sKey += i; i++; } parametrs.Add(sKey, sVal); } iCol++; sKey = xls.GetCellStringValue(iCol, _ATTR_TITLE_ROW); sVal = xls.GetCellStringValue(iCol, iRow); } if (parametrs.Count > 0) { return parametrs; } return null; }