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;
}
