private void GeraRelExcel(IEnumerable <Relexcel> listrel)
{
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
var i = 1;
foreach (var rel in listrel)
{
WorkSheet.Cells[i, 1] = string.Format("{0}/{1}/{2}", rel.Data.Day.ToString("00"), rel.Data.Month.ToString("00"), rel.Data.Year.ToString("00"));
WorkSheet.Cells[i, 2] = string.Format("{0}:{1}", ((int)rel.Hora.TotalHours).ToString("00"), rel.Hora.Minutes.ToString("00"));
WorkSheet.Cells[i, 3] = rel.Os;
WorkSheet.Cells[i, 4] = rel.Descricao;
i += 1;
}
// salva o arquivo
WorkBook.SaveAs(TBArquivo.Text + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
}
public void test2()
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook currentWorkBook = excelApp.ActiveWorkbook;
string workPath = currentWorkBook.Path;
WriteLine(workPath);
string aPath = workPath + "\\a.xlsx";
WriteLine(aPath);
Excel.Workbook workbook = excelApp.Workbooks.Open(aPath);
foreach (Excel.Worksheet sheet in workbook.Worksheets)
{
WriteLine(sheet.Name);
Excel.Range range = sheet.Range["A1"];
string value = range.Value2;
WriteLine(value);
Excel.Range range2 = sheet.Range["A1", "B3"];
object[,] values = range2.Value2;
string v22 = values.GetValue(2, 2) as string;
WriteLine(v22);
}
}
private void btnExport_Click(object sender, EventArgs e)
{
sfdExportExcel.InitialDirectory = "C:";
sfdExportExcel.Title = "Save as Excel File";
sfdExportExcel.FileName = "";
sfdExportExcel.Filter = "Excel Files(2007)|*.xlsx";
if (sfdExportExcel.ShowDialog() != DialogResult.Cancel)
{
Excel.Application excelApp = new Excel.Application();
excelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < dgwOTOutputs.Columns.Count + 1; i++)
{
excelApp.Cells[1, i] = dgwOTOutputs.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgwOTOutputs.Rows.Count; i++)
{
for (int j = 0; j < dgwOTOutputs.Columns.Count; j++)
{
excelApp.Cells[i + 2, j + 1] = dgwOTOutputs.Rows[i].Cells[j].Value.ToString();
}
}
excelApp.ActiveWorkbook.SaveCopyAs(sfdExportExcel.FileName.ToString());
excelApp.ActiveWorkbook.Saved = true;
excelApp.Quit();
}
}
private void lblExcel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Excel.Application excel = new Excel.Application();
excel.Visible = true;
object Missing = Type.Missing;
Workbook workbook = excel.Workbooks.Add(Missing);
Worksheet sheet1 = (Worksheet)workbook.Sheets[1];
int StartCol = 1;
int StartRow = 1;
for (int j = 0; j < dgw.Columns.Count; j++)
{
Range myRange = (Range)sheet1.Cells[StartRow, StartCol + j];
myRange.Value2 = dgw.Columns[j].HeaderText;
}
StartRow++;
for (int i = 0; i < dgw.Rows.Count; i++)
{
for (int j = 0; j < dgw.Columns.Count; j++)
{
Range myRange = (Range)sheet1.Cells[StartRow + i, StartCol + j];
myRange.Value2 = dgw[j, i].Value == null ? "" : dgw[j, i].Value;
myRange.Select();
}
}
}
private void ExcelTablosunaAktar(List <LgsSonuc> ogrenciXls, List <LgsSonuc> okulXls, List <LgsSonuc> ilIlceXls)
{
//excel baş
string excelDosyaAdi = seciliDizin + "_Rapor_" + DateTime.Now.Ticks + ".xlsx";
Microsoft.Office.Interop.Excel.Application aplicacion = new Microsoft.Office.Interop.Excel.Application();
Workbook calismaKitabi = aplicacion.Workbooks.Add();
ExcelOgrenciSayfasi(ogrenciXls, calismaKitabi);
ExcelOkulSayfasi(okulXls, calismaKitabi);
ExcelIlceSayfasi(ilIlceXls, calismaKitabi);
calismaKitabi.SaveAs(excelDosyaAdi, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//calismaKitabi.Close(true);
//aplicacion.Quit();
// calismaKitabi.SaveAs(excelDosyaAdi, XlFileFormat.xlWorkbookNormal);
calismaKitabi.Close(true);
aplicacion.Quit();
Process.Start(excelDosyaAdi);
}
/// <summary>
/// 图形滚动的Rolling方法
/// </summary>
/// <param name="dateThisday">施工当天的日期</param>
/// <remarks></remarks>
public override void Rolling(DateTime dateThisday)
{
F_RollingDate = dateThisday;
object lockobject = new object();
lock (lockobject)
{
Excel.Application app = Chart.Application;
app.ScreenUpdating = false;
// ------------------- 绘制监测曲线图
var Allday = F_dicDateAndColumnNumber.Keys;
//考察选定的日期是否有数据
TodayState State = default(TodayState);
DateTime closedDay = default(DateTime);
//
if (DateTime.Compare(dateThisday, DateSpan.StartedDate) < 0)
{
State = TodayState.BeforeStartDay;
}
else if (DateTime.Compare(dateThisday, DateSpan.FinishedDate) > 0)
{
State = TodayState.AfterFinishedDay;
}
else if (Allday.Contains(dateThisday)) //如果搜索的那一天有数据
{
State = TodayState.DateMatched;
closedDay = dateThisday;
}
else //搜索的那一天没有数据，则查找选定的日期附近最近的一天，并绘制其监测曲线
{
State = TodayState.DateNotFound;
SortedSet <DateTime> sortedlist_AllDays = new SortedSet <DateTime>(Allday);
closedDay = GetClosestDay(sortedlist_AllDays, dateThisday);
}
//
CurveRolling(dateThisday, State, closedDay);
// -------------------- 移动开挖深度的直线与文本框
if (P_ProcessRegionData != null)
{
float excavationElevation = 0;
try
{
excavationElevation = Convert.ToSingle(P_ProcessRegionData.Date_Elevation[dateThisday]);
//ClsData_DataBase.GetElevation(P_rgExcavationProcess, dateThisday)
}
catch (Exception)
{
Debug.Print("上面的Exception已经被Try...Catch语句块捕获，不用担心。出错代码位于ClsDrawing_Mnt_Incline.vb中。");
DateTime ClosestDate =
ClsData_DataBase.FindTheClosestDateInSortedList(
P_ProcessRegionData.Date_Elevation.Keys, dateThisday);
excavationElevation = Convert.ToSingle(P_ProcessRegionData.Date_Elevation[ClosestDate]);
}
MoveExcavation(_ExcavationDepth_lineAndTextbox, excavationElevation, dateThisday, State);
}
app.ScreenUpdating = true;
}
}
public void Dispose()
{
thisWorkBook.Close(false);
//templateWorkBook.Close(false);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
}
public SSForm()
{
excelApp = new Excel.Application();
dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
names = new[] { "王钟梅", "温万萍", "熊邦琴", "汤小丽", "龚正喜", "文清云", "罗小燕", "刘树玉", "孙芳", "齐琪", "李建英", "龚金萍", "聂品", "文新华", "杨兰珍", "董兰芳", "杨忠珍", "崔云华", "李娟", "王建新", "胡刚", "李家华", "陈明新", "孙其望", "曹安明", "熊文华", "蒋金华", "樊冬兰", "王南", "黄玲", "陈佳君", "段会义", "王辉29", "王争鸣", "熊文凤", "陈明凤", "苏金莲", "刘冬莲", "张辉", "杨有为", "王光亚", "倪新忠", "孙慧慧", "文远梅", "叶红", "刘英", "刘爱蓉", "肖继荣44", "李长庭", "张孝兰", "贾义恒", "卢恒山", "王敏47", "杨志奇", "张先觉", "杨安全", "杨楷", "张家洪", "李厚林", "胡荆江", "龚兰", "何德林", "文汉东", "谢明", "金保山", "黄伟荧", "李欣", "贺国权", "戴鸣", "康宁", "姚长江", "王志贵", "辛棣", "徐正丰", "幸小弘", "皮洁", "王昌富", "杨逸", "谢孝森", "黄谋玲", "胡钢", "李鸿霞", "田方兴", "全为民", "熊怡", "冯向南", "袁丙琴", "宫炫", "时运", "杨德义", "刘道忠", "肖爱华", "赵少华", "万荆发", "冯保亮", "王三元", "陈体文", "张红44", "孟祥存", "叶明华", "甘家铀", "何跃进" };
}
public GangweiFormFromSSForm()
{
excelApp = new Excel.Application();
wordApp = new Word.Application();
dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
levels = new[] { "正高级", "副高级", "中级", "助理级", "员级", "见习期、初期", "高级技师", "技师", "高级工", "中级工", "初级工" };
}
public SSForm500()
{
excelApp = new Excel.Application();
dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
levels = new[] { "正高级", "副高级", "中级", "助理级", "员级", "见习期、初期", "高级技师", "技师", "高级工", "中级工", "初级工" };
names = new[] { "" };
}
public SSFormENT()
{
excelApp = new Excel.Application();
dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
levels = new[] { "正高级", "副高级", "中级", "助理级", "员级", "见习期、初期", "高级技师", "技师", "高级工", "中级工", "初级工" };
levelsPay = new[] { 1134, 952, 872, 891, 822, 822, 943, 881, 817, 846, 813 };
wuyePay = new[] { 240, 240, 200, 200, 200, 200, 200, 200, 160, 160, 160 };
names = new[] { "" };
}
public SSDataFromSumForm()
{
excelApp = new Excel.Application();
dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
levels = new[] { "正高级", "副高级", "中级", "助理级", "员级", "见习期、初期", "高级技师", "技师", "高级工", "中级工", "初级工" };
names = new[] { "" };
eduList = new[] { "中专", "大学专科", "大学本科", "硕士研究生", "博士研究生" };
ZJlevelList = new[] { "员级", "助理级", "中级", "副高级", "正高级" };
GQlevelList = new[] { "初级工", "中级工", "高级工", "技师", "高级技师" };
}
private void button2_Click(object sender, EventArgs e)
{
var fileContent = string.Empty;
var filePath = string.Empty;
using (OpenFileDialog openFileDialog = new OpenFileDialog())
{
openFileDialog.InitialDirectory = "c:\\";
openFileDialog.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
openFileDialog.FilterIndex = 2;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = openFileDialog.FileName;
//Create COM Objects. Create a COM object for everything that is referenced
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
//Read the contents of the file into a stream
for (int i = 2; i <= xlRange.Rows.Count; i++)
{
if (xlRange.Cells[i, 1].Value2 != null &&
xlRange.Cells[i, 2].Value2 != null &&
xlRange.Cells[i, 3].Value2 != null)
{
idEjercicio = int.Parse(xlRange.Cells[i, 1].Value2.ToString());
idGrupoMuscular = int.Parse(xlRange.Cells[i, 2].Value2.ToString());
nombre = xlRange.Cells[i, 3].Value2.ToString();
listaEjercicios.Add(new Ejercicio()
{
IdEjercicio = idEjercicio,
IdGrupoMuscular = idGrupoMuscular,
Nombre = nombre
});
}
}
insertaEjercicio();
}
}
}
private void Import()
{
var path = OpenFile();
if (string.IsNullOrEmpty(path))
{
return;
}
Microsoft.Office.Interop.Excel.Application app = null;
try
{
app = new Microsoft.Office.Interop.Excel.Application();
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.Workbooks.Open(path);
dynamic _wb = app.Workbooks[1];
//生成CSV文件，并保存
string fileName = Path.GetTempFileName() + ".csv";
_wb.SaveAs(fileName, (int)XlFileFormat.xlCSV);
_wb.Close();
app.Workbooks.Close();
_wb = null;
app.Quit();
app = null;
GC.Collect();
ReadData(fileName);
}
catch (Exception e)
{
MessageBox.Show(this, e.Message);
}
finally
{
if (app != null)
{
app.Quit();
app = null;
}
GC.Collect();
}
}
private void ReleaseCom(Microsoft.Office.Interop.Excel.Application app)
{
if (app.Workbooks != null)
{
foreach (Microsoft.Office.Interop.Excel.Workbook wb in app.Workbooks)
{
foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
}
wb.Close(false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
}
app.Workbooks.Close();
}
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void mergeExcelSheet()
{
string workPath = @"D:\Library\Desktop\1\merge\";
string sumFileName = "sum.xlsx";
Excel.Application excelApp = new Excel.Application();
Excel.Workbook thisWorkBook = excelApp.Workbooks.Open(workPath + sumFileName);
Excel.Worksheet thisWorkSheet = thisWorkBook.Worksheets["Sheet1"];
//workPath = thisWorkBook.Path;
WriteLine(workPath);
DirectoryInfo workDir = new DirectoryInfo(workPath);
FileInfo[] files = workDir.GetFiles("*.xls*");
foreach (FileInfo file in files)
{
if (FileAttributes.Hidden != (file.Attributes & FileAttributes.Hidden) && file.Name != sumFileName)
{
WriteLine(file.FullName);
Excel.Workbook workbook = excelApp.Workbooks.Open(file.FullName);
Excel.Worksheet copyWorkSheet = workbook.Worksheets["Sheet1"];
if (copyWorkSheet != null)
{
copyWorkSheet.Name = Path.GetFileNameWithoutExtension(file.FullName);
copyWorkSheet.Copy(thisWorkSheet);
}
else
{
WriteLine($"Sheet1 Not Found: ${file.Name}");
}
workbook.Close(false);
}
}
thisWorkBook.SaveAs(Filename: $@"{workPath}done.xlsx");
thisWorkBook.Close();
}
private void btnExcelAktar_Click(object sender, EventArgs e)
{
DialogResult dr = MessageBox.Show("Tüm Kayıtlar Excel'e Aktarılsın Mı?", SMF.PrograminTamAdi, MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (dr == DialogResult.No)
{
return;
}
try
{
Excel.Application excel = new Excel.Application {
Visible = true
};
object missing = Type.Missing;
Workbook workbook = excel.Workbooks.Add(missing);
Worksheet shet1 = (Worksheet)workbook.Sheets[1];
const int startCol = 1;
int startRow = 1;
for (int j = 0; j < dgvRabtBilDB.Columns.Count; j++)
{
Range myRange = (Range)shet1.Cells[startRow, startCol + j];
myRange.Value2 = dgvRabtBilDB.Columns[j].HeaderText;
}
startRow++;
for (int i = 0; i < dgvRabtBilDB.Rows.Count; i++)
{
for (int j = 0; j < dgvRabtBilDB.Columns.Count; j++)
{
Range myRange = (Range)shet1.Cells[startRow + i, startCol + j];
myRange.Value2 = dgvRabtBilDB[j, i].Value == null ? "" : dgvRabtBilDB[j, i].Value;
myRange.Select();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Hata");
}
}
private void openContactsToolStripMenuItem_Click(object sender, EventArgs e)
{
var excel = new Excel.Application
{
SheetsInNewWorkbook = 1
};
excel.Workbooks.Add();
Excel.Worksheet table = excel.Workbooks[1].Worksheets.get_Item(1);
DataWorker dataWorker = DataWorker.Instance;
var contacts = dataWorker.GetContacts();
table.Range["A1", "A1"].Value = "User name";
table.Range["B1", "B1"].Value = "Login";
for (int i = 2; i < contacts.Count + 2; i++)
{
table.Range["A" + i, "A" + i].Value = contacts.ElementAt(i - 2).Item1;
table.Range["B" + i, "B" + i].Value = contacts.ElementAt(i - 2).Item2;
}
excel.Visible = true;
}
private void unload_Click(object sender, EventArgs e)
{
try
{
string connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hospital.mdb;";
OleDbConnection myConnection = new OleDbConnection(connectString);
myConnection.Open();
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkBook = ExcelApp.Workbooks.Add();
//Доктора
var sheetD = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];
sheetD.Name = "doctor";
var cellsD = (Microsoft.Office.Interop.Excel.Range)sheetD.Cells;
cellsD.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
sheetD.Cells[1, 1] = "num_doctor";
sheetD.Cells[1, 2] = "surname_doctor";
sheetD.Cells[1, 3] = "name_doctor";
sheetD.Cells[1, 4] = "middlename_doctor";
sheetD.Cells[1, 5] = "specialization_doctor";
string queryD = "SELECT * FROM doctor";
OleDbCommand commandD = new OleDbCommand(queryD, myConnection);
OleDbDataReader readerD = commandD.ExecuteReader();
int doctor = 2;
while (readerD.Read())
{
sheetD.Cells[doctor, 1] = readerD[1].ToString();
sheetD.Cells[doctor, 2] = readerD[2].ToString();
sheetD.Cells[doctor, 3] = readerD[3].ToString();
sheetD.Cells[doctor, 4] = readerD[4].ToString();
sheetD.Cells[doctor, 5] = readerD[5].ToString();
doctor++;
}
readerD.Close();
sheetD.Columns.AutoFit();
sheetD.Rows.AutoFit();
//Пациенты
var sheetP = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[2];
sheetP.Name = "patient";
var cellsP = (Microsoft.Office.Interop.Excel.Range)sheetP.Cells;
cellsP.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
sheetP.Cells[1, 1] = "num_patient";
sheetP.Cells[1, 2] = "surname_patient";
sheetP.Cells[1, 3] = "name_patient";
sheetP.Cells[1, 4] = "middlename_patient";
sheetP.Cells[1, 5] = "day_patient";
sheetP.Cells[1, 6] = "month_patient";
sheetP.Cells[1, 7] = "year_patient";
sheetP.Cells[1, 8] = "job_patient";
string queryP = "SELECT * FROM patient";
OleDbCommand commandP = new OleDbCommand(queryP, myConnection);
OleDbDataReader readerP = commandP.ExecuteReader();
int patient = 2;
while (readerP.Read())
{
sheetP.Cells[patient, 1] = readerP[1].ToString();
sheetP.Cells[patient, 2] = readerP[2].ToString();
sheetP.Cells[patient, 3] = readerP[3].ToString();
sheetP.Cells[patient, 4] = readerP[4].ToString();
sheetP.Cells[patient, 5] = readerP[5].ToString();
sheetP.Cells[patient, 6] = readerP[6].ToString();
sheetP.Cells[patient, 7] = readerP[7].ToString();
sheetP.Cells[patient, 8] = readerP[8].ToString();
patient++;
}
readerP.Close();
sheetP.Columns.AutoFit();
sheetP.Rows.AutoFit();
//Билеты
var sheetT = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[3];
sheetT.Name = "ticket";
var cellsT = (Microsoft.Office.Interop.Excel.Range)sheetT.Cells;
cellsT.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
sheetT.Cells[1, 1] = "id_doctors";
sheetT.Cells[1, 2] = "id_patients";
sheetT.Cells[1, 3] = "day_ticket";
sheetT.Cells[1, 4] = "month_ticket";
sheetT.Cells[1, 5] = "year_ticket";
sheetT.Cells[1, 6] = "hour_ticket";
sheetT.Cells[1, 7] = "minute_ticket";
string queryT = "SELECT id_doctors, id_patients, day_ticket, month_ticket, year_ticket, hour_ticket, minute_ticket FROM ticket";
OleDbCommand commandT = new OleDbCommand(queryT, myConnection);
OleDbDataReader readerT = commandT.ExecuteReader();
int ticket = 2;
while (readerT.Read())
{
sheetT.Cells[ticket, 1] = readerT[0].ToString();
sheetT.Cells[ticket, 2] = readerT[1].ToString();
sheetT.Cells[ticket, 3] = readerT[2].ToString();
sheetT.Cells[ticket, 4] = readerT[3].ToString();
sheetT.Cells[ticket, 5] = readerT[3].ToString();
sheetT.Cells[ticket, 6] = readerT[3].ToString();
sheetT.Cells[ticket, 7] = readerT[3].ToString();
ticket++;
}
readerT.Close();
sheetT.Columns.AutoFit();
sheetT.Rows.AutoFit();
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
xlWorkBook.SaveCopyAs(path + "/HospitalDB.xlsx");
Process[] List;
List = Process.GetProcessesByName("EXCEL");
foreach (Process proc in List)
{
proc.Kill();
}
MessageBox.Show("База сохранена на рабочем столе");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public Attachment_6()
{
excelApp = new Excel.Application();
wordApp = new Word.Application();
}
public BianziADD()
{
excelApp = new Excel.Application();
wordApp = new Word.Application();
}
private void Export_verification(List <OrgEvent> verificList)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkBook = xlApp.Workbooks.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Templates\\template_verification_list.xls"), 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
private void listBox1_MouseDoubleClick(object sender, MouseEventArgs e)
{
try
{
string item = listBox1.SelectedItem.ToString();
textBox1.Text = dirname + "\\AudioFile\\" + item + ".wav";
string fullnamefile = dirname + "\\SaveInfo\\" + item;
string m_namefile_xls = fullnamefile + "_m.xls";
string t_namefile_xls = fullnamefile + "_t.xls";
//Звернення до створених .xls фалів
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(m_namefile_xls);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
Microsoft.Office.Interop.Excel.Application xlApp_2 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook_2 = xlApp_2.Workbooks.Open(t_namefile_xls);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet_2 = xlWorkbook_2.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange_2 = xlWorksheet_2.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int rowCount_2 = xlRange_2.Rows.Count;
int colCount_2 = xlRange_2.Columns.Count;
//Заповнення масиву music даними створеного _m.xls файлу
music = new string[rowCount, colCount];
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
music[i - 1, j - 1] = xlRange.Cells[i, j].Value2.ToString();
}
}
}
//Заповнення масиву times даними створеного _t.xls файлу
time = new string[rowCount_2, colCount_2];
for (int i = 1; i <= rowCount_2; i++)
{
for (int j = 1; j <= colCount_2; j++)
{
if (xlRange_2.Cells[i, j] != null && xlRange_2.Cells[i, j].Value2 != null)
{
time[i - 1, j - 1] = xlRange_2.Cells[i, j].Value2.ToString();
}
}
}
//Закриття та зупинка всіх процесіх Microsoft Office Excel
xlWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
xlWorkbook_2.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
xlApp.Application.Quit();
xlApp_2.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp_2);
xlApp = null;
xlApp_2 = null;
xlWorkbook = null;
xlWorkbook_2 = null;
xlWorksheet = null;
xlWorksheet_2 = null;
System.GC.Collect();
//Створення сконвертованого масиву time_c - типу double, с масиву time - типу string
double[] time_c = new double[time.GetLength(0)];
for (int i = 0; i < time.GetLength(0); i++)
{
for (int j = 0; j < time.GetLength(1); j++)
{
time_c[i] = Convert.ToDouble(time[i, j]);
}
}
//Створення осного масиву dt - типу double, зі змененними значеннями масиву time_c, для використання в грі
dt = new double[time_c.Length];
dt[0] = time_c[0] / 44100 * 1000;
for (int i = 1; i < time_c.Length; i++)
{
dt[i] = (time_c[i] / 44100 * 1000) - (time_c[i - 1] / 44100 * 1000);
}
//Створення осного масиву game_music - типу string, зі змененними значеннями масиву music, для використання в грі
game_music = new string[music.GetLength(0), music.GetLength(1) - 2];
for (int i = 0; i < music.GetLength(0); i++)
{
for (int j = 0; j < music.GetLength(1) - 2; j++)
{
if (music[i, j] == null)
{
game_music[i, j] = "null";
}
else
{
game_music[i, j] = music[i, j].Remove(1, music[i, j].Length - 1);;
}
}
}
audio_file_path = dirname + "\\AudioFile\\" + item + ".wav";
namefile_xls_m = dirname + "\\SaveInfo\\" + item + "_m" + ".xls";
namefile_xls_t = dirname + "\\SaveInfo\\" + item + "_t" + ".xls";
//Перевірка створення .xls файлів
if (File.Exists(namefile_xls_m))
{
MessageBox.Show("Файл music бул створений", "MassegaBox", MessageBoxButtons.OK);
//Close();
}
else
{
MessageBox.Show("Файл music не існує", "MassegaBox", MessageBoxButtons.OK);
//Close();
}
if (File.Exists(namefile_xls_t))
{
MessageBox.Show("Файл times бул створений", "MassegaBox", MessageBoxButtons.OK);
//Close();
}
else
{
MessageBox.Show("Файл times не існує", "MassegaBox", MessageBoxButtons.OK);
//Close();
}
if (dt != null && game_music != null)
{
MessageBox.Show("Створення ключових даних та занесення композиції до списку завершено успішно", "MassegaBox", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("Помилка при створенні. Масиви - порожні", "MassegaBox", MessageBoxButtons.OK);
}
}
catch (Exception) { }
button3.Show();
}
void ReadFile(string adresses_path)
{
Getter_adresses.Clear();
if (adresses_path.Contains(".txt"))
{
StreamReader sr = new StreamReader(adresses_path, System.Text.Encoding.Default);
string allText = sr.ReadToEnd();
string[] MBAddresses = allText.Split(new char[] { ' ', '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string li in MBAddresses)
{
Getter_adresses.Add(li);
}
sr.Close();
}
if (adresses_path.Contains(".doc") || adresses_path.Contains(".docx"))
{
try
{
Microsoft.Office.Interop.Word.Application word_app = null;
word_app = new Microsoft.Office.Interop.Word.Application();
word_app.Visible = false;
Document word_doc = word_app.Documents.Open(adresses_path);
Thread t = new Thread(new ThreadStart(() =>
{
for (int i = 0; i < word_doc.Paragraphs.Count; i++)
{
string[] temp = word_doc.Paragraphs[i + 1].Range.Text.Split(new char[] { ' ', '\n', '\r' },
StringSplitOptions.RemoveEmptyEntries);
foreach (var item in temp)
{
if (item.Contains("@"))
{
Getter_adresses.Add(item);
}
}
}
word_doc.Close();
word_app.Quit();
}));
t.IsBackground = false;
t.Start();
t.Join();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (adresses_path.Contains(".xls") || adresses_path.Contains(".xlsx"))
{
try
{
Stats.count = 0;
Microsoft.Office.Interop.Excel._Application excel_app = null;
excel_app = new Microsoft.Office.Interop.Excel.Application();
excel_app.Visible = false;
_Workbook excel_workbook = excel_app.Workbooks.Open(adresses_path);
_Worksheet excel_worksheet = excel_workbook.Sheets[1];
excel_worksheet = excel_workbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range excel_range = excel_worksheet.UsedRange;
ScanExcl se = new ScanExcl(excel_app, excel_workbook, excel_worksheet, excel_range);
se.ShowDialog();
Getter_adresses = MessageWithData.Getter_Addresses;
excel_app.Quit();
try
{
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
GC.Collect();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (adresses_path.Contains(".accdb") || adresses_path.Contains(".mdb"))
{
string connection = $"Provider = Microsoft.Jet.OLEDB.4.0; Data Source ={adresses_path};";
//string connection = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={adresses_path};";
OleDbConnection myConnection = new OleDbConnection(connection);
myConnection.Open();
OleDbCommand coommand = new OleDbCommand("SELECT Emails_Col FROM Emails", myConnection);
OleDbDataReader reader = coommand.ExecuteReader();
Getter_adresses.Clear();
while (reader.Read())
{
int AddressIndex = reader.GetOrdinal("Emails_Col");
Getter_adresses.Add(reader.GetString(AddressIndex));
}
myConnection.Close();
}
}
public void DataTableToExcel(System.Data.DataTable tmDataTable, string strFileName)
{
WaitingForm wtForm = new WaitingForm();
waitingBool = true;
wtForm.Show();
if (strFileName == null)
{
return;
}
int RowNum = tmDataTable.Rows.Count;
int ColumnNum = tmDataTable.Columns.Count;
int RowIndex = 2;
int ColumnIndex = 0;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
//打开Excel应用
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //创建一个Excel文件
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//拿到那个工作表
//foreach (DataColumn dc in tmDataTable.Columns)
//{
// ColumnIndex++;
// worksheet.Cells[RowIndex, ColumnIndex] = dc.ColumnName;
//}
//给两列写列名
worksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
worksheet.Columns.ColumnWidth = 20;
Microsoft.Office.Interop.Excel.Range mergeRange;
mergeRange = worksheet.get_Range("A1", "B1");
mergeRange.Merge(0);
if (comboBox4.Text == "")
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text;
}
else
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
}
worksheet.Cells[2, 1] = "值";
worksheet.Cells[2, 2] = "时间";
//添加寄存器对应的监控点名称
for (int i = 0; i < RowNum; i++)
{
RowIndex++;
ColumnIndex = 0;
for (int j = 1; j < 3; j++)
{
ColumnIndex++;
worksheet.Cells[RowIndex, ColumnIndex] = tmDataTable.Rows[i][j].ToString();
}
}
waitingBool = false;
workbook.SaveCopyAs(strFileName + ".xlsx");
MessageBox.Show("Excle表格导出成功,保存为" + strFileName);
//退出关闭EXCLE.EXE线程
xlapp.Quit();
IntPtr t = new IntPtr(xlapp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
public void saveXls(string FileName)
{
var excel = new Microsoft.Office.Interop.Excel.Application();
try
{
if (File.Exists(FileName))
{
excel.Workbooks.Open(FileName, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing);
}
else
{
excel.SheetsInNewWorkbook = 1;
excel.Workbooks.Add(Type.Missing);
excel.Workbooks[1].SaveAs(FileName, XlFileFormat.xlExcel8,
Type.Missing,
Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
Sheets excelsheets = excel.Workbooks[1].Worksheets;
var excelworksheet = (Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells.Clear();
Microsoft.Office.Interop.Excel.Range excelcells = excelworksheet.get_Range("A1", "F1");
excelcells.Merge(Type.Missing);
excelcells.Font.Bold = true;
string title = "Список выполненных заявок за период с " + dateTimePickerFrom.Text + " по " + dateTimePickerTo.Text;
excelcells.Value2 = title;
excelcells.RowHeight = 40;
excelcells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelcells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelcells.Font.Name = "Times New Roman";
excelcells.Font.Size = 14;
for (int j = 0; j < dataGridViewReport.Columns.Count - 1; j++)
{
excelcells = excelworksheet.get_Range("A3", "A3");
excelcells = excelcells.get_Offset(0, j);
excelcells.ColumnWidth = 15;
excelcells.Value2 = dataGridViewReport.Columns[j + 1].HeaderCell.Value.ToString();
excelcells.Font.Bold = true;
}
for (int i = 0; i < dataGridViewReport.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewReport.Columns.Count - 1; j++)
{
excelcells = excelworksheet.get_Range("A4", "A4");
excelcells = excelcells.get_Offset(i, j);
excelcells.ColumnWidth = 20;
excelcells.Value2 = dataGridViewReport.Rows[i].Cells[j + 1].Value.ToString();
}
}
List <string> words = new List <string>();
string[] sum = { "", "", "", "", "Итого:" };
words.AddRange(sum);
words.Add(textBoxItog.Text);
for (int j = 0; j < words.Count; j++)
{
excelcells = excelworksheet.get_Range("A3", "A3");
excelcells = excelcells.get_Offset(dataGridViewReport.Rows.Count + 1, j);
excelcells.ColumnWidth = 25;
excelcells.Value2 = words[j].ToString();
excelcells.Font.Bold = true;
}
excel.Workbooks[1].Save();
excel.Workbooks[1].Close();
}
catch (Exception)
{
}
finally
{
excel.Quit();
}
}
public void SaveDataToExcel()
{
Microsoft.Office.Interop.Excel.Application app = null;
try
{
string log = Program.infoResource.GetLocalizedString(language.InfoId.DriveGraphData);
app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.NoOffice));
return;
}
// app.Visible = false;
//开启等待窗口
SplashScreenManager.ShowForm(this.ParentForm, typeof(wfMain), false, true);
//创建Excel中的工作薄
Workbook wb = (Workbook)app.Workbooks.Add(Missing.Value);
//创建Excel工作薄中的第一页 sheet
Worksheet sheet = (Worksheet)wb.ActiveSheet;
sheet.Name = "紫外设备";
sheet.Cells[1, 1] = "紫外";
sheet.Cells[2, 1] = "紫外背景光谱";
sheet.Cells[2, 2] = "遥测光谱";
sheet.Cells[2, 3] = "NO吸光度";
sheet.Cells[2, 4] = "HC吸光度";
sheet.Cells[2, 5] = "遥测吸光度";
//合并A1-G1,A1-G2标题居中，字号调整，字体调整
sheet.Range["A1", "E1"].Merge();
sheet.Range["A1", "E1"].ColumnWidth = 17;
Range rg = sheet.Range["A1", "E2"];
rg.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rg.Font.Size = 14;
rg.Font.Name = "宋体";
//导入数据
if (ResultDataViewModel.VM.ExhaustDetailData.UVSCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSCalParam.ZeroIntensity, 1);
}
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSData, 2);
if (ResultDataViewModel.VM.ExhaustDetailData.UVSNOCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSNOCalParam.Absorb, 3);
}
if (ResultDataViewModel.VM.ExhaustDetailData.UVSHCCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSHCCalParam.Absorb, 4);
}
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.AbsorbData, 5);
Worksheet wsRed = (Worksheet)wb.Sheets.Add(Missing.Value, sheet, Missing.Value, Missing.Value);
wsRed.Name = "红外设备";
wsRed.Cells[1, 1] = "红外";
wsRed.Cells[2, 1] = "红外背景光谱";
wsRed.Cells[2, 2] = "CO谐波";
wsRed.Cells[2, 3] = "CO2谐波";
wsRed.Cells[2, 4] = "红外背景谐波";
wsRed.Cells[2, 5] = "遥测谐波";
//合并A1-G1
wsRed.Range["A1", "E1"].Merge();
wsRed.Range["A1", "E1"].ColumnWidth = 17;
//A1-G2标题居中，字号调整，字体调整
Range rg2 = wsRed.Range["A1", "E2"];
rg2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rg2.Font.Size = 14;
rg2.Font.Name = "宋体";
//导入数据
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam.ZeroIntensity, 1);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCOCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCOCalParam.Harm, 2);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCO2CalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCO2CalParam.Harm, 3);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam.Harm, 4);
}
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.HarmData, 5);
//保存工作表,退出
SplashScreenManager.CloseForm();
dlgSave.ShowDialog();
if (!String.IsNullOrEmpty(dlgSave.FileName))
{
app.ActiveWorkbook.SaveAs(dlgSave.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
log += Program.infoResource.GetLocalizedString(language.InfoId.OpearteSuccess);
ErrorLog.SystemLog(DateTime.Now, log);
}
//else
// XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.OperateFail));
//app.Save();
app.Quit();
}
catch (Exception ex)
{
//XtraMessageBox.Show(ex.Message+" "+ex.TargetSite.ToString());
// XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.OperateFail));
if (app != null)
{
app.Quit();
}
ErrorLog.Error(ex.ToString());
}
}
public _ExcelToWord()
{
excelApp = new Excel.Application();
wordApp = new Word.Application();
}
private List <Verification> Parse(List <Address> addressList)
{
label_wait.Visible = true;
label_wait.Text = "Считывание и обработка данных...";
var rowNumber = 0;
var result = new List <Verification>();
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(textBox_path.Text, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
_Worksheet xlWorksheet = (_Worksheet)xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
Verification verification = null;
Counter prevCounter = null;
progressBar1.Maximum = xlRange.Rows.Count;
for (int i = 1; i < xlRange.Rows.Count; i++)
{
rowNumber = i;
var addressCell = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 4];
if (addressCell.Font.Bold == true && !string.IsNullOrEmpty(addressCell.Value))
{
AddressShort shortAddress = ParseAddress(addressCell.Value.ToString());
if (verification != null)
{
result.Add(verification);
verification = null;
}
if (shortAddress == null)
{
continue;
}
var exist = addressList.FirstOrDefault(w => w.Street == shortAddress.Street &&
w.House == shortAddress.House && w.Apartment ==
shortAddress.Apartment);
if (exist == null)
{
continue;
}
verification = new Verification
{
Address = addressCell.Value.ToString(),
Counters = new List <Counter>()
};
}
var typeCell = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 2];
if (verification != null)
{
var row = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 1];
if (string.IsNullOrEmpty(row.Value) || !Regex.IsMatch(row.Value.ToString(), "^[\\d]{1,2}$"))
{
continue;
}
var counter = new Counter();
counter.Name = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 2])?.Value == null ? prevCounter?.Name : ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 2])?.Value.ToString();
counter.StartCount = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 12]).Value.ToString();
counter.EndCount = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 13]).Value.ToString();
counter.StartDate = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 14]).Value.ToString();
counter.EndDate = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowNumber, 15]).Value.ToString();
verification.Counters.Add(counter);
prevCounter = counter;
}
progressBar1.PerformStep();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
MessageBox.Show(ex.Message);
}
return(result);
}
private void Export_revision(List <Verification> verificationList, List <OrgEvent> verificList)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkBook = xlApp.Workbooks.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Templates\\template_verification_list.xls"), 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
_Worksheet xlWorksheet = (_Worksheet)xlWorkBook.Sheets[1];
int startRow = 5;
progressBar1.Minimum = 0;
progressBar1.Value = 0;
progressBar1.Maximum = verificationList.Count + verificList.Count;
label_wait.Text = "Запись данных в файл...";
foreach (var verification in verificationList)
{
var start = startRow;
var end = startRow + verification.Counters.Count - 1;
var startCell = xlWorksheet.Cells[start, 1];
var endCell = xlWorksheet.Cells[end, 1];
var range = xlWorksheet.Range[startCell, endCell];
range.Merge(Type.Missing);
range.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.BorderAround();
xlWorksheet.Cells[startRow, 1] = verification.Address;
foreach (var counter in verification.Counters)
{
xlWorksheet.Cells[startRow, 2] = counter.Name;
// xlWorksheet.Cells[startRow, 3] = counter.StartCount.Insert(counter.StartCount.Length - 3, ",");
xlWorksheet.Cells[startRow, 3] = counter.StartCount.ToString();
xlWorksheet.Cells[startRow, 4] = counter.EndCount.ToString();
xlWorksheet.Cells[startRow, 5] = counter.StartDate;
xlWorksheet.Cells[startRow, 6] = counter.EndDate;
var rng = xlWorksheet.Range[xlWorksheet.Cells[startRow, 2], xlWorksheet.Cells[startRow, 6]];
rng.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
startRow++;
}
progressBar1.PerformStep();
}
xlWorksheet = (_Worksheet)xlWorkBook.Sheets[2];
startRow = 5;
foreach (var ver in verificList)
{
var start = startRow;
var end = startRow + ver.Count;
var startCell = xlWorksheet.Cells[start, 1];
var endCell = xlWorksheet.Cells[end, 1];
var range = xlWorksheet.Range[startCell, endCell];
range.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//range.Borders.LineStyle = XlLineStyle.xlContinuous;
(range.Cells as Microsoft.Office.Interop.Excel.Range).ColumnWidth = 50;
//range.BorderAround();
var counterType = String.Empty;
switch (ver.CounterType)
{
case Models.CounterType.COLD:
counterType = "Холодная вода";
break;
case Models.CounterType.HOT:
counterType = "Горячая вода";
break;
case Models.CounterType.ELECTRO:
counterType = "Электрический";
break;
}
xlWorksheet.Cells[startRow, 1] = "ул. " + MongoRepositoryAddresses.Get(ver.AddressId).Street + ", дом "
+ MongoRepositoryAddresses.Get(ver.AddressId).House + ", кв. "
+ MongoRepositoryAddresses.Get(ver.AddressId).Apartment;
xlWorksheet.Cells[startRow, 2] = counterType;
xlWorksheet.Cells[startRow, 3] = new DateTime(ver.DateTime).ToShortDateString();
var rng = xlWorksheet.Range[xlWorksheet.Cells[startRow, 2], xlWorksheet.Cells[startRow, 3]];
rng.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
startRow++;
progressBar1.PerformStep();
}
label_wait.Text = "Документ построен.";
xlApp.Visible = true;
}
