private void button1_Click(object sender, EventArgs e)
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(path);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
BindingList<Dompet> DompetList = new BindingList<Dompet>();
for (int index = 2; index <= lastrow; index++)
{
System.Array MyValues =
(System.Array)MySheet.get_Range
("A" + index.ToString(),"F" + index.ToString()).Cells.Value;
DompetList.Add(new Dompet {
JmlPemesanan = MyValues.GetValue(1,1).ToString(),
JmlPekerja = MyValues.GetValue(1,2).ToString(),
Peralatan = MyValues.GetValue(1,3).ToString(),
JenisKulit = MyValues.GetValue(1,4).ToString(),
ModelDompet = MyValues.GetValue(1,5).ToString(),
Prediksi = MyValues.GetValue(1,6).ToString()
});
}
dataGridView1.DataSource = (BindingList<Dompet>)DompetList;
dataGridView1.AutoResizeColumns();
}
//private void wrtBySht(List<string> workList)
private void wrtBySht(string shtName)
{
xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName);
xlWorkSht.Activate();
string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index
Excel.Range rng = xlWorkSht.get_Range(idx, idx);
DataTable dt;
if (shtName == "InstrumentClassData")
dt = Util.DbConn.SqlTsk.GetTable("procGetInstData");
else
dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName);
int j = 1;
int i = 0;
foreach (DataRow row in dt.Rows)
{
for (i = 0; i < dt.Columns.Count; i++)
{
rng[j + 1, i + 1].Value = row[i].ToString();
}
j++;
if (j > dt.Rows.Count)
{
break;
}
}
rng[j + 1, 1].Value = "end";
}
private void drawGraph()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook
while (thread1.IsAlive)
{
//son satır bulunuyor excel dosyasındaki
Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlWorkSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz
// alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
string[] HeatingData = ReceiveData.Split(':');
string[] HeatingData2 = HeatingData[1].Split('D');
var result = HeatingData2[0];
double heating = Convert.ToDouble(result);
theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");
Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor!
this.Invoke((MethodInvoker)delegate
{
chart1.Series["Series1"].Points.AddY(result);
// excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
});
}
}
catch
{
// MessageBox.Show("Dosya bulunamadı");
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Zaman";
xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";
xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
}
}
private void btnFetch_Click(object sender, RibbonControlEventArgs e)
{
activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
firstRow = activeWorksheet.get_Range("A1");
//firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
newFirstRow = activeWorksheet.get_Range("A1");
try
{
firstRow.Value2 = "Fetching Data Please Wait...";
string url = "http://localhost:5000/sample/getdata";
WebClient wc = new WebClient();
wc.DownloadStringCompleted += HttpsCompleted;
wc.DownloadStringAsync(new Uri(url));
}
catch(Exception ex)
{
firstRow.Value2 = "An Error occured while accessing the service";
}
}
public void WriteTest()
{
xlBook = xlApp.ActiveWorkbook;
xlSheet = xlBook.Worksheets.get_Item(1);
Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlSheet.get_Range("A1", last);
int lastUsedRow = last.Row + 1;
int lastUsedColumn = last.Column + 1;
Console.WriteLine("Rows used {0} Columns used {1}",lastUsedRow,lastUsedColumn );
for(int row = 1;row<10;row++)
for (int col = 1; col < 10; col++)
xlSheet.Cells[row, col] = "Test col:"+col+" row:"+row ;
}
private xlTradeOrder ReadLastOrder()
{
xlBook = xlApp.ActiveWorkbook;
xlSheet = xlBook.Worksheets.get_Item(2);
Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column + 1;
Debug.WriteLine("Rows used " + lastUsedRow);
var Order = new xlTradeOrder();
Order.TimeStamp = DateTime.UtcNow.AddHours(2);
Order.Contract = xlSheet.Cells[lastUsedRow, 1].Value;
Order.BS = (xlSheet.Cells[lastUsedRow, 2].Value == "B") ? Trade.BuySell.Buy : Trade.BuySell.Sell;
Order.Volume = (int)xlSheet.Cells[lastUsedRow, 3].Value;
Order.Price = (long)xlSheet.Cells[lastUsedRow, 4].Value;
Order.Status = StringToOrder(xlSheet.Cells[lastUsedRow, 12].Value);
Order.GetReference();
return Order;
}
public void WriteOrder(xlTradeOrder Order)
{
xlBook = xlApp.ActiveWorkbook;
xlSheet = xlBook.Worksheets.get_Item(2);
Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlSheet.get_Range("A1", last);
int lastUsedRow = last.Row + 1;
int lastUsedColumn = last.Column + 1;
//Debug.WriteLine("Rows used " + lastUsedRow);
xlSheet.Cells[lastUsedRow, 1] = Order.Contract;
xlSheet.Cells[lastUsedRow, 2] = (Order.BS == Trade.BuySell.Buy) ? "B" : "S";
xlSheet.Cells[lastUsedRow, 3] = Order.Volume;
xlSheet.Cells[lastUsedRow, 4] = Order.Price;
xlSheet.Cells[lastUsedRow, 5] = Order.Principle;
xlSheet.Cells[lastUsedRow, 6] = Order.Dealer;
xlSheet.Cells[lastUsedRow, 9] = Order.Member;
xlSheet.Cells[lastUsedRow, 10] = Order.Type;
xlSheet.Cells[lastUsedRow, 11] = Order.Exchange;
xlSheet.Cells[lastUsedRow, 12] = OrderToString(xlTradeOrder.orderStatus.Ready);
_lastOrderMatched = false;
}
public List<string> ReadTest()
{
xlBook = xlApp.ActiveWorkbook;
xlSheet = xlBook.Worksheets.get_Item(1);
Excel.Range last = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column + 1;
Console.WriteLine("Rows used {0} Columns used {1}", lastUsedRow, lastUsedColumn);
var lines = new List<string>();
for (int row = 1; row <lastUsedRow; row++)
{
var cols = new StringBuilder();
for (int col = 1; col < lastUsedColumn; col++)
{
cols.Append(xlSheet.Cells[row, col].Value+",");
}
lines.Add(cols.ToString());
}
return lines;
}
private void button1_Click(object sender, EventArgs e)
{
button1.Enabled = false;
ADD(comboBox1, "khh.ini");
ADD(comboBox2, "fkdw.ini");
ADD(comboBox3, "zh.ini");
ADD(comboBox4, "skh.ini");
ADD(comboBox5, "skmc.ini");
ADD(comboBox6, "yt.ini");
Write("name.txt", textBox4.Text);
//Microsoft.Office.Interop.Excel._Workbook wbook = Globals.ThisAddIn.Application.ActiveWorkbook;//获取激活的工作簿
// group1.Label = "当前共有 "+wbook.Sheets.Count.ToString()+" 张表\r\n自动引导至第一张表，表名为："+wbook.Sheets[1].Name;//获取第一个工作表;
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
newWorksheet.Cells.NumberFormat = "@";
// newWorksheet.Name = "Sheet1";
// Worksheet worksheet = wbook.Worksheets[1];//获取名为sheet1的工作表
// Microsoft.Office.Interop.Excel.Worksheet newWorkbook = Globals.ThisAddIn.Application.Worksheets.Add(System.Type.Missing, worksheet);
SetWidth("A", 6.5);
SetWidth("B", 16.88);
SetWidth("C", 11);
SetWidth("D", 22.63);
SetWidth("E", 10.75);
SetWidth("F", 20.25);
SetMerg("A1", "F1");
SetMerg("A6", "C6");
SetMerg("A5", "B5");
SetMerg("D6", "F6");
SetMerg("D8", "E8");
SetMerg("E9", "F9");
SetFont("A1", "宋体", 16, "安徽青阳农商行" + textBox2.Text + "大额资金支付审批表", true, true, 34.5);
SetFont("E2", "仿宋_GB2312", 12, "划款日期", false, true, 23.25);
SetFont("F2", "仿宋_GB2312", 12, textBox2.Text, false, true, 23.25);
SetFont("A3", "仿宋_GB2312", 12, "开户行", false, true, 50.25);
SetFont("C3", "仿宋_GB2312", 12, "付款单位", false, true, 50.25);
SetFont("E3", "仿宋_GB2312", 12, "账号", false, true, 50.25);
SetFont("B3", "仿宋_GB2312", 12, comboBox1.Text, false, true, 50.25, true);
SetFont("D3", "仿宋_GB2312", 12, comboBox2.Text, false, true, 50.25, true);
SetFont("F3", "仿宋_GB2312", 12, "'" + comboBox3.Text, false, true, 50.25, true);
SetFont("A4", "仿宋_GB2312", 12, "收款行", false, true, 50.25);
SetFont("B4", "仿宋_GB2312", 12, comboBox4.Text, false, true, 50.25);
SetFont("C4", "仿宋_GB2312", 12, "收款人名称", false, true, 50.25);
SetFont("D4", "仿宋_GB2312", 12, comboBox5.Text, false, true, 50.25, true);
SetFont("E4", "仿宋_GB2312", 12, "用途", false, true, 50.25);
SetFont("F4", "仿宋_GB2312", 12, comboBox6.Text, false, true, 50.25);
SetFont("A5", "仿宋_GB2312", 12, "金额（万元）", false, true, 48.75);
SetFont("C5", "仿宋_GB2312", 12, "大写", false, true, 48.75, true);
SetFont("E5", "仿宋_GB2312", 12, "小写", false, true, 48.75);
SetFont("A6", "仿宋_GB2312", 12, "支行审批意见：", false, true, 67.50);
SetFont("D8", "仿宋_GB2312", 14, "支行行长或副行长签字：", false, true, 26.25);
SetFont("D5", "仿宋_GB2312", 12, textBox3.Text, false, true, 48.75, true);
SetFont("F5", "仿宋_GB2312", 12, textBox1.Text, false, true, 48.75, true);
SetFont("E9", "仿宋_GB2312", 14, textBox2.Text, false, true, 18.75, true);
Microsoft.Office.Interop.Excel.Range range = newWorksheet.get_Range("A3", "F6");
// range.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternCrissCross;
// range.Borders.Weight = 1;
range.Cells.Borders.LineStyle = 1;
// range.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
// range.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
// range.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
// range.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
// range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
button1.Enabled = true;
newWorksheet.PageSetup.TopMargin = 1.3f;
newWorksheet.PageSetup.BottomMargin = 1.3f;
newWorksheet.PageSetup.LeftMargin = 1.2f;
newWorksheet.PageSetup.RightMargin = 0.4f;
newWorksheet.PageSetup.CenterHorizontally = true;
// MessageBox.Show(Globals.ThisAddIn.Application.ActiveWorkbook.FullName);
this.Close();
}
public void Spread(Excel.Worksheet sheet)
{
_sheet = sheet;
var tokenArray = _token;
var objectList = tokenArray
.Where(x => x.Type == JTokenType.Object)
.Select((x, i) => new { Index = i, Object = (JsonObject)x.CreateJsonToken() })
.ToList();
var titleColumnDic = objectList
.SelectMany(x => x.Object.Keys)
.Distinct()
.Select((x, i) => new { Column = i, Title = x })
.ToDictionary(x => x.Title, x => x.Column);
Excel.Range minCell = _sheet.Cells[_titleRow, 1];
Excel.Range maxCell = _sheet.Cells[objectList.Count + _titleRow, titleColumnDic.Count];
var rowsCount = maxCell.Row - minCell.Row + _titleRow;
var columnsCount = maxCell.Column - minCell.Column + 1;
var data = new object[rowsCount, columnsCount];
titleColumnDic.OrderBy(x => x.Value).ForEach(x =>
{
var row = 0;
var column = x.Value;
var title = x.Key;
data[row, column] = title;
_cellDataList.Add(new CellData
{
Cell = _sheet.Cells[_titleRow, column + 1],
Key = new JsonTitle(title),
Type = DataType.Title,
});
});
objectList.ForEach(x =>
{
var row = x.Index + 1;
x.Object.Keys.ForEach(key =>
{
var column = titleColumnDic[key];
var jsonToken = x.Object.GetJsonToken(key);
if (jsonToken != null)
{
data[row, column] = jsonToken.ToValue();
_cellDataList.Add(new CellData
{
Index = x.Index,
Cell = _sheet.Cells[row + _titleRow, column + 1],
Value = jsonToken,
Type = DataType.Value,
});
}
});
});
var range = _sheet.get_Range(minCell.Address, maxCell.Address);
range.Value2 = data;
}
/// <summary>
/// Метод создания и сохранения документов
/// в форматах Microsoft Word (doc, PDF),
/// Excel (exls)
/// </summary>
/// <param name="type">Тип создаваемого документа
/// отчёт или статистика</param>
/// <param name="format">Формат сохранения
/// документ или таблица</param>
/// <param name="name">Название документа</param>
/// <param name="table">Входная таблица с данными</param>
public void Document_Create(Document_Type type,
Document_Format format, string name,
DataTable table)
{
//Получение данных о конфигурации документа
Configuration_class configuration_Class
= new Configuration_class();
configuration_Class.Document_Configuration_Get();
//Проверка на пустоту названия
switch (name != "" || name != null)
{
case true:
//Выбор формата либо Word либо Excel
switch (format)
{
case Document_Format.Word:
//Запуск процесса в дистпечере задач
word.Application application
= new word.Application();
//создание документа в процессе
word.Document document
//Присвоение документа процессу, Visible: true
//возможность редактирования документа
= application.Documents.Add(Visible: true);
try
{
//Объявление дипапазона для формирования текста
word.Range range = document.Range(0, 0);
//89Настройка отступов в документе
document.Sections.PageSetup.LeftMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Left_Merge);
document.Sections.PageSetup.TopMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Top_Merge);
document.Sections.PageSetup.RightMargin
= application.
CentimetersToPoints((float)
Configuration_class.doc_Right_Merge);
document.Sections.PageSetup.BottomMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Bottom_Merge);
//Присвоение текстового знеачения в дипазон
range.Text =
Configuration_class.Organiztion_Name;
//Настройка выравнивания текста
range.ParagraphFormat.Alignment =
word.WdParagraphAlignment.
wdAlignParagraphCenter;
//Настройка интервала после абзаца
range.ParagraphFormat.SpaceAfter = 1;
//Настройка интервала перед абзаца
range.ParagraphFormat.SpaceBefore = 1;
//Настройка межстрочного интервала
range.ParagraphFormat.LineSpacingRule
= word.WdLineSpacing.wdLineSpaceSingle;
//Настройка названия шрифта
range.Font.Name = "Times New Roman";
//Настройка размера шрифта
range.Font.Size = 12;
//Добавление параграфов
document.Paragraphs.Add(); //В конце текста
document.Paragraphs.Add(); //Свободный
document.Paragraphs.Add(); //Для будущего текста
//Параграф для названия документа
word.Paragraph Document_Name
= document.Paragraphs.Add();
//Настройка параграфа через свойство диапазона
Document_Name.Format.Alignment
= word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
//Проверка на тип документа, отчёт или статистика
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type.Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//Добавление таблицы в область документа
//Указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range,
table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment
= word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment =
word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документп");
break;
}
}
public string[,] CzytajDane()
{
m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;
m_xlWorkbook = m_xlApp.Workbooks.Open(m_xlFileName,
m_xx, m_xx, m_xx, m_xx, m_xx, m_xx, m_xx,
m_xx, m_xx, m_xx, m_xx, m_xx, m_xx, m_xx);
m_xlWorksheet = (Excel.Worksheet)m_xlWorkbook.Worksheets[1]; // 0 wskazuje na pierwszy arkusz
string startCell = "A1"; // zakres danych do wczytania
string endCell = "F6";
m_projectRange = m_xlWorksheet.get_Range(startCell, endCell);
Array projectCells = (Array)m_projectRange.Cells.Value2;
int col = m_projectRange.Columns.Count;
int row = m_projectRange.Rows.Count;
string[,] tab1 = new string[col, row];
for (int i = 0; i < col; i++)
{
for (int j = 0; j < row; j++)
{
tab1[i, j] = " " + projectCells.GetValue(i + 1, j + 1);
}
}
m_xlApp.Quit();
Console.Write("Wczytana tablica z pliku Excela BIJACZ \n");
for (int i = 0; i < col; i++)
{
for (int j = 0; j < row; j++)
{
Console.Write(tab1[i, j] + "\t");
}
Console.WriteLine();
}
return tab1;
}
public static List<OffsetItem> LoadOffset(string filePath)
{
List<OffsetItem> ots = new List<OffsetItem>();
int sheetNo = 4;
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(filePath);
MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
for (int index = 1; index <= lastRow; index++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "T" + index.ToString()).Cells.Value;
if (MyValues.GetValue(1, 2) == null && MyValues.GetValue(1, 3) == null)
{
Console.WriteLine("OffsetItem no value on row " + index.ToString());
WriteError(MyValues, string.Format(""), false);
continue;
}
RowIndex = 1;
ots.Add(new OffsetItem
{
Apply_Number = GetValue(MyValues),
Worker_Number = GetValue(MyValues),
Worker_CnName = GetValue(MyValues),
Worker_Dept = GetValue(MyValues),
Worker_Group = GetValue(MyValues),
CreateEd = GetValue(MyValues),
Offset_StartEd = GetValue(MyValues),
Offset_StartTime = GetValue(MyValues),
Offset_EndEd = GetValue(MyValues),
Offset_EndTime = GetValue(MyValues),
Offset_Hours = GetValue(MyValues),
Offset_Days = GetValue(MyValues),
App1 = GetValue(MyValues),
App2 = GetValue(MyValues),
OT_Numbers = GetValue(MyValues),
OTOffset_Hours = GetValue(MyValues),
//如果没有，可以为空，系统导入时自动生成
Offset_Number = GetValue(MyValues),
Cycle_StartEd = GetValue(MyValues),
Cycle_EndEd = GetValue(MyValues),
Status = GetValue(MyValues)
});
}
return ots;
}
/// <summary>
/// Create the excel file
/// </summary>
/// <returns>Message to display to user</returns>
internal KeyValuePair<String, Boolean> createExcelFile()
{
excelApplication = new Microsoft.Office.Interop.Excel.Application();
if (excelApplication == null)
{
return new KeyValuePair<string, bool>("Error : no se pudo iniciar la aplicación Excel en este equipo",false);
}
excelWorkBook = excelApplication.Workbooks.Add();
excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.ActiveSheet;
createHeader();
insertData();
String startRange = "A1";
String endRange = ("H" + dataGrid.Items.Count + 1 + 2);
Microsoft.Office.Interop.Excel.Range excelCells = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.get_Range(startRange, endRange);
excelCells.Columns.AutoFit();
return finishExcel();
}
private void btnExportExecl_Click(object sender, EventArgs e)
{
if (grvData.FocusedRowHandle < 0)
{
return;
}
string path = "";
FolderBrowserDialog f = new FolderBrowserDialog();
if (f.ShowDialog() == DialogResult.OK)
{
path = f.SelectedPath;
}
else
{
return;
}
string _pPath = Application.StartupPath + "\\ResultCheckTemplate.xls";
string orderCode = Lib.ToString(grvData.GetFocusedRowCellValue(colOrderCode));
string productCode = Lib.ToString(grvData.GetFocusedRowCellValue(colPID));
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo file kết quả kiểm tra!"))
{
DataSet ds = Lib.GetListDataFromSP("spGetProductWorkingInfo_ByOrder", new string[] { "@OrderCode", "@ProductCode" }, new object[] { orderCode, productCode });
DataTable dtProduct = ds.Tables[0];
DataTable dt = ds.Tables[1];
DataRow r1 = dt.NewRow();
dt.Rows.InsertAt(r1, 0);
int count = dt.Rows.Count;
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
string fileNameHSTK = "KetQuaKiemTra_" + orderCode + "_" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xls";
try
{
File.Copy(_pPath, path + "\\" + fileNameHSTK, true);
app = new Excel.Application();
app.Workbooks.Open(path + "\\" + fileNameHSTK);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
app.DisplayAlerts = false;
string congDoan = "";
int position = 0;
//workSheet.Cells[2, 3] = "";
workSheet.Cells[3, 2] = Lib.ToString(dtProduct.Rows[0]["ProductName"]); //tên
workSheet.Cells[5, 6] = Lib.ToString(dtProduct.Rows[0]["MotorCode"]); //mã motor
workSheet.Cells[6, 5] = Lib.ToString(dtProduct.Rows[0]["LoaiMo"]); //loại mỡ
workSheet.Cells[6, 8] = Lib.ToString(dtProduct.Rows[0]["LuongMo"]); //lượng mỡ
workSheet.Cells[2, 7] = grvData.GetFocusedRowCellDisplayText(colDateLR); //ngày lắp ráp
workSheet.Cells[7, 7] = orderCode + "-1 " + productCode;
workSheet.Cells[9, 10] = dt.Columns.Contains("1") ? orderCode + "-1" : "";
workSheet.Cells[9, 11] = dt.Columns.Contains("2") ? orderCode + "-2" : "";
workSheet.Cells[9, 12] = dt.Columns.Contains("3") ? orderCode + "-3" : "";
workSheet.Cells[9, 13] = dt.Columns.Contains("4") ? orderCode + "-4" : "";
workSheet.Cells[9, 14] = dt.Columns.Contains("5") ? orderCode + "-5" : "";
workSheet.Cells[9, 15] = dt.Columns.Contains("6") ? orderCode + "-6" : "";
for (int i = count - 1; i >= 0; i--)
{
DataRow r = dt.Rows[i];
string maCongDoan = Lib.ToString(r["ProductStepCode"]);
if (congDoan != maCongDoan)
{
((Excel.Range)workSheet.Rows[11]).Insert();
((Excel.Range)workSheet.Rows[11]).Insert();
workSheet.get_Range("A10", "Q10").Copy(workSheet.get_Range("A11", "Q11"));
if (position > 1)
{
workSheet.get_Range("A14", "A" + (13 + position)).Merge(false);
workSheet.get_Range("B14", "B" + (13 + position)).Merge(false);
}
position = 0;
congDoan = maCongDoan;
workSheet.Cells[12, 1] = maCongDoan;
workSheet.Cells[12, 2] = "";
workSheet.Cells[12, 3] = "";
workSheet.Cells[12, 8] = "";
workSheet.Cells[12, 9] = "Đánh giá";
workSheet.Cells[12, 10] = dt.Columns.Contains("StatusResult1") ? Lib.ToString(r["StatusResult1"]) : "";
workSheet.Cells[12, 11] = dt.Columns.Contains("StatusResult2") ? Lib.ToString(r["StatusResult2"]) : "";
workSheet.Cells[12, 12] = dt.Columns.Contains("StatusResult3") ? Lib.ToString(r["StatusResult3"]) : "";
workSheet.Cells[12, 13] = dt.Columns.Contains("StatusResult4") ? Lib.ToString(r["StatusResult4"]) : "";
workSheet.Cells[12, 14] = dt.Columns.Contains("StatusResult5") ? Lib.ToString(r["StatusResult5"]) : "";
workSheet.Cells[12, 15] = dt.Columns.Contains("StatusResult6") ? Lib.ToString(r["StatusResult6"]) : "";
workSheet.get_Range("C12", "I12").Merge(false);
workSheet.get_Range("A12", "Q12").Font.Size = 15;
workSheet.get_Range("A12", "Q12").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
position++;
}
((Excel.Range)workSheet.Rows[11]).Insert();
workSheet.get_Range("A10", "Q10").Copy(workSheet.get_Range("A11", "Q11"));
workSheet.Cells[12, 1] = maCongDoan;
workSheet.Cells[12, 2] = Lib.ToString(r["ProductStepName"]);
workSheet.Cells[12, 3] = Lib.ToString(r["WorkingName"]);
workSheet.Cells[12, 8] = Lib.ToString(r["PeriodValue"]);
workSheet.Cells[12, 9] = Lib.ToString(r["ValueTypeName"]);
workSheet.Cells[12, 10] = dt.Columns.Contains("1") ? Lib.ToString(r["1"]) : "";
workSheet.Cells[12, 11] = dt.Columns.Contains("2") ? Lib.ToString(r["2"]) : "";
workSheet.Cells[12, 12] = dt.Columns.Contains("3") ? Lib.ToString(r["3"]) : "";
workSheet.Cells[12, 13] = dt.Columns.Contains("4") ? Lib.ToString(r["4"]) : "";
workSheet.Cells[12, 14] = dt.Columns.Contains("5") ? Lib.ToString(r["5"]) : "";
workSheet.Cells[12, 15] = dt.Columns.Contains("6") ? Lib.ToString(r["6"]) : "";
position++;
}
for (int i = 0; i < 5; i++)
{
((Excel.Range)workSheet.Rows[10]).Delete();
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: " + ex.Message);
}
finally
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
Process.Start(path + "\\" + fileNameHSTK);
}
}
}
private void ExportDataSetToExcel(DataSet ds, string strPath)
{
int inHeaderLength = 3, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
// strPath += DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName;//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "G" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
}
//Excel Header
OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "G3");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
excelWorkSheet.Cells[1, 1] = "Picquet Move Control System";
//Style table column names
cellRang = excelWorkSheet.get_Range("A4", "G4");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
//Formate price column
excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath, Default, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
MessageBox.Show("Excel generated successfully \n As " + strPath);
}
/// <summary>
/// Читает ячейку
/// </summary>
public string readCell(string nameCell)
{
return(ObjWorkSheet.get_Range(nameCell, nameCell).Value2.ToString());
}
public void saveExcel(DataGridView dataGridView1)
{
SaveFileDialog save = new SaveFileDialog();
save.Title = "Сохранить изображение как...";
save.OverwritePrompt = true;
save.CheckPathExists = true;
save.Filter = "Excel Files|.xlsx";
if (save.ShowDialog() == DialogResult.OK)
{
try
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.ActiveSheet;
for (int j = 1; j < dataGridView1.ColumnCount; j++)
{
if (j + 1 == dataGridView1.ColumnCount)
{
worksheet.Rows[1].Columns[j] = "Изображение";
}
else
{
worksheet.Rows[1].Columns[j] = dataGridView1.Columns[j - 1].HeaderText; // заполненине заголовков.
}
}
for (int i = 2; i < dataGridView1.RowCount + 1; i++)
{
for (int j = 1; j < dataGridView1.ColumnCount - 1; j++)
{
worksheet.Rows[i].Columns[j] = dataGridView1.Rows[i - 2].Cells[j - 1].Value; // заполнение остальных ячеек
if (j == 1 || j == 4)
{
worksheet.Cells[i, j].NumberFormat = "0";
}
if (j == 5)
{
worksheet.Cells[i, j].NumberFormat = "0,000";
}
if (j == 7)
{
worksheet.Cells[i, j].NumberFormat = "0,00";
}
}
}
worksheet.Columns.AutoFit();
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
Excel.Range rg = worksheet.get_Range("A" + 1, "A" + 1);
Dataproc obj1 = new Dataproc();
obj1.readimages(dataGridView1.Rows[i].Cells[0].Value.ToString());
if (obj1.images[0].Data != null)
{
object missing = System.Reflection.Missing.Value;
byte[] bytearray = obj1.images[0].Data;
ImageConverter converter = new ImageConverter();
Image img = (Image)converter.ConvertFrom(bytearray);
Bitmap objBitmap = new Bitmap(img, new Size(400, 400));
Clipboard.SetImage(objBitmap);
Excel.Range oRange = (Excel.Range)worksheet.Cells[i + 2, 8];
rg.Rows[i + 2].RowHeight = 300;
rg.Columns[8].ColumnWidth = 56.43F;
worksheet.Paste(oRange, missing);
}
rg.Columns[8].ColumnWidth = 56.43F;
}
excelApp.AlertBeforeOverwriting = false;
workbook.SaveAs(save.FileName);
excelApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка не удалось экспортировать БД в файл Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public static void saveData(string file, DataTable data)
{
Exc.Application ObjExcel = null;
Exc.Workbook ObjWorkBook = null;
Exc.Worksheet ObjWorkSheet = null;
try
{
int[] maxLengths = new int[data.Columns.Count];
//Приложение самого Excel
ObjExcel = new Exc.Application();
//Книга.
ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ObjWorkSheet = (Exc.Worksheet)ObjWorkBook.Sheets[1];
for (int i = 0; i < data.Columns.Count; i++)
{
ObjExcel.Cells[1, i + 1] = data.Columns[i].ColumnName;
}
ObjWorkSheet.get_Range("A1", getStringCell(data.Columns.Count) + "1").HorizontalAlignment = Exc.XlHAlign.xlHAlignCenter;
ObjWorkSheet.get_Range("A1", getStringCell(data.Columns.Count) + "1").Font.Bold = true;
ObjWorkSheet.get_Range("A1", getStringCell(data.Columns.Count) + "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
ObjWorkSheet.get_Range("A1", getStringCell(data.Columns.Count) + "1").Borders.Weight = 2;
ObjWorkSheet.get_Range("A1", getStringCell(data.Columns.Count) + "1").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
for (int i = 0; i < data.Rows.Count; i++)
{
for (int j = 0; j < data.Columns.Count; j++)
{
if (data.Rows[i].ItemArray[j].ToString().Length > maxLengths[j])
{
maxLengths[j] = data.Rows[i].ItemArray[j].ToString().Length;
}
ObjExcel.Cells[i + 2, j + 1] = data.Rows[i].ItemArray[j];
}
}
for (int i = 0; i < maxLengths.Length; i++)
{
ObjExcel.get_Range(getStringCell(i + 1) + 1.ToString() + ":" + getStringCell(i + 1) + 1.ToString()).ColumnWidth = maxLengths[i] * 1.4;
}
ObjExcel.Cells[data.Rows.Count + 3, data.Columns.Count - 1] = "Дата створення:";
ObjExcel.Cells[data.Rows.Count + 3, data.Columns.Count] = DateTime.Now.ToLongDateString().ToString();
ObjWorkSheet.get_Range(getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString(), getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString()).HorizontalAlignment = Exc.XlHAlign.xlHAlignCenter;
ObjWorkSheet.get_Range(getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString(), getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString()).Font.Bold = true;
ObjWorkSheet.get_Range(getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString(), getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString()).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
ObjWorkSheet.get_Range(getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString(), getStringCell(data.Columns.Count) + (data.Rows.Count + 3).ToString()).Borders.Weight = 2;
ObjWorkSheet.get_Range(getStringCell(data.Columns.Count - 1) + (data.Rows.Count + 3).ToString(), getStringCell(data.Columns.Count) + (data.Rows.Count + 3).ToString()).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
ObjWorkBook.SaveAs(file);
}
catch (Exception ex)
{
MessageBox.Show("Помилка роботи із Excel. Код помилки: " + ex.Message, "Error");
}
finally
{
//Закрытие книгу Excel.
ObjWorkBook.Close();
//Закрытие приложения Excel.
ObjExcel.Quit();
//Обнуляем созданые объекты
ObjWorkBook = null;
ObjWorkSheet = null;
ObjExcel = null;
//Вызываем сборщик мусора для их уничтожения и освобождения памяти
GC.Collect();
string appid = "Excel.Application";
Type excelType = Type.GetTypeFromProgID(appid);
object excelAppInstance = Activator.CreateInstance(excelType);
object appWorkkbooks = excelType.InvokeMember("Workbooks", BindingFlags.GetProperty, null, excelAppInstance, null);
object workbook = appWorkkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, appWorkkbooks, new object[] { @file });
excelType.InvokeMember("Visible", BindingFlags.SetProperty, null, excelAppInstance, new object[] { true });
}
}
public static string ExportToDataGridView <T>(List <T> dataList, string exceptColumns)
{
Excel.Application excel = null;
Excel.Workbook workBook = null;
Excel.Worksheet workSheet = null;
Excel.Range range = null;
string strHeaderStart = "A1";
string strDataStart = "A2";
object optionalValue = Missing.Value;
try
{
excel = new Excel.Application();
workBook = excel.Workbooks.Add(); // 워크북 추가
workSheet = workBook.Worksheets.get_Item(1); // as Excel.Worksheet; // 엑셀 첫번째 워크시트 가져오기
//excel.Application.Workbooks.Add(true);
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
int columnIndex = 0;
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (PropertyInfo property in headerInfo)
{
if (!exceptColumns.Contains(property.Name))
{
var attribute = property.GetCustomAttributes(typeof(T), false)
.Cast <T>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ?
property.Name : attribute.ToString());
}
}
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range.Interior.Color = Color.FromArgb(51, 52, 79);
range.Font.Color = Color.White;
int rowIndex = 0;
object[,] objData = new object[rowIndex, objHeaders.Count];
foreach (T data in dataList)
{
rowIndex++;
columnIndex = 0;
foreach (PropertyInfo prop in typeof(T).GetProperties())
{
if (!exceptColumns.Contains(prop.Name))
{
columnIndex++;
if (prop.GetValue(data, null) != null)
{
excel.Cells[rowIndex + 1, columnIndex] = prop.GetValue(data, null).ToString();
}
}
}
}
range = workSheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(rowIndex, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(rowIndex + 1, objHeaders.Count);
range.WrapText = false;
range.Columns.AutoFit();
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public string GetReport()
{
try
{
object misValue = System.Reflection.Missing.Value;
_excelApp = new Excel.Application();
_excelBook = _excelApp.Workbooks.Add(misValue);
_excelSheet = (Excel.Worksheet)_excelBook.Worksheets.get_Item(1);
_excelSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
_excelApp.Cells[1, 1] = "ЗАО «Специализированная Автошкола»";
_excelApp.Cells[2, 1] = "Юридический адрес:";
_excelApp.Cells[3, 1] = "г. Могилев, ул. Школьная 16-405";
_excelApp.Cells[4, 1] = "УНН: 600359652";
_excelApp.Cells[5, 1] = "Р/с 3012205280013 в Ф-л ОАО Бел";
_excelApp.Cells[6, 1] = "АПБ МОУ г. Могилев,";
_excelApp.Cells[7, 1] = "пр-т Мира 91 код 942";
_excelApp.Cells[8, 1] = "Телефон:(222) 290-45-65";
_excelApp.Cells[9, 1] = "Директор: Хацкевич Андрей Евгеньевич";
_excelApp.Cells[11, 1] = string.Format("График работы инструктора {0}", FIO);
_excelApp.Cells[12, 1] = string.Format("с {0} по {1}", StartDate.ToShortDateString(), EndDate.ToShortDateString());
var cellHeader1 = (Excel.Range)_excelApp.Cells[11, 1];
var cellHeader2 = (Excel.Range)_excelApp.Cells[11, 4];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
cellHeader1 = (Excel.Range)_excelApp.Cells[12, 1];
cellHeader2 = (Excel.Range)_excelApp.Cells[12, 4];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Cells[14, 1] = "Дата";
_excelApp.Columns[1].ColumnWidth = 15;
_excelApp.Cells[14, 1].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[1].VerticalAlignment = Excel.Constants.xlCenter;
_excelApp.Cells[14, 2] = "Время";
_excelApp.Columns[2].ColumnWidth = 20;
_excelApp.Columns[2].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[2].VerticalAlignment = Excel.Constants.xlCenter;
_excelApp.Cells[14, 3] = "Тип занятия";
_excelApp.Columns[3].ColumnWidth = 20;
_excelApp.Columns[3].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[3].VerticalAlignment = Excel.Constants.xlCenter;
_excelApp.Cells[14, 4] = "ФИО курсанта/Группа";
_excelApp.Columns[4].ColumnWidth = 25;
_excelApp.Columns[4].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[4].VerticalAlignment = Excel.Constants.xlCenter;
int row = 15;
foreach (var l in Lessons.OrderBy(l => (l.Date + l.StartTime)))
{
_excelApp.Cells[row, 1] = l.Date.ToShortDateString();
_excelApp.Cells[row, 2] = l.StartTime.ToString() + "-" + l.EndTime.ToString();
_excelApp.Cells[row, 3] = l.Type;
_excelApp.Cells[row, 4] = l.Training;
row++;
}
Excel.Range cell1 = (Excel.Range)_excelApp.Cells[14, 1];
Excel.Range cell2 = (Excel.Range)_excelApp.Cells[row - 1, 4];
var cells = _excelSheet.get_Range(cell1, cell2); // выделяем
cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
cells.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // правая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // левая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // нижняя внешняя
cells.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние вертикальные
cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние горизонтальные
row++;
//_excelBook.SaveAs(_saveAsPath);
_excelBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Filename: _saveAsPath);
}
catch (Exception)
{
}
finally
{
_excelBook.Close(false);
_excelApp.Quit();
}
return _saveAsPath;
}
public static void SetCurrentCell(int startRow, int startColumn, int?endRow, int?endColumn)
{
s_range = s_worksheet.get_Range(
GetExcelCellName(startColumn, startRow),
GetExcelCellName((endColumn.HasValue ? endColumn.Value : startColumn), (endRow.HasValue ? endRow.Value : startRow)));
}
/// <summary>
/// Get values from a spreadsheet for the given range.
/// </summary>
/// <param name="filename"></param>
/// <param name="sheetname"></param>
/// <param name="toprow">Start row</param>
/// <param name="lcolumn">Start col</param>
/// <param name="height">Start height</param>
/// <param name="width">Start width</param>
/// <returns>List of strings</returns>
public static System.Array getValuesFromRange(String filename, String sheetname, int startRow, int endRow, int startCol, int endCol)
{
System.Array rangeValues;
try
{
successStatus = openXlApp();
xlWorkbook = openXlWorkBook(filename);
xlSheet = xlWorkbook.ActiveSheet;
String xlRange = Util.rangeToExcelAddress(startRow, endRow, startCol, endCol);
Excel.Range range = xlSheet.get_Range(xlRange);
rangeValues = (System.Array)range.Cells.Value2;
successStatus = quitXlApp();
}
finally
{
garbageCollect();
}
return rangeValues;
}
private void ведомостьВходимостиToolStripMenuItem_Click(object sender, EventArgs e)
{
reportItemEnter report = new reportItemEnter();
if (report.ShowDialog(this) == DialogResult.OK)
{
PelengEntities pe = new PelengEntities();
string name;
List <Dictionary <string, int> > fullList = new List <Dictionary <string, int> >();
List <string> needRead = new List <string>();
if (report.Number.EndsWith("0"))
{
var specs = (from m in pe.Сборки
where m.НомерСборки == report.Number
select m).First();
name = specs.Наименование;
var inAssembly = (from m in pe.ВходящиеСборки
where m.НомерВхСборки == report.Number
select m).ToList();
foreach (ВходящиеСборки vs in inAssembly)
{
Dictionary <string, int> dt = new Dictionary <string, int>();
dt.Add(vs.НомерСборки, vs.Количество);
fullList.Add(dt);
needRead.Add(vs.НомерСборки);
}
}
else
{
var specs = (from m in pe.Детали
where m.НомерДетали == report.Number
select m).First();
name = specs.Наименование;
var inAssembly = (from m in pe.СборкиДетали
where m.НомерДетали == report.Number
select m).ToList();
foreach (СборкиДетали d in inAssembly)
{
Dictionary <string, int> dt = new Dictionary <string, int>();
dt.Add(d.НомерСборки, d.Количество);
fullList.Add(dt);
needRead.Add(d.НомерСборки);
}
}
while (needRead.Count != 0)
{
string s = needRead[0];
for (int i = 0; i < fullList.Count; i++)
{
if (fullList[i].Last().Key == s)
{
var newAssembly = (from m in pe.ВходящиеСборки
where m.НомерВхСборки == s
select m).ToList();
int c = 0;
IDictionary <string, int> dt = new Dictionary <string, int>();
foreach (string key in fullList[i].Keys)
{
dt.Add(key, fullList[i][key]);
}
foreach (ВходящиеСборки vs in newAssembly)
{
c++;
if (c > 1)
{
dt.Add(vs.НомерСборки, vs.Количество);
fullList.Add(new Dictionary <string, int>(dt));
dt.Remove(vs.НомерСборки);
}
else
{
fullList[i].Add(vs.НомерСборки, vs.Количество);
}
needRead.Add(vs.НомерСборки);
}
}
}
needRead.Remove(s);
}
Dictionary <string, int> dtTypeNull = new Dictionary <string, int>();
Dictionary <string, Dictionary <string, int> > listDtOne = new Dictionary <string, Dictionary <string, int> >();
foreach (Dictionary <string, int> dt in fullList)
{
string str = report.Number;
int n = 1;
foreach (string s in dt.Keys)
{
str += " (" + dt[s] + ") --> " + s;
n *= dt[s];
}
if (report.TypeReport == 0)
{
if (!dtTypeNull.ContainsKey(dt.First().Key))
{
dtTypeNull.Add(dt.First().Key, dt.First().Value);
}
}
else
{
if (!listDtOne.ContainsKey(dt.Last().Key))
{
Dictionary <string, int> lastDt = new Dictionary <string, int>();
lastDt.Add(str, n);
listDtOne.Add(dt.Last().Key, lastDt);
}
else
{
listDtOne[dt.Last().Key].Add(str, n);
}
}
n = 1;
}
Excel.Application application = new Excel.Application();
Object missing = Type.Missing;
//добавили книгу
application.Workbooks.Add(missing);
Excel.Worksheet sheet = (Excel.Worksheet)application.ActiveSheet;
if (report.TypeReport == 0)
{
if (dtTypeNull.Count != 0)
{
typeNull(sheet, dtTypeNull);
}
else
{
addExcelCell(sheet, 3, 1, "Первичная входимость", "Times New Roman", 16, true);
if (report.Number.EndsWith("0"))
{
addExcelCell(sheet, 6, 1, "Данная сборка не входит ни в одно изделие!", "Times New Roman", 14, true);
}
else
{
addExcelCell(sheet, 6, 1, "Данная деталь не входит ни в одно изделие!", "Times New Roman", 14, true);
}
}
}
else if (report.TypeReport == 1)
{
if (listDtOne.Count != 0)
{
typeOne(sheet, listDtOne);
}
else
{
addExcelCell(sheet, 3, 1, "Полная входимость без цепочки", "Times New Roman", 16, true);
if (report.Number.EndsWith("0"))
{
addExcelCell(sheet, 6, 1, "Данная сборка не входит ни в одно изделие!", "Times New Roman", 14, true);
}
else
{
addExcelCell(sheet, 6, 1, "Данная деталь не входит ни в одно изделие!", "Times New Roman", 14, true);
}
}
}
else
{
if (listDtOne.Count != 0)
{
typeTwo(sheet, listDtOne);
}
else
{
addExcelCell(sheet, 3, 1, "Полная входимость с цепочкой", "Times New Roman", 16, true);
if (report.Number.EndsWith("0"))
{
addExcelCell(sheet, 6, 1, "Данная сборка не входит ни в одно изделие!", "Times New Roman", 14, true);
}
else
{
addExcelCell(sheet, 6, 1, "Данная деталь не входит ни в одно изделие!", "Times New Roman", 14, true);
}
}
}
addExcelCell(sheet, 1, 1, System.DateTime.Now.ToString(), "Times New Roman", 12, true);
addExcelCell(sheet, 4, 1, report.Number + " " + name, "Times New Roman", 16, true);
(sheet.get_Range("A3", "D3") as Excel.Range).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
application.Visible = true;
}
}
public static List<OTItem> LoadOT(string filePath)
{
List<OTItem> ots = new List<OTItem>();
int sheetNo = 2;
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(filePath);
MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
for (int index = 1; index <= lastRow; index++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "AD" + index.ToString()).Cells.Value;
if (MyValues.GetValue(1, 2) == null && MyValues.GetValue(1, 3)==null)
{
Console.WriteLine("OTItem no value on row " + index.ToString());
continue;
}
RowIndex = 1;
ots.Add(new OTItem
{
OT_ApplyNumber = GetValue(MyValues),
Worker_Number = GetValue(MyValues),
Worker_CnName = GetValue(MyValues),
Worker_Dept = GetValue(MyValues),
Worker_Group = GetValue(MyValues),
Cycle_StartEd = GetValue(MyValues),
Cycle_EndEd = GetValue(MyValues),
Create_Ed = GetValue(MyValues),
OT_StartEd = GetValue(MyValues),
OT_StartTime = GetValue(MyValues),
OT_EndEd = GetValue(MyValues),
OT_EndTime = GetValue(MyValues),
Statistic_Date = GetValue(MyValues),
OT_Hours = GetValue(MyValues),
Pay_Hours = GetValue(MyValues),
Offset_Hours = GetValue(MyValues),
LeftOffset_Hour = GetValue(MyValues),
LeftChange_Hour = GetValue(MyValues),
//需要为导入数据确定一个加班种类,the value is -1
OT_WorkType = GetValue(MyValues),
Reason = GetValue(MyValues),
Comment = GetValue(MyValues),
App1 = GetValue(MyValues),
App2 = GetValue(MyValues),
//审批状态 固定为“审批完成”旧数据请考虑都走完审批再导入到新系统
Status = GetValue(MyValues),
Apply_Type = GetValue(MyValues),
//加班号,唯一标识一次加班的号码
OT_Number = GetValue(MyValues),
Attendance_StartEd = GetValue(MyValues),
Attendance_EndEd = GetValue(MyValues),
Shift_Id = GetValue(MyValues),
Compensate_Rate = GetValue(MyValues)
});
}
return ots;
}
//private void button1_Click(object sender, RibbonControlEventArgs e)
//{
// //PositionMasterView view = new PositionMasterView();
// ////view.ViewModel_. = InstPositionWapperVM_;
// //Window w = new Window();
// //w.Content = view;
// //w.Visibility = 0;
// //DataBaseManager dbManager = new DataBaseManager();
// //List<string> fieldName = new List<string>();
// //List<string> fieldValue = new List<string>();
// //fieldName.Add("KRCODE");
// //fieldName.Add("NAME");
// //fieldValue.Add("KR000000001");
// //fieldValue.Add("ELS1011");
// //dbManager.addInstDB("MasterInfo", fieldName, fieldValue);
//}
private PositionBookingInfoVM readPositionInfoFromExcel()
{
Excel.Application xl_app = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Excel.Workbook xl_workbook = null;
xl_workbook = xl_app.ActiveWorkbook;
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)xl_workbook.Worksheets.get_Item(1);
//check book valid
//Excel.Range newFirstRow = sheet.get_Range("TestName");
//newFirstRow.Value2 = "OK";
string itemcode = Convert.ToString(sheet.get_Range("itemcode").Value2);
string krcode = Convert.ToString(sheet.get_Range("krcode").Value2);
string teamcode = Convert.ToString(sheet.get_Range("teamcode").Value2);
string fundcode = Convert.ToString(sheet.get_Range("fundcode").Value2);
string bookID = Convert.ToString(sheet.get_Range("bookID").Value2);
string excelType = Convert.ToString(sheet.get_Range("excelType").Value2);
string productType = Convert.ToString(sheet.get_Range("productType").Value2);
string groupID = Convert.ToString(sheet.get_Range("groupID").Value2);
string groupState = Convert.ToString(sheet.get_Range("groupState").Value2);
string itemname = Convert.ToString(sheet.get_Range("itemname").Value2);
string shortName = Convert.ToString(sheet.get_Range("shortName").Value2);
string issueDate = Convert.ToString(sheet.get_Range("issueDate").Value2);
string maturityDate = Convert.ToString(sheet.get_Range("maturityDate").Value2);
string underlying = Convert.ToString(sheet.get_Range("underlying").Value2);
string counterParty = Convert.ToString(sheet.get_Range("counterParty").Value2);
string contractType = Convert.ToString(sheet.get_Range("contractType").Value2);
string notional = Convert.ToString(sheet.get_Range("notional").Value2);
string currency = Convert.ToString(sheet.get_Range("currency").Value2);
string bookedOrder = Convert.ToString(sheet.get_Range("bookedOrder").Value2);
string bookingState = Convert.ToString(sheet.get_Range("bookingState").Value2);
string bookingDate = Convert.ToString(sheet.get_Range("bookingDate").Value2);
PositionBookingInfoVM vm = new PositionBookingInfoVM(itemcode,
krcode,
teamcode,
fundcode,
bookID,
excelType,
productType,
groupID,
groupState,
itemname,
shortName,
issueDate,
maturityDate,
underlying,
counterParty,
contractType,
notional,
currency,
bookedOrder,
bookingState,
bookingDate);
return(vm);
//엑셀에서 로드해 갔고 InstPositionWapperVM_ 에 다가 박음
}
private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka)
{
excelapp = new Excel.Application();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka),
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);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelappworkbook.Close();
excelappworkbooks.Close();
excelapp.Workbooks.Close();
}
private void btn1_Click_1(object sender, RoutedEventArgs e)
{
clearComments();
string src_file_name = Directory.GetCurrentDirectory() + @"\Files\list.csv";
string dest_file_name = Directory.GetCurrentDirectory() + @"\Files\list.xlsx";
MsExcel.Application oExcApp; //Excel Application;
MsExcel.Workbook oExcBook; //
try
{
if (File.Exists(dest_file_name))
{
File.Delete(dest_file_name);
}
oExcApp = new MsExcel.Application();
object missing = System.Reflection.Missing.Value;
oExcBook = oExcApp.Workbooks.Add(true);
MsExcel.Worksheet worksheet1 = (MsExcel.Worksheet)oExcBook.Worksheets["sheet1"];
worksheet1.Activate();
oExcApp.Visible = false;
oExcApp.DisplayAlerts = false;
MsExcel.Range range1 = worksheet1.get_Range("B1", "H2");
range1.Columns.ColumnWidth = 8;
range1.Columns.RowHeight = 20;
range1.Merge(false);
//设置垂直居中和水平居中
range1.VerticalAlignment = MsExcel.XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = MsExcel.XlHAlign.xlHAlignCenter;
//range1.Font.Color = Color.FromRgb(0, 0, 255);
range1.Font.Size = 20;
range1.Font.Bold = true;
worksheet1.Cells[1, 2] = "学生成绩单";
worksheet1.Cells[3, 1] = "学号";
worksheet1.Cells[3, 2] = "姓名";
worksheet1.Columns[1].ColumnWidth = 12;
StreamReader sw = new StreamReader(src_file_name);
string a_str;
string[] str_list;
int i = 4;
a_str = sw.ReadLine();
while (a_str != null)
{
str_list = a_str.Split(",".ToCharArray());
worksheet1.Cells[i, 1] = str_list[0];
worksheet1.Cells[i, 2] = str_list[1];
i++;
a_str = sw.ReadLine();
}
sw.Close();
for (int i1 = 0; i1 < 5; i1++)
{
for (int j = 0; j < 8; j++)
{
worksheet1.Cells[i1 + 18, j + 3].Value2 = "=CEILING.MATH(RAND()*100)";
worksheet1.Cells[i1 + 4, j + 3].Value2 = worksheet1.Cells[i1 + 18, j + 3].Value;
}
}
//添加图表
MsExcel.Shape theShape = worksheet1.Shapes.AddChart(MsExcel.XlChartType.xl3DColumn, 120, 130, 380, 250);
worksheet1.Cells[3, 3].Value2 = "美术";
worksheet1.Cells[3, 4].Value2 = "物理";
worksheet1.Cells[3, 5].Value2 = "政治";
worksheet1.Cells[3, 6].Value2 = "化学";
worksheet1.Cells[3, 7].Value2 = "体育";
worksheet1.Cells[3, 8].Value2 = "英语";
worksheet1.Cells[3, 9].Value2 = "数学";
worksheet1.Cells[3, 10].Value2 = "历史";
//设定图表的数据区域
MsExcel.Range range = worksheet1.get_Range("b3:j8");
theShape.Chart.SetSourceData(range, Type.Missing);
//设置图标题文本
theShape.Chart.HasTitle = true;
theShape.Chart.ChartTitle.Text = "学生成绩";
theShape.Chart.ChartTitle.Caption = "学生成绩";
//设置单元格边框线型
range1 = worksheet1.get_Range("a3", "j8");
range1.Borders.LineStyle = MsExcel.XlLineStyle.xlContinuous;
oExcBook.RefreshAll();
worksheet1 = null;
object file_name = dest_file_name;
oExcBook.Close(true, file_name, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcBook);
oExcBook = null;
oExcApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcApp);
oExcApp = null;
System.GC.Collect();
}
catch (Exception e2)
{
MessageBox.Show(e2.Message);
}
finally
{
Console.WriteLine(" 正在结束 excel 进程");
showComment("正在结束excel进程");
//关闭 excel 进程
Process[] AllProces = Process.GetProcesses();
int flag = 0;
for (int j = 0; j < AllProces.Length; j++)
{
string theProcName = AllProces[j].ProcessName;
//Console.WriteLine(theProcName);
if (String.Compare(theProcName, "EXCEL") == 0)
{
if (AllProces[j].Responding && !AllProces[j].HasExited)
{
AllProces[j].Kill();
AllProces[j].WaitForExit();
flag = j;
}
}
}
//Close excel Process.
Console.WriteLine(AllProces[1].HasExited);
OpenExcel(dest_file_name);
}
}
//----------------------------------------------------------
public static string Export_Excel(ObservableCollection <data_material_project> my_material_project, string path, string name_sheet, ComboBox pattern)
{
string result = "F";
try
{
List <data_fill_pattern> data_pattern = CollectionViewSource.GetDefaultView(pattern.ItemsSource).Cast <data_fill_pattern>().ToList();
Excel.Application excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
sheet.Name = name_sheet;
Excel.Range range = sheet.UsedRange;
int range_MCT = 1;
int range_TVL = 2;
int range_DV = 3;
int range_MVL = 4;
int range_DTS = 5;
int range_MSF = 6;
int range_TSF = 7;
int range_MC = 8;
int range_TC = 9;
int range_TON = 10;
int i = 2;
foreach (data_material_project data in my_material_project)
{
Format_Range_Excel(range, i, range_MCT, data.ma_cong_tac_project, Color.Transparent);
Format_Range_Excel(range, i, range_TVL, data.ten_vat_lieu_project, Color.Transparent);
Format_Range_Excel(range, i, range_DV, data.don_vi_project, Color.Transparent);
Format_Range_Excel(range, i, range_MVL, "", Color.FromArgb(255, data.mau_vat_lieu.Red, data.mau_vat_lieu.Green, data.mau_vat_lieu.Blue));
Format_Range_Excel(range, i, range_DTS, data.do_trong_suot_vat_lieu.ToString(), Color.Transparent);
Format_Range_Excel(range, i, range_MSF, "", Color.FromArgb(255, data.mau_surface.Red, data.mau_surface.Green, data.mau_surface.Blue));
Format_Range_Excel(range, i, range_TSF, data_pattern.First(x => x.pattern_id.IntegerValue == data.id_surface.IntegerValue).name, Color.Transparent);
Format_Range_Excel(range, i, range_MC, "", Color.FromArgb(255, data.mau_cut.Red, data.mau_cut.Green, data.mau_cut.Blue));
Format_Range_Excel(range, i, range_TC, data_pattern.First(x => x.pattern_id.IntegerValue == data.id_cut.IntegerValue).name, Color.Transparent);
Format_Range_Excel(range, i, range_TON, data.ton, Color.Transparent);
range.Cells[i, range_MCT].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
range.Cells[i, range_TVL].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
range.Cells[i, range_MCT].IndentLevel = 2;
range.Cells[i, range_TVL].IndentLevel = 2;
i++;
}
List <string> column_name = new List <string>()
{
"Material ID", "Material Name", "Unit", "Material Color", "Material Transparency", "Surface Color", "Surface Name", "Cut Color", "Cut Name", "Factor TON"
};
for (int a = 1; a < 11; a++)
{
Format_Range_Excel(range, 1, a, column_name[a - 1], Color.Transparent);
}
int row = sheet.Rows.Count;
sheet.get_Range("A1", "J" + row).Columns.AutoFit();
workbook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault);
workbook.Close(true, null, null);
excel.Quit();
result = "S";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
result = "F";
}
return(result);
}
private void Pier_Process_Design_BS()
{
string file_path = Path.Combine(iApp.LastDesignWorkingFolder, Title);
if (iApp.user_path != "")
{
file_path = Path.Combine(iApp.user_path, Title);
}
if (!Directory.Exists(file_path))
{
Directory.CreateDirectory(file_path);
}
//file_path = Path.Combine(file_path, "RCC Cantilever Abutment Design");
//if (!Directory.Exists(file_path)) Directory.CreateDirectory(file_path);
file_path = Path.Combine(file_path, "Pier with open foundation.xlsx");
//file_path = Path.Combine(file_path, "BoQ_Flyover_ROB_RUBs.xlsx");
//file_path = Path.Combine(file_path, "BoQ for " + cmb_boq_item.Text + ".xlsx");
string copy_path = file_path;
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Pier\Pier Design Limit State\Pier with open foundation BS.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
else
{
MessageBox.Show(file_path + " file not found.");
return;
}
iApp.Excel_Open_Message();
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.Application();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Design Data"];
List <TextBox> All_Data = Get_TextBoxes();
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv.RowCount + after_indx), "L" + (dgv.RowCount + after_indx));
//formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <double> data = new List <double>();
try
{
string kStr = "";
foreach (var item in All_Data)
{
kStr = item.Name.Replace("txt_des_", "");
//myExcelWorksheet.get_Range("E53").Formula = data[rindx++].ToString();
myExcelWorksheet.get_Range(kStr).Formula = item.Text;
}
#region Input 2
//myExcelWorksheet.get_Range("K78").Formula = data[rindx++].ToString();
//myExcelWorksheet.get_Range("K81").Formula = data[rindx++].ToString();
#endregion Input 2
}
catch (Exception exx) { }
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
//iApp.Excel_Open_Message();
}
///------------------------------------------------------------------
/// <summary>
/// 出勤簿印刷・シート追加一括印刷 </summary>
///------------------------------------------------------------------
private void sReport()
{
const int S_GYO = 7; //エクセルファイル日付明細開始行
//開始日付
int StartDay = 1;
//終了日付
int EndDay = DateTime.DaysInMonth(int.Parse(txtYear.Text) + Utility.GetRekiHosei(), int.Parse(txtMonth.Text));
string sDate;
DateTime eDate;
//////const int S_ROWSMAX = 7; //エクセルファイル列最大値
try
{
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(Properties.Settings.Default.OCR出勤簿シートパス, 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));
Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2];
try
{
// 休日配列インスタンス化
Config.Holiday[] Holiday = new Config.Holiday[1];
Holiday[0] = new Config.Holiday();
Holiday[0].hDate = DateTime.Parse("1900/01/01");
// ローカルデータベース接続
SysControl.SetDBConnect Con = new SysControl.SetDBConnect();
OleDbCommand sCom = new OleDbCommand();
sCom.Connection = Con.cnOpen();
OleDbDataReader dR;
// 休日データを配列に読み込む
int iH = 0;
string sqlSTRING = string.Empty;
sqlSTRING += "select * from 休日 order by 年月日";
sCom.CommandText = sqlSTRING;
dR = sCom.ExecuteReader();
while (dR.Read())
{
if (iH > 0)
{
Array.Resize(ref Holiday, iH + 1); // 配列要素数追加
Holiday[iH] = new Config.Holiday(); // 休日配列インスタンス化
}
Holiday[iH].hDate = DateTime.Parse(dR["年月日"].ToString());
if (dR["月給者"].ToString() == "1")
{
Holiday[iH].Gekkyuu = true;
}
else
{
Holiday[iH].Gekkyuu = false;
}
if (dR["時給者"].ToString() == "1")
{
Holiday[iH].Jikyuu = true;
}
else
{
Holiday[iH].Jikyuu = false;
}
iH++;
}
dR.Close();
sCom.Connection.Close();
// ページカウント
int pCnt = 0;
//// 有給記号非表示設定 : 2017/11/27
//Excel.TextBox t34 = oxlsSheet.TextBoxes("Text Box 32");
//t34.Visible = false;
//oxlsSheet.Cells[5, 5] = string.Empty;
// パートのとき有給記号非表示設定 : 2017/11/30
if (_YakushokuType == global.STATUS_PART)
{
Excel.TextBox t34 = oxlsSheet.TextBoxes("Text Box 32");
t34.Visible = false;
oxlsSheet.Cells[5, 5] = string.Empty;
}
//Excel.TextBox t14 = oxlsSheet.TextBoxes("テキスト 14");
//t14.Visible = false;
//Excel.TextBox t34 = oxlsSheet.TextBoxes("yukyukigo");
//Excel.TextBox t14 = oxlsSheet.TextBoxes("kyukakigo");
//if (_tBoxStatus)
//{
// t34.Visible = false;
// t14.Visible = false;
// oxlsSheet.Cells[5, 5] = string.Empty;
//}
//else
//{
// t34.Visible = true;
// t14.Visible = true;
// oxlsSheet.Cells[5, 5] = "有給";
//}
// グリッドを順番に読む
for (int i = 0; i < dg1.RowCount; i++)
{
// チェックがあるものを対象とする
if (dg1[0, i].Value.ToString() == "True")
{
// 印刷2件目以降はシートを追加する
pCnt++;
if (pCnt > 1)
{
oxlsSheet.Copy(Type.Missing, oXlsBook.Sheets[pCnt - 1]);
oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[pCnt];
}
int sRow = i;
// 帳票区分（1:メイン勤務出勤簿、2:サブ勤務出勤簿）
// 配列から取得 2017/05/17
//if (radioButton1.Checked)
//{
// oxlsSheet.Cells[2, 1] = PR_STATUS_MAIN.ToString();
//}
//else
//{
// oxlsSheet.Cells[2, 1] = PR_STATUS_SUB.ToString();
//}
oxlsSheet.Cells[2, 1] = dg1[5, sRow].Value.ToString();
// 年
oxlsSheet.Cells[3, 4] = string.Format("{0, 2}", int.Parse(txtYear.Text)).Substring(0, 1);
oxlsSheet.Cells[3, 5] = string.Format("{0, 2}", int.Parse(txtYear.Text)).Substring(1, 1);
// 月
oxlsSheet.Cells[3, 8] = string.Format("{0, 2}", int.Parse(txtMonth.Text)).Substring(0, 1);
oxlsSheet.Cells[3, 9] = string.Format("{0, 2}", int.Parse(txtMonth.Text)).Substring(1, 1);
// 所属名
oxlsSheet.Cells[3, 15] = dg1[2, sRow].Value.ToString();
// 所属コード
string szCode = dg1[1, sRow].Value.ToString().PadLeft(global.ShozokuMaxLength, ' ');
for (int ci = 0; ci < szCode.Length; ci++)
{
oxlsSheet.Cells[3, 25 + ci] = szCode.Substring(ci, 1);
}
//oxlsSheet.Cells[3, 27] = dg1[1, sRow].Value.ToString().Substring(0, 1);
//oxlsSheet.Cells[3, 28] = dg1[1, sRow].Value.ToString().Substring(1, 1);
//oxlsSheet.Cells[3, 29] = dg1[1, sRow].Value.ToString().Substring(2, 1);
// 社員番号
//for (int ci = 0; ci < global.ShainLength; ci++)
//{
// oxlsSheet.Cells[2, 25 + ci] = dg1[3, sRow].Value.ToString().Substring(ci, 1);
//}
oxlsSheet.Cells[2, 25] = dg1[3, sRow].Value.ToString().Substring(0, 1);
oxlsSheet.Cells[2, 26] = dg1[3, sRow].Value.ToString().Substring(1, 1);
oxlsSheet.Cells[2, 27] = dg1[3, sRow].Value.ToString().Substring(2, 1);
oxlsSheet.Cells[2, 28] = dg1[3, sRow].Value.ToString().Substring(3, 1);
oxlsSheet.Cells[2, 29] = dg1[3, sRow].Value.ToString().Substring(4, 1);
// 氏名
oxlsSheet.Cells[2, 15] = dg1[4, sRow].Value.ToString();
// 日付
int addRow = 0;
for (int iX = StartDay; iX <= EndDay; iX++)
{
// 暦補正値は設定ファイルから取得する
sDate = (int.Parse(txtYear.Text) + Utility.GetRekiHosei()).ToString() + "/" + txtMonth.Text + "/" + iX.ToString();
eDate = DateTime.Parse(sDate);
oxlsSheet.Cells[S_GYO + addRow, 2] = ("日月火水木金土").Substring(int.Parse(eDate.DayOfWeek.ToString("d")), 1);
rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO + addRow, 1];
rng[1] = (Excel.Range)oxlsSheet.Cells[S_GYO + addRow, 2];
// 日曜日なら曜日の背景色を変える
if (rng[1].Text.ToString() == "日")
{
oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.LightGray;
}
// 祝祭日なら曜日の背景色を変える
for (int j = 0; j < Holiday.Length; j++)
{
// 休日登録されている
if (Holiday[j].hDate == eDate)
{
// 月給者または時給者が各々休日対象となっている
if (dg1[5, sRow].Value.ToString() == "社員" && Holiday[j].Gekkyuu == true ||
dg1[5, sRow].Value.ToString() == "パート" && Holiday[j].Jikyuu == true)
{
oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.LightGray;
break;
}
else
{
oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.White;
}
}
}
// 行数加算
addRow++;
}
}
}
// マウスポインタを元に戻す
this.Cursor = Cursors.Default;
// 確認のためExcelのウィンドウを表示する
//oXls.Visible = true;
// 印刷
oXlsBook.PrintOut();
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
}
finally
{
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
// マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
private void bMakeNaklad_Click(object sender, EventArgs e)
{
try
{
Excel.Application exapp = new Excel.Application();
exapp.Visible = true;
exapp.Workbooks.Open(Application.StartupPath + "\\blank.xls", Type.Missing, true);
Excel.Worksheet list1 = (exapp.Worksheets.get_Item(1));
DataRowView rrashod = (DataRowView)bsРасход.List[bsРасход.Position];
bsКлиент.Filter = "idklienta =" + rrashod["idklienta"];
DataRowView rklient = (DataRowView)bsКлиент.List[0];
bsКлиент.RemoveFilter();
string klient = rklient["nameklienta"] + ", г." + rklient["gorod"] + ", ул. " +
rklient["ulica"] + ", д." + rklient["dom"] +
", тел. " + rklient["telefon"];
list1.get_Range("L9").Value = klient;
list1.get_Range("I13").Value = klient;
list1.get_Range("CF18").Value = rrashod["nakladnaya"];
list1.get_Range("CF19").Value = rrashod["datavidachi"];
list1.get_Range("AX23").Value = rrashod["nakladnaya"];
list1.get_Range("BI23").Value = rrashod["datavidachi"];
list1.get_Range("A28").Value = "'1.";
DataRowView rtovar = (DataRowView)bsТовар.List[bsТовар.Position];
list1.get_Range("D28").Value = rtovar["nametovara"];
list1.get_Range("X28").Value = rtovar["edizmer"];
list1.get_Range("AM28").Value = rrashod["kolichestvo"];
list1.get_Range("BH28").Value = rtovar["cena"];
bsСотрудник.Filter = "idsotrudnika =" + rrashod["idsotrudnika"];
DataRowView rsot = (DataRowView)bsСотрудник.List[0];
bsСотрудник.RemoveFilter();
list1.get_Range("L39").Value = rsot["dolzhnost"];
list1.get_Range("AG39").Value = rsot["fio"];
}
catch
{
MessageBox.Show("Не возможно сформировать отчет с помощью Microsoft Excel.", "Внимание, ошибка!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void SetMerg(string a1, string a2)
{
Microsoft.Office.Interop.Excel.Worksheet newWorksheet1 = Globals.ThisAddIn.Application.ActiveSheet;
newWorksheet1.get_Range(a1, a2).Merge();
}
public void ExportToExcel(string mapname, int address, int length, byte[] mapdata, int cols, int rows, bool isSixteenbit, int[] xaxisvalues, int[] yaxisvalues, bool isupsidedown, string xaxisdescr, string yaxisdescr, string zaxisdescr)
{
//en-US
CultureInfo tci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = tci;
try
{
try
{
if (xla == null)
{
xla = new Microsoft.Office.Interop.Excel.Application();
}
}
catch (Exception xlaE)
{
Console.WriteLine("Failed to create office application interface: " + xlaE.Message);
}
// turn mapdata upside down
if (isupsidedown)
{
mapdata = TurnMapUpsideDown(mapdata, cols, rows, isSixteenbit);
}
xla.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
ws.Name = "symboldata";
// Now create the chart.
ChartObjects chartObjs = (ChartObjects)ws.ChartObjects(Type.Missing);
ChartObject chartObj = chartObjs.Add(100, 400, 400, 300);
Microsoft.Office.Interop.Excel.Chart xlChart = chartObj.Chart;
int nRows = rows;
//if (isSixteenbit) nRows /= 2;
int nColumns = cols;
string upperLeftCell = "B3";
int endRowNumber = System.Int32.Parse(upperLeftCell.Substring(1)) + nRows - 1;
char endColumnLetter = System.Convert.ToChar(Convert.ToInt32(upperLeftCell[0]) + nColumns - 1);
string upperRightCell = System.String.Format("{0}{1}", endColumnLetter, System.Int32.Parse(upperLeftCell.Substring(1)));
string lowerRightCell = System.String.Format("{0}{1}", endColumnLetter, endRowNumber);
// Send single dimensional array to Excel:
Range rg1 = ws.get_Range("B2", "Z2");
double[] xarray = new double[nColumns];
double[] yarray = new double[nRows];
ws.Cells[1, 1] = "Data for " + mapname;
for (int i = 0; i < xarray.Length; i++)
{
if (xaxisvalues.Length > i)
{
xarray[i] = (int)xaxisvalues.GetValue(i);
}
else
{
xarray[i] = i;
}
//ws.Cells[i + 3, 1] = xarray[i];
ws.Cells[2, 2 + i] = xarray[i];
}
for (int i = 0; i < yarray.Length; i++)
{
if (yaxisvalues.Length > i)
{
if (isupsidedown)
{
yarray[i] = (int)yaxisvalues.GetValue((yarray.Length - 1) - i);
}
else
{
yarray[i] = (int)yaxisvalues.GetValue(i);
}
}
else
{
yarray[i] = i;
}
ws.Cells[i + 3, 1] = yarray[i];
//ws.Cells[2, 2 + i] = yarray[i];
}
Range rg = ws.get_Range(upperLeftCell, lowerRightCell);
rg.Value2 = AddData(nRows, nColumns, mapdata, isSixteenbit);
Range chartRange = ws.get_Range("A2", lowerRightCell);
xlChart.SetSourceData(chartRange, Type.Missing);
if (yarray.Length > 1)
{
xlChart.ChartType = XlChartType.xlSurface;
}
// Customize axes:
Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory,
XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = yaxisdescr;
try
{
Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlSeriesAxis,
XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = xaxisdescr;
}
catch (Exception E)
{
Console.WriteLine("Failed to set y axis: " + E.Message);
}
Axis zAxis = (Axis)xlChart.Axes(XlAxisType.xlValue,
XlAxisGroup.xlPrimary);
zAxis.HasTitle = true;
zAxis.AxisTitle.Text = zaxisdescr;
// Add title:
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = mapname;
// Remove legend:
xlChart.HasLegend = false;
// add 3d shade
xlChart.SurfaceGroup.Has3DShading = true;
/*if (File.Exists(m_currentfile + "~" + mapname + ".xls"))
* {
*
* }*/
try
{
wb.SaveAs(Tools.Instance.m_currentfile + "~" + mapname + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, false, null, null, null, null);
}
catch (Exception sE)
{
Console.WriteLine("Failed to save workbook: " + sE.Message);
}
}
catch (Exception E)
{
Console.WriteLine("Failed to export to excel: " + E.Message);
Console.WriteLine("Failed to export to excel: " + E.Message);
}
tci = new CultureInfo("nl-NL");
Thread.CurrentThread.CurrentCulture = tci;
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void CreateTable()
{
string[] headers = new string[]
{
"Műsor azonosítója",
"Dátum",
"Időpont",
"Film címe",
"Eladott jegyek száma",
"Bevétel"
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
var eladott_jegyek = (from x in context.Foglalas
where x.Foglalt == true
group x by x.Musor_Id into g
select new { id = g.Key, jegyek = g.Count() });
object[,] values = new object[Musor.Count, headers.Length];
int counter = 0;
foreach (Musor m in Musor)
{
values[counter, 0] = m.Id_Musor;
values[counter, 1] = m.Datum.ToString("yyyy.MM.dd");
values[counter, 2] = m.Idopont.ToString();
values[counter, 3] = m.Film.Cím;
values[counter, 4] = (from x in eladott_jegyek
where x.id == m.Id_Musor
select x.jegyek).FirstOrDefault();
values[counter, 5] = (int)values[counter, 4] * 1000;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range firstcolumn = xlSheet.get_Range(GetCell(2, 1), GetCell(1 + values.GetLength(0), 1));
firstcolumn.Font.Bold = true;
firstcolumn.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
firstcolumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
firstcolumn.Interior.Color = Color.LightGray;
int lastcolumnID = xlSheet.UsedRange.Columns.Count;
Excel.Range lastcolumn = xlSheet.get_Range(GetCell(2, lastcolumnID), GetCell(1 + values.GetLength(0), lastcolumnID));
lastcolumn.Font.Italic = true;
lastcolumn.NumberFormat = string.Format("# ##0 Ft");
}
//This method wirtes the sim data to the excel file.
public void WriteToExcel()
{
int iSimRunNumber = 0;
int linenumber = 0;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
//Add a new sheets object.
oXLSheets = oXL.Sheets as Excel.Sheets;
foreach (DOE22SimFile simfile in DOESimFiles)
{
iSimRunNumber++;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "RUN-" + iSimRunNumber.ToString();
//oSheet.Name = Path.GetFileName(simfile.filepath);
linenumber = 0;
// Output BEPS to excel Sheet.
oSheet.Cells[linenumber = 1, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print bpes report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
//reset linenumber for All sheet.
linenumber = 0;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "ALL";
foreach (DOE22SimFile simfile in DOESimFiles)
{
linenumber++;
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
if (bWriteBEPS == true)
{
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print beps report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
}
//Print ES-D
if (bWriteESD == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
linenumber = linenumber + simfile.esdTable.Rows.Count + 1;
}
//Print Zone Annual Data
if (bWriteZoneAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "Zone Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.ZoneAnnualTable, oSheet);
linenumber = linenumber + simfile.ZoneAnnualTable.Rows.Count + 1;
}
//Print System Annual Data
if (bWriteSystemAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "System Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.SystemAnnualTable, oSheet);
linenumber = linenumber + simfile.SystemAnnualTable.Rows.Count + 1;
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(sFoldername + @"\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void GenerateReport(Excel.Workbook workbook, SheetItems items)
{
Excel.Worksheet worksheet = workbook.Worksheets.get_Item(1) as Excel.Worksheet;
var cell_dateTime = worksheet.Cells.Find("$DateTime");
if (cell_dateTime != null)
{
cell_dateTime.Value = items.DateTime;
}
var cell_name = worksheet.Cells.Find("$Name");
if (cell_name != null)
{
cell_name.Value = items.Name;
}
var cell_line = worksheet.Cells.Find("$Line");
if (cell_line != null)
{
cell_line.Value = items.Line;
}
var cell_machine = worksheet.Cells.Find("$Machine");
if (cell_machine != null)
{
cell_machine.Value = items.Machine;
}
var cell_point = worksheet.Cells.Find("$Ref");
if (cell_point != null)
{
cell_point.Value = items.Ref;
}
var cell_measure = worksheet.Cells.Find("$Value");
if (cell_measure != null)
{
cell_measure.Value = items.Value;
}
var cell_Analysis = worksheet.Cells.Find("$AnalysisType");
if (cell_Analysis != null)
{
cell_Analysis.Value = items.AnalysisType;
}
var cell_PlotImg = worksheet.Cells.Find("$PlotImage");
if (cell_PlotImg != null)
{
Clipboard.SetDataObject(items.Img, true);
worksheet.Paste(cell_PlotImg, false);
cell_PlotImg.Value = "";
}
var cell_bfTime = worksheet.Cells.Find("$BeforeTime");
if (cell_bfTime != null)
{
Clipboard.SetDataObject(items.Img_BeforTime, true);
worksheet.Paste(cell_bfTime, false);
cell_bfTime.Value = "";
}
var cell_bfFFT = worksheet.Cells.Find("$BeforeFFT");
if (cell_bfFFT != null)
{
Clipboard.SetDataObject(items.Img_BeforFFT, true);
worksheet.Paste(cell_bfFFT, false);
cell_bfFFT.Value = "";
}
var cell_afTime = worksheet.Cells.Find("$AfterTime");
if (cell_afTime != null)
{
Clipboard.SetDataObject(items.Img_AfterTime, true);
worksheet.Paste(cell_afTime, false);
cell_afTime.Value = "";
}
var cell_afFFT = worksheet.Cells.Find("$AfterFFT");
if (cell_afFFT != null)
{
Clipboard.SetDataObject(items.Img_AfterFFT, true);
worksheet.Paste(cell_afFFT, false);
cell_afFFT.Value = "";
}
var cell_DailyData = worksheet.Cells.Find("$DailyData");
if (cell_DailyData != null)
{
var range1 = worksheet.get_Range("A11", "M11");
int num = 10;
foreach (var item in items.DailyDatas)
{
object[,] values =
{ {
item.Machine,
item.Point,
item.Function,
item.Unit,
item.Caution,
item.Failure,
item.Repair,
item.Stop,
item.MIN.ToString("#.00"),
item.MAX.ToString("#.00"),
item.AVG.ToString("#.00"),
item.Status.Stat,
"",
} };
if (num < items.DailyDatas.Length + 8)
{
range1.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
}
var range2 = worksheet.get_Range("A" + num, "M" + num);
range2.Value2 = values;
Excel.Range range = worksheet.Cells[num, "L"] as Excel.Range;
range.Interior.Color = item.Status.StatColor;
range.Font.Color = item.Status.StatColor_Font;
num++;
ReleaseObject(ref range2);
}
ReleaseObject(ref range1);
}
ReleaseObject(ref worksheet);
}
public Form1()
{
InitializeComponent();
double x, y, d, dx, dy, x0, y0;
int y_step;
excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 3;
excelapp.Workbooks.Add(Type.Missing);
excelapp.DisplayAlerts = true;
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelappworkbooks[1];
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelcells_a1 = excelworksheet.get_Range("A1", Type.Missing);
excelcells = excelcells_a1.get_Offset(0, 0);
///////////////////////////
////// Источники ///////
///////////////////////////
double sourceRadius = 0.2;
sources[0].id = 0;
sources[0].x = 0.27;
sources[0].y = 2.00;
sources[0].radius = sourceRadius;
sources[1].id = 1;
sources[1].x = 1.10;
sources[1].y = 2.81;
sources[1].radius = sourceRadius;
sources[2].id = 2;
sources[2].x = 2.22;
sources[2].y = 2.64;
sources[2].radius = sourceRadius;
sources[3].id = 3;
sources[3].x = 2.80;
sources[3].y = 1.61;
sources[3].radius = sourceRadius;
sources[4].id = 4;
sources[4].x = 2.39;
sources[4].y = 0.51;
sources[4].radius = sourceRadius;
sources[5].id = 5;
sources[5].x = 1.30;
sources[5].y = 0.13;
sources[5].radius = sourceRadius;
sources[6].id = 6;
sources[6].x = 0.36;
sources[6].y = 0.81;
sources[6].radius = sourceRadius;
sources[7].id = 7;
sources[7].x = 1.49;
sources[7].y = 1.50;
sources[7].radius = sourceRadius;
///////////////////////////
////// Приёмники ///////
///////////////////////////
/*
// Вариант один к одному для ВОСЬМИ приёмников
double receiverRadius = 0.23;
double receiverInnerRadius = 0.13;
receiversCount = 8;
receivers[0].id = 0;
receivers[0].x = 0.23;
receivers[0].y = 1.97;
receivers[0].radius = receiverRadius;
receivers[0].innerRadius = receiverInnerRadius;
receivers[1].id = 1;
receivers[1].x = 1.08;
receivers[1].y = 2.76;
receivers[1].radius = receiverRadius;
receivers[1].innerRadius = receiverInnerRadius;
receivers[2].id = 2;
receivers[2].x = 2.23;
receivers[2].y = 2.62;
receivers[2].radius = receiverRadius;
receivers[2].innerRadius = receiverInnerRadius;
receivers[3].id = 3;
receivers[3].x = 2.82;
receivers[3].y = 1.63;
receivers[3].radius = receiverRadius;
receivers[3].innerRadius = receiverInnerRadius;
receivers[4].id = 4;
receivers[4].x = 2.44;
receivers[4].y = 0.54;
receivers[4].radius = receiverRadius;
receivers[4].innerRadius = receiverInnerRadius;
receivers[5].id = 5;
receivers[5].x = 1.31;
receivers[5].y = 0.2;
receivers[5].radius = receiverRadius;
receivers[5].innerRadius = receiverInnerRadius;
receivers[6].id = 6;
receivers[6].x = 0.35;
receivers[6].y = 0.81;
receivers[6].radius = receiverRadius;
receivers[6].innerRadius = receiverInnerRadius;
receivers[7].id = 7;
receivers[7].x = 1.49;
receivers[7].y = 1.50;
receivers[7].radius = receiverRadius;
receivers[7].innerRadius = receiverInnerRadius;
*/
// Полтное гексагональное покрытие приёмниками
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
/*
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.23;
double receiverInnerRadius = 0.0;
*/
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.20;
double receiverInnerRadius = 0.10;
y_step = 0;
y = 0;
receiversCount = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
receivers[receiversCount].x = x;
receivers[receiversCount].y = y;
receivers[receiversCount].radius = receiverRadius;
receivers[receiversCount].innerRadius = receiverInnerRadius;
receiversCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
////////////////////////
////// Каналы ///////
////////////////////////
dx = 0.0429; // Шаг по x для гексагональной разметки
dy = 0.0357; // Шаг по y для гексагональной разметки
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
double channelRaduis = 0.021; // Радиус одного канала
y_step = 0;
y = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
channels[channelsCount].x = x;
channels[channelsCount].y = y;
channels[channelsCount].radius = channelRaduis;
channels[channelsCount].isOk = true;
channelsCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
}
private void OnExport(int numToEdit = 0)
{
int len1 = MonthUseList.Count();
int len2 = 7;
int len3;
int len4;
object[,] arrRes = new object[len1, len2];
object[,] arrChart = new object[len1, 2];
object[] arrCaption = new object[] { "ЦЗ", "План", "Факт", "Откл", "ПланРуб", "ФактРуб", "ОтклРуб" };
object[] arrCaption1 = new object[] { "ЦЗ", "Факт" };
DBListToArray();
ArrToExcel();
void DBListToArray()
{
int i = 0;
foreach (var r in MonthUseList)
{
arrRes[i, 0] = r.IdCostCenter;
arrRes[i, 1] = r.Plan;
arrRes[i, 2] = r.Fact;
arrRes[i, 3] = r.Difference;
arrRes[i, 4] = r.PlanCost;
arrRes[i, 5] = r.FactCost;
arrRes[i, 6] = r.DifferenceCost;
i++;
}
var qry2 = from o in MonthUseList
select new
{
o.Fact,
o.IdCostCenter
};
double Total = qry2.Sum(n => n.Fact);
var qry3 = from o in MonthUseList
where o.Fact >= Total * Proc / 100
orderby o.Fact descending
select new
{
o.Fact,
o.IdCostCenter
};
var qry4 = from o in MonthUseList
where o.Fact < Total * Proc / 100
orderby o.Fact descending
select new
{
o.Fact,
o.IdCostCenter
};
double Total1 = qry4.Sum(n => n.Fact);
int j = 0;
ChartList.Clear();
foreach (var newY in qry3.ToList())
{
ViewResult r = new ViewResult();
r.IdCostCenter = newY.IdCostCenter;
r.Fact = newY.Fact;
ChartList.Add(r);
j++;
}
ViewResult s = new ViewResult();
s.ResourceName = "Прочие";
s.Fact = Total1;
ChartList.Add(s);
len3 = ChartList.Count();
len4 = 2;
i = 0;
foreach (var l in ChartList)
{
arrChart[i, 0] = l.IdCostCenter == 0 ? "ЦЗ-Прочие" :"ЦЗ-" + l.IdCostCenter.ToString();
arrChart[i, 1] = l.Fact;
i++;
}
}
void ArrToExcel()
{
Excel.Application ex = new Excel.Application();
//ex.SheetsInNewWorkbook = 1;
//Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
ex.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + @"tmpfavorit.xlsx",
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);
ex.DisplayAlerts = true;
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(1);
sheet.Name = "ЦентрыЗатрат";
Excel.Range c1 = (Excel.Range)sheet.Cells[1, 1];
Excel.Range c2 = (Excel.Range)sheet.Cells[1, 1];
Excel.Range rangeCapt = sheet.get_Range(c1, c2);
rangeCapt.Value = ChartCaption;
c1 = (Excel.Range)sheet.Cells[3, 1];
c2 = (Excel.Range)sheet.Cells[3, len2];
Excel.Range rangeCaption = sheet.get_Range(c1, c2);
rangeCaption.Value = arrCaption;
c1 = (Excel.Range)sheet.Cells[4, 1];
c2 = (Excel.Range)sheet.Cells[4 + len1 - 1, len2];
Excel.Range range = sheet.get_Range(c1, c2);
range.Value = arrRes;
sheet = (Excel.Worksheet)ex.Worksheets.get_Item(2);
c1 = (Excel.Range)sheet.Cells[1, 1];
c2 = (Excel.Range)sheet.Cells[1, 1];
rangeCapt = sheet.get_Range(c1, c2);
rangeCapt.Value = "Структура фактического потребления, руб.";
c1 = (Excel.Range)sheet.Cells[3, 1];
c2 = (Excel.Range)sheet.Cells[3, 2];
rangeCaption = sheet.get_Range(c1, c2);
rangeCaption.Value = arrCaption1;
c1 = (Excel.Range)sheet.Cells[4, 1];
c2 = (Excel.Range)sheet.Cells[4 + len3 - 1, 2];
range = sheet.get_Range(c1, c2);
range.Value = arrChart;
ex.Visible = true;
ex.DisplayAlerts = true;
//Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
//dlg.FileName = "CostCenters01"; // Default file name
//dlg.DefaultExt = ".xlsx"; // Default file extension
//dlg.Filter = "Книга Excel (.xlsx)|*.xlsx"; // Filter files by extension
//dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
//Nullable<bool> result = dlg.ShowDialog();
//if (result == true)
//{
// string filename = dlg.FileName;
// ex.Application.ActiveWorkbook.SaveAs(filename + @"", Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// ex.WindowState = Excel.XlWindowState.xlMaximized;
// MessageBoxResult result1 = System.Windows.MessageBox.Show("Отчёт успешно сформирован",
// "Поздравляю",
// MessageBoxButton.OK,
// MessageBoxImage.Information);
// //ex.Visible = true;
//}
//ex.Quit();
}
}
/// <summary>
/// Set values to a spreadsheet for the given range.
/// </summary>
/// <param name="filename"></param>
/// <param name="sheetname"></param>
/// <param name="toprow">Start row</param>
/// <param name="lcolumn">Start col</param>
/// <param name="height">Start height</param>
/// <param name="width">Start width</param>
/// <returns>True if succeeded, false if failed.</returns>
public static bool setValuesToRange(String filename, String sheetname, System.Array values, int startRow, int endRow, int startCol, int endCol)
{
try
{
successStatus = openXlApp();
xlWorkbook = openXlWorkBook(filename);
xlSheet = xlWorkbook.Sheets[sheetname];
String xlRange = Util.rangeToExcelAddress(startRow, endRow, startCol, endCol);
Excel.Range range = xlSheet.get_Range(xlRange);
range.Value2 = values;
successStatus = quitXlApp();
}
finally
{
garbageCollect();
}
return successStatus;
}
protected void execute(DataSet ds, string x)
{
Microsoft.Office.Interop.Excel.Application objXL = null;
Microsoft.Office.Interop.Excel.Workbook objWB = null;
objXL = new Microsoft.Office.Interop.Excel.Application();
objXL.Visible = true;
objWB = objXL.Workbooks.Add(ds.Tables.Count);
int sheetcount = 1;
foreach (DataTable dt in ds.Tables)
{
Microsoft.Office.Interop.Excel.Worksheet objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets.Add();
String[,] arr = null;
objSHT = objXL.ActiveSheet;
Microsoft.Office.Interop.Excel.Range sheetCells = objSHT.Cells;
Microsoft.Office.Interop.Excel.Range cellFirst = sheetCells[1, 1] as Microsoft.Office.Interop.Excel.Range;
Microsoft.Office.Interop.Excel.Range cellLast = sheetCells[dt.Rows.Count, dt.Columns.Count] as Microsoft.Office.Interop.Excel.Range;
Microsoft.Office.Interop.Excel.Range theRange = objSHT.get_Range(cellFirst, cellLast);
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (j == 0)
{
if (dt.Columns[i].ColumnName.ToString().Equals(null))
{
arr[0, i] = string.Empty;
}
else
{
arr[0, i] = dt.Columns[i].ColumnName.ToString();
}
}
if (dt.Rows[j][i].ToString().Equals(null))
{
arr[j + 1, i] = string.Empty;
}
else
{
arr[j + 1, i] = dt.Rows[j][i].ToString();
}
}
theRange.set_Value(Type.Missing, arr);
//Incrementing sheet count
sheetcount++;
}
}
objWB.Saved = true;
objWB.SaveCopyAs(x);
//Closing work book
objWB.Close();
//Closing excel application
objXL.Quit();
MessageBox.Show("Data Saved Successfully", "DBF to XLS Conversion Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
protected void export_xls6(DataSet ds, string x)
{
Microsoft.Office.Interop.Excel.Application objXL = null;
//Workbook refrence
Microsoft.Office.Interop.Excel.Workbook objWB = null;
/*try
*
* {*/
//Instancing Excel using COM services
objXL = new Microsoft.Office.Interop.Excel.Application();
objXL.Visible = true;
//Adding WorkBook
objWB = objXL.Workbooks.Add(ds.Tables.Count);
//Variable to keep sheet count
int sheetcount = 1;
//Do I need to explain this ??? If yes please close my website and learn abc of .net .
foreach (DataTable dt in ds.Tables)
{
//Adding sheet to workbook for each datatable
Microsoft.Office.Interop.Excel.Worksheet objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets.Add();
//Naming sheet as SheetData1.SheetData2 etc....
objSHT.Name = "SheetData" + sheetcount.ToString();
String[,] arr = null;
objSHT = objXL.ActiveSheet;
Microsoft.Office.Interop.Excel.Range sheetCells = objSHT.Cells;
Microsoft.Office.Interop.Excel.Range cellFirst = sheetCells[1, 1] as Microsoft.Office.Interop.Excel.Range;
Microsoft.Office.Interop.Excel.Range cellLast = sheetCells[dt.Rows.Count, dt.Columns.Count] as Microsoft.Office.Interop.Excel.Range;
Microsoft.Office.Interop.Excel.Range theRange = objSHT.get_Range(cellFirst, cellLast);
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
//Condition to put column names in 1st row
//Excel work book indexes start from 1,1 and not 0,0
if (j == 0)
{
if (dt.Columns[i].ColumnName.ToString().Equals(null))
{
arr[0, i] = string.Empty;
}
else
{
arr[0, i] = dt.Columns[i].ColumnName.ToString();
}
}
if (dt.Rows[j][i].ToString().Equals(null))
{
arr[j + 1, i] = string.Empty;
}
else
{
arr[j + 1, i] = dt.Rows[j][i].ToString();
}
}
}
theRange.set_Value(Type.Missing, arr);
//Incrementing sheet count
sheetcount++;
}
//Saving the work book
objWB.Saved = true;
objWB.SaveCopyAs(x);
//Closing work book
objWB.Close();
//Closing excel application
objXL.Quit();
System.Windows.Forms.MessageBox.Show("Data Saved Successfully", "DBF to XLS Conversion Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
/*catch (Exception ex)
*
* {
*
* objWB.Saved = true;
*
* //Closing work book
*
* objWB.Close();
*
* //Closing excel application
*
* objXL.Quit();
*
*
* MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
* }*/
}
/// <summary>
/// 根据10月数据
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List<OffsetItem> LoadOffsetOctober(string filePath)
{
List<OffsetItem> ots = new List<OffsetItem>();
int sheetNo = 1;
if (MyApp == null) MyApp = new Microsoft.Office.Interop.Excel.Application();
ExcelTool.MyApp.Visible = false;
ExcelTool.MyBook = MyApp.Workbooks.Open(filePath);
ExcelTool.MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
for (int index = 2; index <= lastRow; index++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "V" + index.ToString()).Cells.Value;
if (MyValues.GetValue(1, 2) == null && MyValues.GetValue(1, 3) == null)
{
Console.WriteLine("OffsetItem no value on row " + index.ToString());
WriteErrorOLEDB(MyValues, string.Format("原{0},数据不完整",index), false);
continue;
}
ots.Add(new OffsetItem
{
//根据10月数据import
OriginalRow=index,
Apply_Number = "",
Worker_Number = GetValue(MyValues,2), //B
Worker_CnName = GetValue(MyValues,3), //C
Worker_Dept = GetValue(MyValues, 19), //S
Worker_Group = GetValue(MyValues,1), //A
CreateEd = GetValue(MyValues,13), //M column
Offset_StartEd = GetValue(MyValues,13), //M
Offset_StartTime = GetValue(MyValues,14),//N
Offset_EndEd = GetValue(MyValues,15), //M
Offset_EndTime = GetValue(MyValues,16),//P
RemoveOffset_Hours = GetValue(MyValues, 12), //L
Offset_Hours = GetValue(MyValues,18), //R
Offset_Days = "0",
App1 = "",
App2 = "",
OT_Numbers = "", //no value
//如果没有，可以为空，系统导入时自动生成
Offset_Number =GetValue(MyValues,11), //K column
Shift_Id=GetValue(MyValues,4),//D column
OT_WorkEd=GetValue(MyValues,5),//E
OT_Work_StartTime = GetValue(MyValues, 6), //F
OT_Work_EndTime=GetValue(MyValues,7), //G
OT_Work_Comment=GetValue(MyValues,8), //H
OT_Hours = GetValue(MyValues, 9), //I
OT_CompensateRate = GetValue(MyValues, 10), //J
Status = GetValue(MyValues,17) //Q column,已获批准 待审 草稿
});
}
return ots;
}
private void ExportToExcel_gp()
{
if (dt_gp.Rows.Count == 0) { MessageBox.Show("ไม่มีข้อมูล"); return; }
var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;
try
{
this.Cursor = Cursors.WaitCursor;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0; int z = 0;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 12]].Font.Bold = true;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 1] = "วันที่";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].EntireColumn.ColumnWidth = 13;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 2] = "เลขที่ขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 3] = "รหัสสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].EntireColumn.ColumnWidth = 60;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 4] = "ชื่อสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 5] = "จำนวน";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 6] = "หน่วยนับ";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 7] = "ราคาขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 8] = "ราคาทุน";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 9] = "กำไรขั้นต้น";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 10] = "GP%";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 11] = "ราคาขายรวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 12] = "ราคาทุนรวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 13] = "กำไร(ขาดทุน) รวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 14] = "ชื่อผู้ขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 15] = "ชื่อลูกค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 16] = gvGP.Columns[15].HeaderText;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 17] = gvGP.Columns[16].HeaderText;
object[,] oo = new object[dt_gp.Rows.Count, dt_gp.Columns.Count];
for (i = 0; i < dt_gp.Rows.Count; i++)
{
for (int j = 0; j < dt_gp.Columns.Count; j++)
{
string value = "";
try
{
decimal num = 0;
DateTime dateTime = new DateTime();
value = dt_gp.Rows[i][j].ToString();
if (DateTime.TryParse(value, out dateTime) && !Decimal.TryParse(value, out num))
{
oo[i, j] = (dateTime).ToOADate();
//xlWorkSheet.Cells[i + 2, j + 1] = (dateTime).ToOADate();
//xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).NumberFormat = "dd-mm-yyyy";
//xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
else
{
oo[i, j] = dt_gp.Rows[i][j].ToString();
//xlWorkSheet.Cells[i + 2, j + 1] = dt_gp.Rows[i][j].ToString();
}
}
catch (Exception ex)
{
}
}
}
string excelRange = string.Format("A2:{0}{1}",
findColumnLetter(dt_gp.Columns.Count), dt_gp.Rows.Count + 1);
xlWorkSheet.get_Range(excelRange, Type.Missing).Value2 = oo;
xlWorkSheet.get_Range("A2", "A" + dt_gp.Rows.Count + 1).NumberFormat = "dd-mm-yyyy";
xlWorkSheet.get_Range("G2", "G" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
xlWorkSheet.get_Range("H2", "H" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
xlWorkSheet.get_Range("I2", "I" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
string fileName = String.Empty;
SaveFileDialog saveFileExcel = new SaveFileDialog();
saveFileExcel.FileName = "" + DBConnString.sDb + " GP ตั้งแต่วันที่ " + dateTimePicker1.Text + " ถึง " + dateTimePicker2.Text + " ";
saveFileExcel.Filter = "Excel files (*.xls,*.xlsx)|*.xls*";
saveFileExcel.FilterIndex = 2;
saveFileExcel.RestoreDirectory = true;
if (saveFileExcel.ShowDialog() == DialogResult.OK)
{
fileName = saveFileExcel.FileName;
xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
this.Cursor = Cursors.Default;
MessageBox.Show("Export " + saveFileExcel.FileName + " Complete.");
}
else
{
return;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// 根据10月数据import
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List<OTItem> LoadOTOctober(string filePath)
{
List<OTItem> ots = new List<OTItem>();
int sheetNo =1;
if (MyApp == null) MyApp=new Microsoft.Office.Interop.Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(filePath);
MySheet = (Worksheet)MyBook.Sheets[sheetNo]; // Explict cast is not required here
int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
for (int index = 4; index <= lastRow; index++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "P" + index.ToString()).Cells.Value;
if ((MyValues.GetValue(1, 1) == null && MyValues.GetValue(1, 2) == null) || MyValues.GetValue(1, 1).ToString().Trim()=="")
{
Console.WriteLine("OTItem no value on row " + index.ToString());
WriteErrorOLEDB(MyValues, string.Format("原{0},数据不完整",index),true);
continue;
}
RowIndex = 1;
ots.Add(new OTItem
{
OriginalRow=index,
OT_ApplyNumber = "",
Worker_Number = GetValue(MyValues,1), //A
Worker_CnName = GetValue(MyValues,2), //B
Worker_Dept = GetValue(MyValues,3), //C
Worker_Group = GetValue(MyValues,4), //D
Cycle_StartEd = GetValue(MyValues,5), //E
Cycle_EndEd = GetValue(MyValues,6), //F
Create_Ed = GetValue(MyValues,7), //G
OT_StartEd = GetValue(MyValues,8), //H
OT_StartTime = GetValue(MyValues,9,true), //I
OT_EndEd = GetValue(MyValues,8), //H
OT_EndTime = GetValue(MyValues,10,true),//J
Statistic_Date = "",
OT_Hours = GetValue(MyValues,11),//K
Pay_Hours = GetValue(MyValues,12),//L
Offset_Hours = GetValue(MyValues,13), //M
LeftOffset_Hour = "",
LeftChange_Hour = "",
//需要为导入数据确定一个加班种类,the value is -1
OT_WorkType ="-1",
Reason = GetValue(MyValues,14), //N
Comment = "",
App1 = "",
App2 = "",
//审批状态 固定为“审批完成”旧数据请考虑都走完审批再导入到新系统
Status = "审批完成",
Apply_Type ="",
//加班号,唯一标识一次加班的号码 "OD"
OT_Number ="",
Attendance_StartEd = "",
Attendance_EndEd = "",
Shift_Id = GetValue(MyValues,15), //o
Compensate_Rate = GetValue(MyValues,16) //P
});
}
return ots;
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;
DataGridView DgvResult = dgvSummarySaleMonthProduct;
DataGridView DgvResult2 = dgvSummarySaleMonthProductMonth;
misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
int c = DgvResult.Columns.Count;
int r = DgvResult.Rows.Count;
for (int i = 0; i < c; i++)
{
xlWorkSheet.Cells[1, i + 1] = DgvResult.Columns[i].HeaderText.ToString();
}
for (int i = 0; i < r; i++)
{
if (backgroundWorker1.CancellationPending)
{
e.Cancel = true;
}
else
{
for (int j = 0; j < c; j++)
{
xlWorkSheet.Cells[i + 2, j + 1] = DgvResult.Rows[i].Cells[j].Value.ToString();
}
backgroundWorker1.ReportProgress(98 * i / (r - 1));
}
}
c = DgvResult2.Columns.Count;
xlWorkSheet.Cells[r + 2, 1] = DgvResult2.Rows[0].Cells[0].Value.ToString();
for (int j = 1; j < c; j++)
{
xlWorkSheet.Cells[r + 2, j + 2] = DgvResult2.Rows[0].Cells[j].Value.ToString();
}
backgroundWorker1.ReportProgress(99);
if (backgroundWorker1.CancellationPending)
{
e.Cancel = true;
backgroundWorker1.ReportProgress(0);
}
else
{
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, c + 1]).Borders.Weight = 2d;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Font.Name = "Arial";
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Font.Size = 10;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).EntireColumn.AutoFit();
xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, 2]).Merge(Type.Missing);
xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlWorkSheet.get_Range(xlWorkSheet.Cells[r + 1 + 1, 1], xlWorkSheet.Cells[r + 1 + 1, c + 1]).Borders.Weight = 2d;
/////////// End Create file Excel ///////////
}
}
catch
{
MessageBox.Show("ผิดพลาด");
e.Cancel = true;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
//this.Cursor = Cursors.Default;
}
}
//Obtiene el archivo y comienza a procesarlo
private void procesarBtn_Click(object sender, EventArgs e)
{
//Crea el documento
XmlDocument doc = new XmlDocument();
//Crea la raiz y le da formato
XmlElement rootElement = (XmlElement)doc.AppendChild(doc.CreateElement("informacionAFIP"));
rootElement.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
//Agrega el periodo de información
DateTime dt = Convert.ToDateTime(dateText.Text);
rootElement.AppendChild(doc.CreateElement("periodoInformacion")).InnerText = dt.ToString("yyyyMMdd");
//Agrega el rut y dígito de la entidad
rootElement.AppendChild(doc.CreateElement("rutEntidad")).InnerText = rutText.Text;
rootElement.AppendChild(doc.CreateElement("digitoEntidad")).InnerText = dvText.Text;
//Agrega los fondos como elemento
XmlElement fondosRootElement = (XmlElement)rootElement.AppendChild(doc.CreateElement("fondos"));
//Procesa el archivo Excel para
//1) Crear los fondos de inversión privados
//2) Agregar aportantes a esos fondos
this.MyApp = new Excel.Application();
this.MyApp.Visible = false;
this.MyBook = MyApp.Workbooks.Open(archivoText.Text);
this.MySheet = (Excel.Worksheet)MyBook.Sheets[1];
int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//El primer proceso es para crear un list con los fondos de inversión privada
for (int index = 2; index <= lastRow - 1; index++)
{
//Obtiene la fila
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "R" + index.ToString()).Cells.Value;
//Obtiene los datos del Excel
string fipRut = MyValues.GetValue(1, 4).ToString();
string digitoFondo = MyValues.GetValue(1, 5).ToString();
string denominacionFondo = MyValues.GetValue(1, 6).ToString();
string valorActivos = MyValues.GetValue(1, 7).ToString();
string valorPasivos = MyValues.GetValue(1, 8).ToString();
string criteriosContables = MyValues.GetValue(1, 9).ToString();
//Comprueba si existe el FIP para agregarlo a la lista
if (!fipList.Any(obj => obj.rutFondo == fipRut))
{
fipList.Add(
new Fip
{
rutFondo = fipRut,
digitoFondo = digitoFondo,
denominacionFondo = denominacionFondo,
valorActivos = valorActivos,
valorPasivos = valorPasivos,
criteriosContables = criteriosContables
}
);
}
}
//El segundo proceso es para agregar aportantes a la lista de fondos de inversión privada
foreach(Fip fip in fipList)
{
//Agrega los fondos de inversión al XML
XmlElement newFipElement = (XmlElement)fondosRootElement.AppendChild(doc.CreateElement("fondoInversionPrivado"));
//Agrega el contenido de esos elementos
newFipElement.AppendChild(doc.CreateElement("rutFondo")).InnerText = fip.rutFondo;
newFipElement.AppendChild(doc.CreateElement("digitoFondo")).InnerText = fip.digitoFondo;
newFipElement.AppendChild(doc.CreateElement("denominacionFondo")).InnerText = fip.denominacionFondo;
newFipElement.AppendChild(doc.CreateElement("valorActivos")).InnerText = fip.valorActivos;
newFipElement.AppendChild(doc.CreateElement("valorPasivos")).InnerText = fip.valorPasivos;
newFipElement.AppendChild(doc.CreateElement("criteriosContables")).InnerText = fip.criteriosContables;
//Agrega un nodo nuevo para los participes
newFipElement.AppendChild(doc.CreateElement("participes"));
}
//El tercer proceso es para agregar a todos los aportantes a los fondos de inversión privada
XmlNodeList fipNodeList = doc.SelectNodes("informacionAFIP/fondos/fondoInversionPrivado");
foreach (XmlNode fipNode in fipNodeList)
{
//Recorre todos los registros nuevamente para agregarlos al fondo correspondiente
for (int index = 2; index <= lastRow - 1; index++)
{
//Obtiene la fila
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "R" + index.ToString()).Cells.Value;
//Si es igual lo agrega
if (fipNode["rutFondo"].InnerText == MyValues.GetValue(1, 4).ToString())
{
//Valores null
string apellidoPaterno;
string apellidoMaterno;
string primerNombre;
string segundoNombre;
if (MyValues.GetValue(1, 13) == null){ apellidoPaterno = "";}else{ apellidoPaterno = MyValues.GetValue(1, 13).ToString();}
if (MyValues.GetValue(1, 14) == null) { apellidoMaterno = ""; } else { apellidoMaterno = MyValues.GetValue(1, 14).ToString(); }
if (MyValues.GetValue(1, 15) == null) { primerNombre = ""; } else { primerNombre = MyValues.GetValue(1, 15).ToString(); }
if (MyValues.GetValue(1, 16) == null) { segundoNombre = ""; } else { segundoNombre = MyValues.GetValue(1, 16).ToString(); }
XmlElement newFipAportant = (XmlElement)fipNode["participes"].AppendChild(doc.CreateElement("aportante"));
newFipAportant.AppendChild(doc.CreateElement("rutAportante")).InnerText = MyValues.GetValue(1, 10).ToString();
newFipAportant.AppendChild(doc.CreateElement("digitoAportante")).InnerText = MyValues.GetValue(1, 11).ToString();
newFipAportant.AppendChild(doc.CreateElement("extranjero")).InnerText = MyValues.GetValue(1, 12).ToString();
newFipAportant.AppendChild(doc.CreateElement("apellidoPaterno")).InnerText = apellidoPaterno;
newFipAportant.AppendChild(doc.CreateElement("apellidoMaterno")).InnerText = apellidoMaterno;
newFipAportant.AppendChild(doc.CreateElement("primerNombre")).InnerText = primerNombre;
newFipAportant.AppendChild(doc.CreateElement("segundoNombre")).InnerText = segundoNombre;
newFipAportant.AppendChild(doc.CreateElement("montoParticipacion")).InnerText = MyValues.GetValue(1, 17).ToString();
newFipAportant.AppendChild(doc.CreateElement("numeroCuotas")).InnerText = MyValues.GetValue(1, 18).ToString();
}
}
}
doc.Save(rutaExportText.Text + @"\svsFile.xml");
MessageBox.Show("Archivo guardado en: " + rutaExportText.Text + @"\svsFile.xml");
//ValidateXmlWithXsd(@"C:\svsFile" + dateText.Text + ".xml", Application.StartupPath + @"\\fpapo.xsd");
}
private void SaveToExcelAutomatic(DataGridView dataGridView1, string fileName, bool isShowExcle)
{
int iRows = 0;
int iCols = 0;
int iTrueCols = 0;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet ws = null;
if (wb.Worksheets.Count > 0)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
else
{
wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
if (ws != null)
{
ws.Name = "SheetName";
iRows = dataGridView1.Rows.Count; //加上列头行
iTrueCols = dataGridView1.Columns.Count; //包含隐藏的列，一共有多少列
//求列数，省略Visible = false的列
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (dataGridView1.Columns[i].Visible)
{
iCols++;
}
}
string[,] dimArray = new string[iRows + 1, iCols];
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
dimArray[0, k] = dataGridView1.Columns[j].HeaderText;
k++;
}
}
for (int i = 0; i < iRows; i++)
{
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
if (dataGridView1.Rows[i].Cells[j].Value != null)
{
dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
else
{
dimArray[i + 1, k] = "";
}
k++;
}
}
}
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;
//ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);
//ws.Columns.c = datagridview.Columns[j].Width;
k++;
}
}
}
//保存
wb.SaveCopyAs(fileName);
wb.Saved = true;
//app.Visible = true;
}
static void Main(string[] args)
{
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
string arquivo = @"\\D5668m001e035\operacoes\Andre\201610_RELATORIO_ANALÍTICO_VENDAS__CREDSYSTEM_251020160123.xlsx";
string arquivoCarga = Path.Combine(@"\\D5668m001e035\OPERACOES\Andre\Carga\", Path.GetFileName(arquivo));
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + arquivoCarga + "; Extended Properties='Excel 12.0; HDR=NO'";
string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbTeste_Carga;Data Source=D5668M001E035;Connect Timeout=0";
// Copia o arquivo para o diretório de carga para preservar o original:
File.Copy(arquivo, arquivoCarga, true);
try
{
app = new Excel.Application();
books = app.Workbooks;
book = app.Workbooks.Open(arquivoCarga, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
List <string> lista = new List <string>();
// Pega os nomes das planilhas:
foreach (Excel.Worksheet plan in book.Worksheets)
{
lista.Add((string)plan.Name);
}
// Para cada planilha que não seja de TOTAIS:
foreach (string nome in lista)
{
if (!nome.Contains("TOTAIS"))
{
sheet = book.Worksheets.get_Item(nome);
// Deleta as 7 primeiras linhas que não fazem parte do processo:
Excel.Range range = sheet.get_Range("A1", "A7".ToString());
range.EntireRow.Delete(Excel.XlDirection.xlUp);
book.Save();
using (OleDbConnection oleDbConnection = new OleDbConnection(excelConnectionString))
{
oleDbConnection.Open();
using (DataTable dataTable = new DataTable())
{
using (OleDbCommand oleDbCommand = new OleDbCommand("SELECT * FROM [" + nome + "$]", oleDbConnection))
{
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand))
{
oleDbDataAdapter.Fill(dataTable);
}
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnectionString))
{
sqlBulkCopy.DestinationTableName = "tbBS_tmp_CredSystem";
sqlBulkCopy.ColumnMappings.Add(0, "Semana");
sqlBulkCopy.ColumnMappings.Add(1, "Data Inc Documento");
sqlBulkCopy.ColumnMappings.Add(2, "Nome da Loja");
sqlBulkCopy.ColumnMappings.Add(3, "Número Produto");
sqlBulkCopy.ColumnMappings.Add(4, "Nome Campanha");
sqlBulkCopy.ColumnMappings.Add(5, "Número de Parcelas");
sqlBulkCopy.ColumnMappings.Add(6, "Data Emissão");
sqlBulkCopy.ColumnMappings.Add(7, "Dia do Processamento");
sqlBulkCopy.ColumnMappings.Add(8, "Número Apólice");
sqlBulkCopy.ColumnMappings.Add(9, "Número Certificado");
sqlBulkCopy.ColumnMappings.Add(10, "Prêmio Documento");
sqlBulkCopy.ColumnMappings.Add(11, "Número Cliente");
sqlBulkCopy.ColumnMappings.Add(12, "Chave Segurado").ToString(); // Se o campo for numérico e estiver perdendo formatação, utilizar o ToString()
sqlBulkCopy.ColumnMappings.Add(13, "Nome Segurado");
sqlBulkCopy.ColumnMappings.Add(14, "Número Campanha");
sqlBulkCopy.ColumnMappings.Add(15, "Início de Vigência");
sqlBulkCopy.ColumnMappings.Add(16, "Data Liberação");
sqlBulkCopy.ColumnMappings.Add(17, "Fim Vigência");
sqlBulkCopy.ColumnMappings.Add(18, "Número Forma Parcelamento");
sqlBulkCopy.ColumnMappings.Add(19, "Número Ciclo Adm Cobrança");
sqlBulkCopy.WriteToServer(dataTable);
}
}
}
}
}
book.Close(true, Type.Missing, Type.Missing);
app.Quit();
// Deleta o arquivo modificado ao final da carga:
File.Delete(arquivoCarga);
}
catch (Exception e)
{
throw e;
}
finally
{
KillProcessoExcelEspecifico(arquivoCarga);
//Console.ReadKey();
}
}
private void btnFCM_Click(object sender, EventArgs e)
{
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\FCM-SX- " + Quotation.Code + ".xlsx";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM-SX-TEMPLATE.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo FCM..."))
{
DataTable dtQuotation = LibQLSX.Select("exec spGetQuotation @QuotationID = " + Quotation.ID);
DataTable dtAllCost = new DataTable();
if (Quotation.StatusNC == 1)
{
dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_DN] " + Quotation.ID);
}
else
{
dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_CN] " + Quotation.ID);
}
decimal totalNC_ThietKe1 = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'"));
DataTable dtGroup = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCode");
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
try
{
app = new Excel.Application();
app.Workbooks.Open(localPath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
workSheet.Cells[4, 4] = "KD";
workSheet.Cells[4, 6] = Quotation.DeliveryTime;
workSheet.Cells[5, 4] = TextUtils.ToString(Quotation.ProjectCode);
workSheet.Cells[6, 4] = TextUtils.ToString(Quotation.ProjectName);
workSheet.Cells[7, 4] = TextUtils.ToString(Quotation.CustomerName);
workSheet.Cells[8, 8] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]);
workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["DName"]);
workSheet.Cells[9, 7] = TextUtils.ToString(Quotation.POnumber);
//Lợi nhuận
workSheet.Cells[14, 8] = TextUtils.ToDecimal(txtTotalProfit.EditValue);
//Giá bán
workSheet.Cells[16, 8] = TextUtils.ToDecimal(txtTotalTPA.EditValue);//Giá bán chưa VAT
workSheet.Cells[16, 9] = TextUtils.ToDecimal(txtTotalHD.EditValue);
//Chi phí vật tư
workSheet.Cells[19, 8] = TextUtils.ToDecimal(txtTotalVT.EditValue);//Giá vật tư
workSheet.Cells[19, 9] = TextUtils.ToDecimal(txtTotalVT.EditValue);
decimal totalNC_GianTiep = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N01'"));
workSheet.Cells[23, 6] = totalNC_GianTiep / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[23, 8] = totalNC_GianTiep;
workSheet.Cells[23, 9] = totalNC_GianTiep;//Chi phí cố định - đầu nhân viên gián tiếp
//Chi phí phân bổ khác
decimal totalPB_QuanLy = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N08'"));
workSheet.Cells[35, 6] = totalPB_QuanLy / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[35, 9] = totalPB_QuanLy;//Chi phí quản lí
decimal totalPB_TaiChinh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N09'"));
workSheet.Cells[36, 6] = totalPB_TaiChinh / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[36, 9] = totalPB_TaiChinh; //Chi phí tài chính và lãi vay
workSheet.Cells[37, 9] = TextUtils.ToDecimal(txtTotalDP.EditValue); //Chi phí dự phòng
decimal totalPB_BaoHanh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N11'"));
workSheet.Cells[43, 6] = totalPB_BaoHanh / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[43, 9] = totalPB_BaoHanh;//Chi phí bảo hành
if (Quotation.StatusNC == 1)
{
//Chi phí nhân công
decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N02'"));
workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế
decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N03'"));
workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR
//Chi phí kỹ thuật
workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán
workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại
//workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán
workSheet.Cells[30, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán
decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N04'"));
workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow)
decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.02'"));
workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow)
decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.03'"));
workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow)
}
else
{
//Chi phí nhân công
decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'"));
workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế
decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P07'"));
workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR
//Chi phí kỹ thuật
workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán
workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại
//workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán
//workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán
decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P12'"));
workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow)
decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PLD'"));
workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow)
decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PCG'"));
workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow)
}
for (int i = dtGroup.Rows.Count - 1; i >= 0; i--)
{
string groupCode = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
DataRow[] listCost = dtAllCost.AsEnumerable()
.Where(y => y.Field <string>("GroupCode") == groupCode)
.ToArray();
for (int k = listCost.Length - 1; k >= 0; k--)
{
workSheet.Cells[46, 2] = TextUtils.ToString(listCost[k]["Code"]);
workSheet.Cells[46, 3] = TextUtils.ToString(listCost[k]["Name"]);
//workSheet.Cells[46, 4] = TextUtils.ToString(listCost[k]["Name"]);
workSheet.Cells[46, 5] = "Đồng";
//workSheet.Cells[46, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]);
workSheet.Cells[46, 8] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]);
((Excel.Range)workSheet.Rows[46]).Insert();
Excel.Range range = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]);
range.Merge(true);
}
workSheet.Cells[46, 2] = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
workSheet.Cells[46, 3] = TextUtils.ToString(dtGroup.Rows[i]["GroupName"]);
((Excel.Range)workSheet.Rows[46]).Font.Bold = true;
((Excel.Range)workSheet.Rows[46]).Insert();
Excel.Range range1 = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]);
range1.Merge(true);
}
((Excel.Range)workSheet.Rows[45]).Delete();
((Excel.Range)workSheet.Rows[45]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(localPath);
}
}
private void Step3_Property_Excel()
{
for (int i = 1; i <= 1; i++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(i);
Excel.Range rng = (Excel.Range)xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[3, 3]);
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).Borders.Weight = 2d;
//xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]).EntireColumn.NumberFormat = "DD/MM/YYYY";
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).Font.Name = "Arial";
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).Font.Size = 10;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).EntireColumn.AutoFit();
/////////// End Create file Excel ///////////
}
}
public string GetReport()
{
try
{
object misValue = System.Reflection.Missing.Value;
_excelApp = new Excel.Application();
_excelBook = _excelApp.Workbooks.Add(misValue);
_excelSheet = (Excel.Worksheet)_excelBook.Worksheets.get_Item(1);
_excelSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
_excelApp.Cells[1, 1] = "ЗАО «Специализированная Автошкола»";
_excelApp.Cells[2, 1] = "Юридический адрес:";
_excelApp.Cells[3, 1] = "г. Могилев, ул. Школьная 16-405";
_excelApp.Cells[4, 1] = "УНН: 600359652";
_excelApp.Cells[5, 1] = "Р/с 3012205280013 в Ф-л ОАО Бел";
_excelApp.Cells[6, 1] = "АПБ МОУ г. Могилев,";
_excelApp.Cells[7, 1] = "пр-т Мира 91 код 942";
_excelApp.Cells[8, 1] = "Телефон:(222) 290-45-65";
_excelApp.Cells[9, 1] = "Директор: Хацкевич Андрей Евгеньевич";
_excelApp.Cells[11, 1] = "Расписание теоритических занятий";
_excelApp.Cells[12, 1] = string.Format("на {0} {1} г.", GetMonthName(Month), Year);
_excelApp.Cells[14, 1] = "Дата/Время";
_excelApp.Cells.get_Range("A14", "B14").Merge(Type.Missing);
_excelApp.Columns[1].ColumnWidth = 15;
_excelApp.Cells[14, 1].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[1].VerticalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[2].ColumnWidth = 11;
_excelApp.Columns[2].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[2].VerticalAlignment = Excel.Constants.xlCenter;
//вывод времени
var times = Lessons.Select(l => l.Lessons.Select(t => new
{
StartTime = t.StartTime,
EndTime = t.EndTime,
})).SelectMany(t => t.Select(d => d)).Distinct().OrderBy(d => d.StartTime);
int col = 3;
foreach (var t in times)
{
_excelApp.Cells[14, col] = t.StartTime.ToString() + "-" + t.EndTime.ToString();
_excelApp.Columns[col].ColumnWidth = 19;
_excelApp.Columns[col].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[col].VerticalAlignment = Excel.Constants.xlCenter;
col++;
}
int colCount = col - 1;
var cellHeader1 = (Excel.Range)_excelApp.Cells[11, 1];
var cellHeader2 = (Excel.Range)_excelApp.Cells[11, colCount];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
cellHeader1 = (Excel.Range)_excelApp.Cells[12, 1];
cellHeader2 = (Excel.Range)_excelApp.Cells[12, colCount];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
//вывод дат
int row = 15;
foreach (var l in Lessons.OrderBy(l => l.Dates.OrderBy(d => d.Day).FirstOrDefault().DayOfWeek))
{
_excelApp.Cells[row, 1] = l.DayOfWeek;
int rowStart = row;
foreach (var d in l.Dates.OrderBy(d => d.Date))
{
_excelApp.Cells[row, 2] = d.ToShortDateString();
row++;
}
int rowEnd = row - 1;
_excelApp.Cells.get_Range(string.Format("A{0}", rowStart), string.Format("A{0}", rowEnd)).Merge(Type.Missing);
for (int i = 3; i < col; i++)
{
Excel.Range cellStart = (Excel.Range)_excelApp.Cells[rowStart, i];
Excel.Range cellEnd = (Excel.Range)_excelApp.Cells[rowEnd, i];
_excelApp.Cells.get_Range(cellStart, cellEnd).Merge(Type.Missing);
}
}
//вывод занятий
for (int i = 3; i < col; i++)
{
for (int j = 15; j < row; j++)
{
string dayOfWeek = _excelApp.Cells[j, 1].Text;
string str1 = ((string)_excelApp.Cells[14, i].Text).Substring(0, 8);
TimeSpan startTime = TimeSpan.Parse(str1);
string str2 = ((string)_excelApp.Cells[14, i].Text).Substring(9, 8);
TimeSpan endTime = TimeSpan.Parse(str2);
var less = Lessons.Where(l => l.DayOfWeek == dayOfWeek).FirstOrDefault();
if (less != null)
{
var item = less.Lessons.Where(l => l.StartTime == startTime && l.EndTime == endTime).FirstOrDefault();
if (item != null)
{
_excelApp.Cells[j, i] = item.Group + "\n" + item.Instructor;
}
}
}
}
Excel.Range cell1 = (Excel.Range)_excelApp.Cells[14, 1];
Excel.Range cell2 = (Excel.Range)_excelApp.Cells[row - 1, col - 1];
var cells = _excelSheet.get_Range(cell1, cell2); // выделяем
cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
cells.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // правая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // левая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // нижняя внешняя
cells.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние вертикальные
cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние горизонтальные
//_excelBook.SaveAs(_saveAsPath);
_excelBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Filename: _saveAsPath);
}
catch (Exception)
{
}
finally
{
_excelBook.Close(false);
_excelApp.Quit();
}
return _saveAsPath;
}
public void SyuYakuDL(DataGridView gr, DataTable ds)
{
string saveFileName = "集約_" + DateTime.Now.ToString("yyyyMMdd");
string templetFile = @"template\集約.xlsx";
SaveFileDialog saveDialog = new SaveFileDialog
{
Filter = "Excel文件 |*.xlsx",
FileName = saveFileName,
RestoreDirectory = true
};
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
File.Copy(templetFile, saveFileName, true);
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(saveFileName);
Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = "集約";
excel.Visible = false;
int excelRow = 0;
int number = 1;
int selectRow = 0;
try
{
log.Info("EXEC BEGIN");
//一行目値取得
//datagridview行数判断
for (int a = 0; a < gr.RowCount; a++)
{
//選択行
if ((bool)gr.Rows[a].Cells[0].EditedFormattedValue == true)
{
//DataSet行数判断
for (int i = 0; i < ds.Rows.Count; i++)
{
if (gr.Rows[a].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) &&
gr.Rows[a].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) &&
gr.Rows[a].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) &&
gr.Rows[a].Cells["SEQNO"].Value.Equals(ds.Rows[i][18]))
{
//NO行取得
worksheet.Cells[2, 1] = 1;
for (int r = 0; r < 19; r++)
{
worksheet.Cells[2, r + 2] = ds.Rows[i][r];
}
selectRow = a;
break;
}
else
{
continue;
}
}
break;
}
}
for (int b = selectRow + 1; b < gr.RowCount; b++)
{
if ((bool)gr.Rows[b].Cells[0].EditedFormattedValue == true)
{
for (int i = 0; i < ds.Rows.Count; i++)
{
if (gr.Rows[b].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) &&
gr.Rows[b].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) &&
gr.Rows[b].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) &&
gr.Rows[b].Cells["SEQNO"].Value.Equals(ds.Rows[i][18]))
{
Excel.Range RngToCopy = worksheet.get_Range("A2").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("A" + (number + 2)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
//NO行取得
number++;
worksheet.Cells[excelRow + 3, 1] = number;
for (int j = 0; j < 19; j++)
{
worksheet.Cells[excelRow + 3, j + 2] = ds.Rows[i][j];
}
excelRow++;
}
}
}
}
}
catch (Exception e)
{
log.Error("ERROR" + e.Message);
}
finally
{
Application.DoEvents();
var xml = XDocument.Load(@"..\Mitsui.xml");
var queryC = xml.Root.Descendants("rndflg")
.Elements("col")
.Select(a => a.Value);
List <string> cols = new List <string>();
foreach (var element in queryC)
{
cols = element.Split(',').ToList();
}
foreach (var b in cols)
{
worksheet.get_Range(b + ":" + b).Locked = false;
}
worksheet.Protect();
//workbook.Saved = true;
workbook.Save();
excel.Quit();
Marshal.FinalReleaseComObject(excel);
log.Info("EXEC END");
}
}
else
{
MessageBox.Show("処理を中止しました。");
}
}
private void btnExportToExcel_Click(object sender, EventArgs e)
{
TabPage tbSelected = tcMain.SelectedTab;
DataGridView dgvToCopy = new DataGridView();
foreach (Control ctMain in tbSelected.Controls)
{
foreach (Control ctSubLevel1 in ctMain.Controls)
{
Type type = ctSubLevel1.GetType();
if (ctSubLevel1.GetType().Name == "DataGridView")
{
dgvToCopy = (DataGridView)ctSubLevel1;
}
}
}
dgvToCopy.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
dgvToCopy.MultiSelect = true;
dgvToCopy.SelectAll();
DataObject dataObj = dgvToCopy.GetClipboardContent();
if (dataObj != null)
{
Clipboard.SetDataObject(dataObj);
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = tcMain.SelectedTab.Text + ".xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlexcel = new Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//Paste clipboard results to worksheet range
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// Delete blank column A and select cell A1
Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dgvToCopy.ClearSelection();
}
else
{
Clipboard.Clear();
dgvToCopy.ClearSelection();
}
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
exportProgress.Minimum = 0;
exportProgress.Maximum = TestInfoList.Count ;
exportProgress.Value = 0;
double value = 0;
UpdateProgressBarDelegate updatePbDelegate = new UpdateProgressBarDelegate(exportProgress.SetValue);
try
{
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(templatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
mySheet1.Activate();
int rowCount = TestInfoList.Count + 2; ;//总行数
//写入参数信息
int paramCount = 0;//参数行数
if (IsExportParams)
{
paramCount = ParamList.Count * 4;
for (int i = 0; i < ParamList.Count; i++)
{
Model.Parameter p = ParamList[i];
Range r = mySheet1.get_Range(mySheet1.Cells[1, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[1, testInfoColumnCount + i * 4 + 4]);
r.Merge();
r.Value = p.ParamName;
r.Font.Bold = true;
r.HorizontalAlignment = XlHAlign.xlHAlignCenter;
r.VerticalAlignment = XlHAlign.xlHAlignCenter;
Range r1 = mySheet1.get_Range(mySheet1.Cells[2, testInfoColumnCount + i * 4 + 1], mySheet1.Cells[2, testInfoColumnCount + i * 4 + 4]);
r1.Value2 = paramContents;
r1.Font.Bold = true;
r1.EntireColumn.AutoFit();
}
}
//写入测试信息
string[,] content = new string[rowCount, testInfoColumnCount + paramCount];
//double?[,] paramContent = new double?[rowCount, paramCount];
XDocument xdoc;
BLL.TB_Dict dictBLL = new BLL.TB_Dict();
for (int i = 0; i < TestInfoList.Count; i++)
{
int rowIndex = i;
Model.TestInfoModel model = TestInfoList[i];
content[rowIndex, 0] = string.Format("测试{0}", i + 1);//测试顺序
content[rowIndex, 1] = model.Ath_Name;//姓名
content[rowIndex, 2] = model.TestDate.ToString("yyyy-MM-dd HH:mm");//测试日期
content[rowIndex, 3] = model.DJoint;//测试关节
content[rowIndex, 4] = model.DJointSide;//测试侧
content[rowIndex, 5] = model.DPlane;//运动方式
content[rowIndex, 6] = model.DTestMode;//测试模式
content[rowIndex, 7] = model.MotionRange;//运动范围
content[rowIndex, 8] = model.Speed;//测试速度
content[rowIndex, 9] = model.Break;//休息时间
content[rowIndex, 10] = model.NOOfSets;//测试组数
content[rowIndex, 11] = model.NOOfRepetitions;//重复次数
content[rowIndex, 12] = model.DInsuredSide;//受伤测
content[rowIndex, 13] = model.DGravitycomp;//重力补偿
if (IsExportParams)
{
string xmlPath = Model.AppPath.XmlDataDirPath + model.DataFileName;
xdoc = XDocument.Load(xmlPath);
List<XElement> action1 = xdoc.Descendants("action1").Elements<XElement>().ToList<XElement>();
List<XElement> action2 = xdoc.Descendants("action2").Elements<XElement>().ToList<XElement>();
for (int j = 0; j < ParamList.Count; j++)
{
int paramOneColumnIndex = j * 4;
double p1;
if (double.TryParse(action1[ParamList[j].Index].Attribute("max").Value, out p1)) {
//paramContent[rowIndex, paramOneColumnIndex] = p1;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 1] = p1;
}
double p2;
if (double.TryParse(action1[ParamList[j].Index].Attribute("avg").Value, out p2))
{
//paramContent[rowIndex, paramOneColumnIndex + 1] = p2;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 2] = p2;
}
double p3;
if (double.TryParse(action2[ParamList[j].Index].Attribute("max").Value, out p3))
{
//paramContent[rowIndex, paramOneColumnIndex + 2] = p3;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 3] = p3;
}
double p4;
if (double.TryParse(action2[ParamList[j].Index].Attribute("avg").Value, out p4))
{
//paramContent[rowIndex, paramOneColumnIndex + 3] = p4;
mySheet1.Cells[rowIndex + 3, paramOneColumnIndex + testInfoColumnCount + 4] = p4;
}
}
}
//写进度条
value += 1;
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background,
new object[] { ProgressBar.ValueProperty, value });
}
//写入测试信息
Range range1 = mySheet1.get_Range(mySheet1.Cells[3, 1], mySheet1.Cells[rowCount, testInfoColumnCount]);
range1.Value2 = content;
//写入参数信息
//Range range2 = mySheet1.get_Range(mySheet1.Cells[3, testInfoColumnCount + 1], mySheet1.Cells[rowCount, testInfoColumnCount + paramCount]);
//range2.Value2 = paramContent;
//if (IsExportParams)
//{
// rowCount = TestInfoList.Count + (ParamList.Count + 1) * TestInfoList.Count + 1;//信息行数+信息行数×参数行数+第一行列头信息
// paramCount = ParamList.Count + 1;//参数行数加1行参数名
//}
//else {
// rowCount = TestInfoList.Count + 1;
//}
//string[,] content = new string[rowCount, 13];
//XDocument xdoc;
//Model.TB_Dict actionModel;
//BLL.TB_Dict dictBLL = new BLL.TB_Dict();
//for (int i = 0; i < TestInfoList.Count; i++) {
// int rowIndex = i + i * paramCount;
// Model.TestInfoModel model = TestInfoList[i];
// content[rowIndex, 0] = model.Ath_Name;//姓名
// content[rowIndex, 1] = model.TestDate.ToString("yyyy-MM-dd HH:mm");//测试日期
// content[rowIndex, 2] = model.DJoint;//测试关节
// content[rowIndex, 3] = model.DJointSide;//测试侧
// content[rowIndex, 4] = model.DPlane;//运动方式
// content[rowIndex, 5] = model.DTestMode;//测试模式
// content[rowIndex, 6] = model.MotionRange;//运动范围
// content[rowIndex, 7] = model.Speed;//测试速度
// content[rowIndex, 8] = model.Break;//休息时间
// content[rowIndex, 9] = model.NOOfSets;//测试组数
// content[rowIndex, 10] = model.NOOfRepetitions;//重复次数
// content[rowIndex, 11] = model.DInsuredSide;//受伤测
// content[rowIndex, 12] = model.DGravitycomp;//重力补偿
// if (IsExportParams) {
// //写入参数信息
// actionModel = dictBLL.GetModel(model.Joint, model.Plane, model.Test_Mode);
// content[rowIndex + 1, 0] = "所选测试顺序";
// content[rowIndex + 1, 1] = "参数";
// content[rowIndex + 1, 2] = actionModel.actionone + "(极值)";
// content[rowIndex + 1, 3] = actionModel.actionone + "(平均值)";
// content[rowIndex + 1, 4] = actionModel.actiontwo + "(极值)";
// content[rowIndex + 1, 5] = actionModel.actiontwo + "(平均值)";
// string xmlPath = Model.AppPath.XmlDataDirPath + model.DataFileName;
// xdoc = XDocument.Load(xmlPath);
// List<XElement> action1 = xdoc.Descendants("action1").Elements<XElement>().ToList<XElement>();
// List<XElement> action2 = xdoc.Descendants("action2").Elements<XElement>().ToList<XElement>();
// for (int j = 0; j < ParamList.Count; j++)
// {
// content[rowIndex + 1 + j + 1, 0] = "测试" + (i + 1);
// content[rowIndex + 1 + j + 1, 1] = ParamList[j].ParamName;
// content[rowIndex + 1 + j + 1, 2] = action1[ParamList[j].Index].Attribute("max").Value;
// content[rowIndex + 1 + j + 1, 3] = action1[ParamList[j].Index].Attribute("avg").Value;
// content[rowIndex + 1 + j + 1, 4] = action2[ParamList[j].Index].Attribute("max").Value;
// content[rowIndex + 1 + j + 1, 5] = action2[ParamList[j].Index].Attribute("avg").Value;
// }
// }
// //写进度条
// value += 1;
// Dispatcher.Invoke(updatePbDelegate,
// System.Windows.Threading.DispatcherPriority.Background,
// new object[] { ProgressBar.ValueProperty, value });
//}
//Range range = mySheet1.get_Range(mySheet1.Cells[2, 1], mySheet1.Cells[rowCount, 13]);
//range.Value2 = content;
mySheet1.SaveAs(choosePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
myWorkBooks.Close();
excelApp.Quit();
MessageBox.Show("导出成功！", "系统信息");
System.Windows.Window.GetWindow(this).Close();
}
catch (Exception ee) {
MessageBox.Show("导出出错！\r\n错误信息为：" + ee.Message, "系统错误");
}
}
public void completarTablas(double[] aleatorios, double[,] frecuencias, double chi, int intervalos)
{
//Completa tabla de numeros aleatorios
xlWorkSheet.Range["D1:H1"].Merge();
xlWorkSheet.Range["D1:H1"].Value = "Chi Cuadrado";
xlWorkSheet.Range["D1:H1"].Font.Size = 15;
xlWorkSheet.Range["D1:H1"].HorizontalAlignment = HorizontalAlignment.Center;
xlWorkSheet.Cells[3, 1] = "N";
xlWorkSheet.Cells[3, 2] = "Aleatorio";
//Formatea los campos numericos a 4 decimales
xlWorkSheet.Range["B:B"].NumberFormat = "0.0000";
xlWorkSheet.Range["E:E"].NumberFormat = "0.0000";
xlWorkSheet.Range["F:F"].NumberFormat = "0.0000";
for (int i = 0; i < aleatorios.Length; i++)
{
xlWorkSheet.Cells[i + 4, "A"] = i + 1;
xlWorkSheet.Cells[i + 4, "B"] = aleatorios[i];
}
//cabecera de la tabla
xlWorkSheet.Cells[3, 5] = "Mínimo";
xlWorkSheet.Cells[3, 6] = "Máximo";
xlWorkSheet.Cells[3, 7] = "O(i)";
xlWorkSheet.Cells[3, 8] = "E(i)";
xlWorkSheet.Cells[3, 9] = "(O-E)^2/E";
//Cargamos los datos
for (int i = 0; i < frecuencias.GetLength(1); i++)
{
xlWorkSheet.Cells[i + 4, "D"] = i + 1;
xlWorkSheet.Cells[i + 4, "E"] = frecuencias[0, i];
xlWorkSheet.Cells[i + 4, "F"] = frecuencias[1, i];
xlWorkSheet.Cells[i + 4, "G"] = frecuencias[2, i];
xlWorkSheet.Cells[i + 4, "H"] = frecuencias[3, i];
xlWorkSheet.Cells[i + 4, "I"] = frecuencias[4, i];
}
//Coloca el resultado final
xlWorkSheet.Cells[frecuencias.GetLength(1) + 4, "H"].value = "X^2";
xlWorkSheet.Cells[frecuencias.GetLength(1) + 4, "I"].value = chi;
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
int inter = 3 + intervalos;
chartRange = xlWorkSheet.get_Range("G3", "H" + inter);
chartPage.HasTitle = true;
chartPage.ChartTitle.Caption = "Chi Cuadrado de los Números Aleatorios Generados";
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage.HasLegend = true;
chartPage.ShowDataLabelsOverMaximum = true;
//Crea Excel
//xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlApp.Visible = true;
//xlWorkBook.Close(true, misValue, misValue);
//xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void Form1_Load(object sender, EventArgs e)
{
app = new Excel.Application();
app.Visible = false;
workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls");
worksheet = (Excel.Worksheet) workbook.ActiveSheet;
int i = 0;
names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
string current_date = DateTime.Now.ToString("dd.MM");
for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
{
string departament1_value = Convert.ToString(departaments1.Cells[i]);
string departament2_value = Convert.ToString(departaments1.Cells[i]);
string birthday_value = Convert.ToString(departaments1.Cells[i]);
string name_value = Convert.ToString(departaments1.Cells[i]);
if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД")
{
if (birthday_value == current_date)
label1.Text = "Сьогодні свій день народження відзначає " + name_value;
}
}
app.Quit();
}