public static void openXL(string xlp)
{
xla = new Excel.Application();
xlw = xla.Workbooks.Open(xlp);
xls = xlw.Worksheets.get_Item(1);
xlr = xls.UsedRange;
}
public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
/// <summary>
/// Class constructor does all the work
/// </summary>
/// <param name="excelApp"></param>
/// <param name="fileName"></param>
public StudentWorkbook( Excel.Application excelApp, string fileName )
{
try // to open the student's spreadsheet
{
excelWorkbook = excelApp.Workbooks.Open( fileName, 0,
true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, // read only
false, 0, true, false, false );
}
catch ( Exception e )
{
Console.WriteLine( "error: " + e.Message );
Console.WriteLine( "Could not open spreadsheet " + fileName );
}
Excel.Sheets excelSheets = excelWorkbook.Worksheets; // get the Worksheets collection
Excel.Worksheet excelWorksheet = excelSheets[ 1 ]; // get the first one
// get the Team Number cell
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" );
// try to convert this cell to an integer
if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 )
{
Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" );
}
// get the scores cells
scores = excelWorksheet.get_Range( "B7", "B15" );
// get the Additional Comments cell
comments = excelWorksheet.get_Range( "B18", "B18" );
} // end of StudentWorkbook()
public void addData(int row, int col, string data,
string cell1, string cell2, string format)
{
worksheet.Cells[row, col] = data;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.NumberFormat = format;
}
public Insert(Excel.Application application, CultureInfo culture)
{
try {
_excelapp = application;
_culture = culture;
_range = application.ActiveWindow.RangeSelection;
} catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
}
public Date(Excel.Application application, CultureInfo culture)
{
try {
_excelapp = application;
_culture = culture;
_range = application.ActiveWindow.RangeSelection;
_minDateTime = new DateTime(1900, 1, 1);
} catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
}
public Gen(Excel.Application application, CultureInfo culture)
{
try {
_excelapp = application;
_culture = culture;
_range = application.ActiveWindow.RangeSelection;
_singlesel = _range.Columns.Count == 1 && _range.Rows.Count == 1;
_fnb = _singlesel ? new FieldNumber("Number of rows", "", 100, 1, true) : null;
} catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
}
//Constructor that takes an Excel range as an argument
//The data is stored in the cells of the range
public NormalDistribution(Excel.Range r)
{
_cells = r;
_size = r.Count;
_mean = __mean();
_variance = __variance();
_standard_deviation = __standard_deviation();
_error = __error();
_ranked_errors = __rank_errors();
}
public void generatExcelNoSubmit(string f)
{
string file_Name = f;
m_oExcelApp = new Excel.Application();
m_oExcelApp.Visible = false;
m_oExcelApp.UserControl = false;
m_oSheet = null;
excelRange = null;
try
{
m_oBook = m_oExcelApp.Workbooks.Add(missing);
Excel.Sheets sheets = m_oBook.Worksheets;
//Add new 4 Sheet
//sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
//Product Sheet [Sheet1]
m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
m_oSheet.Activate();
m_oSheet.Name = "รายการเสนอซื้อ";
SetData_to_SheetNoSubmit();
string template = Application.StartupPath;
string strRunReport = file_Name;
//string strPass = "******"; password
m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
m_oExcelApp.Visible = true;
}
catch (interop.COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
if (m_oExcelApp == null)
{
m_oExcelApp.Quit();
m_oExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
private void Sheet1_Startup(object sender, System.EventArgs e)
{
rngUp = this.Range["B2", missing];
rngDown = this.Range["B3", missing];
rngInterest = this.Range["B4", missing];
rngInitial = this.Range["B5", missing];
rngPeriods = this.Range["B6", missing];
rngExercise = this.Range["B7", missing];
rngRuns = this.Range["B8", missing];
rngRemote = this.Range["B9", missing];
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
public static void openXL(string xlp)
{
try {
xla = new Excel.Application();
xlw = xla.Workbooks.Open(xlp);
xls = xlw.Sheets[1];
xlr = xls.UsedRange;
}
catch (Exception e)
{
w(" FAIL: " + e.ToString());
}
}
internal Worksheet(
ExcelApplication excel,
Workbook workbook,
Excel.Worksheet _worksheet,
ObjectDisposedCallback disposeCallback,
bool displayGridLines)
{
this.ExcelApplication = excel;
this.Workbook = workbook;
this._worksheet = _worksheet;
this._cells = _worksheet.Cells;
this.disposeCallback = disposeCallback;
this.Name = _worksheet.Name;
DisplayGridlines = displayGridLines;
}
protected override void subWrite()
{
fileColumn = worksheet.Columns[2];
directoryColumn = worksheet.Columns[1];
writeHeader();
writeFileNamesToColumnB(linkSourceDirectory);
DirectoryInfo[] subDirectories = linkSourceDirectory.GetDirectories();
foreach (var sub in subDirectories)
{
writeDirectoryPathToColumnA(sub);
writeFileNamesToColumnB(sub);
}
}
public void addData(int row, int col, string content, bool bold, bool italic, bool underline, int? size, string fontName)
{
//Data
worksheet.Cells[row, col] = content;
//Cell format
workSheet_range = worksheet.get_Range(string.Empty + new string((char)(col + 64), 1) + row.ToString());
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Bold = bold;
workSheet_range.Font.Italic = italic;
workSheet_range.Font.Underline = underline;
if (size != null)
workSheet_range.Font.Size = size;
if(fontName != null)
workSheet_range.Font.Name = fontName;
}
public static void ReplaceExcelRange(Range com, InputSample input)
{
bool done = false;
while (!done)
{
try
{
com.Value2 = input.GetInputArray();
done = true;
}
catch (Exception)
{
}
}
}
public static Excel1.Range ReadExcel(string filePath, int workSheetNumber)
{
try
{
string startupPath = System.IO.Directory.GetCurrentDirectory();
string outPutDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
string xmlfilepath = Path.Combine(outPutDirectory + filePath);
string xmlfile_path = new Uri(xmlfilepath).LocalPath;
xlApp = new Excel1.Application();
xlWorkBook = xlApp.Workbooks.Open(xmlfile_path, 0, false, 5, "", "", true, Excel1.XlPlatform.xlWindows, "\t", true, false, 0, true, true, false);
xlWorkSheet = (Excel1.Worksheet)xlWorkBook.Worksheets.get_Item(workSheetNumber);
return range = xlWorkSheet.UsedRange;
}
catch (Exception)
{
return null;
}
}
public void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
worksheet.Cells[row, col] = htext;
workSheet_range = worksheet.get_Range(cell1, cell2);
//workSheet_range.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GRAY":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GAINSBORO":
workSheet_range.Interior.Color =
System.Drawing.Color.Gainsboro.ToArgb();
break;
case "Turquoise":
workSheet_range.Interior.Color =
System.Drawing.Color.Turquoise.ToArgb();
break;
case "PeachPuff":
workSheet_range.Interior.Color =
System.Drawing.Color.PeachPuff.ToArgb();
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Bold = font;
workSheet_range.ColumnWidth = size;
if (fcolor.Equals(""))
{
workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
}
else
{
workSheet_range.Font.Color = System.Drawing.Color.Red;
}
}
public SpreadsheetSplitter(string filename, string destination_folder)
{
excel = new Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
workbook = excel.Workbooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
first_sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
string main_sheet_name = first_sheet.Name;
output_folder = destination_folder;
sheet_range = first_sheet.UsedRange;
heading_column_start = 1;
heading_column_end = sheet_range.Columns.Count - 1;
heading_row_start = 1;
heading_row_end = 2;
start_row = 3;
start_col = GetExcelColumnName(heading_column_start);
end_col = GetExcelColumnName(heading_column_end);
}
//Constructor that takes an array of TreeNodes as an argument
public NormalDistribution(AST.Range[] range_nodes, Excel.Application app)
{
//turn the dictionary into an Excel.Range
Excel.Range r1 = range_nodes[0].GetCOMObject(app);
foreach (AST.Range range_node in range_nodes)
{
try //in a try-catch because Union malfunctioned in one observed case
{
r1 = app.Union(r1, range_node.GetCOMObject(app));
} catch { }
}
_cells = r1;
_size = r1.Count;
_mean = __mean();
_variance = __variance();
_standard_deviation = __standard_deviation();
_error = __error();
_ranked_errors = __rank_errors();
}
//constructor
public CXLRTemplateDef(Excel.Range dsRange){
for(int i = 1; i <= dsRange.Rows.Count; i++) {
Excel.Range vCurRow = (Excel.Range)dsRange.Rows[i, Type.Missing];
String vCurDefValue = ExcelSrv.ExtractCellValue(vCurRow.Cells[1, 1]);
if (!String.IsNullOrEmpty(vCurDefValue) && vCurDefValue.StartsWith(csGrpHeaderDef)) {
String vFldName = ExcelSrv.ExtractFieldName(vCurDefValue);
this._headerFormats.Add(vFldName, vCurRow);
} else
if(!String.IsNullOrEmpty(vCurDefValue) && vCurDefValue.StartsWith(csGrpFooterDef)) {
String vFldName = ExcelSrv.ExtractFieldName(vCurDefValue);
this._footerFormats.Add(vFldName, vCurRow);
} else
if(!String.IsNullOrEmpty(vCurDefValue) && (vCurDefValue.Equals(csDetailsDef))) {
this._detailsRng = vCurRow;
} else
if(!String.IsNullOrEmpty(vCurDefValue) && (vCurDefValue.Equals(csTotalsDef))) {
this._totalsRng = vCurRow;
}
}
}
/// <summary>
/// Конструктор для инициалицазии переменных класса
/// </summary>
/// <param name="fileExcel">Путь к файлу для открытия</param>
public ExcelApplication(string fileExcel)
: base(fileExcel)
{
try
{
excelApp = new Excel.Application();
excelBook = excelApp.Workbooks.Open(fileToExcel);
excelSheet = (Excel.Worksheet)excelBook.Worksheets.get_Item(1);
excelRange = excelSheet.UsedRange;
}
catch
{
MessageBox.Show("Заданный файл Excel не доступен!");
//excelBook.Close(falseObj,missingObj,missingObj);
excelApp.Quit();
excelBook = null;
excelApp = null;
Application.Exit();
}
}
public void OpenExcelDoc(String ofd)
{
Thread.Sleep(1000);
application = new MExcel.Application();
try
{
workBook = application.Workbooks.Open(ofd);
}
catch (System.Runtime.InteropServices.COMException)
{
MessageBox.Show("Не удалось открыть файл Excel","Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information);
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
System.GC.Collect();
application = new MExcel.Application();
workBook = application.Workbooks.Open(ofd);
}
workSheet = (MExcel.Worksheet)workBook.Worksheets.get_Item(4);
range = workSheet.UsedRange;
}
/// <summary>
/// Открыть файл - адрес берется из характеристики
/// </summary>
public void OpenExcelDoc(/*String ofd*/)
{
Thread.Sleep(1000);
application = new MExcel.Application();
try
{
workBook = application.Workbooks.Open(characteristic.FileAdres);
}
catch (System.Runtime.InteropServices.COMException error)
{
//MessageBox.Show(error.Message, "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information);
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
System.GC.Collect();
application = new MExcel.Application();
workBook = application.Workbooks.Open(characteristic.FileAdres);
}
workSheet = (MExcel.Worksheet)workBook.Worksheets.get_Item(characteristic.NumberList);
range = workSheet.UsedRange;
}
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";
}
}
private void HttpsCompleted(object sender, DownloadStringCompletedEventArgs e)
{
if (e.Error == null)
{
string response = e.Result;
DataTable xmlData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(response);
//DataTable dtObj = new DataTable("MyExcel");
try
{
activeWorksheet.UsedRange.ClearContents();
foreach (DataColumn cols in xmlData.Columns)
{
newFirstRow.Value2 = cols.ColumnName;
newFirstRow = newFirstRow.Next;
}
newFirstRow = activeWorksheet.Cells[firstRow.Row + 1, firstRow.Column];
object[] data = new object[xmlData.Columns.Count];
foreach (DataRow dr in xmlData.Rows)
{
data = new object[xmlData.Columns.Count];
data = dr.ItemArray;
//= (Excel.Range)activeWorksheet.Cells[newFirstRow.Row, firstRow.Column]; ;
Excel.Range newRangeData = newFirstRow.get_Resize(1, xmlData.Columns.Count);
newRangeData.set_Value(Type.Missing, data);
newFirstRow = activeWorksheet.Cells[newFirstRow.Row + 1, firstRow.Column];
}
firstRow.get_Offset(1, 1).get_Resize(xmlData.Rows.Count, xmlData.Columns.Count).Style = "Currency";
}
catch(Exception ex)
{
firstRow.Value2 = "An Error occured while performing operation on the excel";
}
}
else
{
firstRow.Value2 = "An Error occured while accessing the service";
}
}
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false)
{
Excel.Series series;
Excel.Range temp;
range = range.get_Resize(2, fun.Length);
range.set_Value(Missing.Value, GetArray(fun, value));
if (addChart)
{
LastIndex += chartHeight + 10;
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
currentChart = chartObject.Chart;
series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries();
temp = range.get_Resize(1, fun.Length);
series.Values = temp;
series.XValues = temp.get_Offset(1, 0);
currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
range = range.get_Offset(3, 0);
}
public void excelWrite()
{
//if (File.Exists(_filePath))
//{
// File.Delete(_filePath);
// FileStream fs = File.Create(_filePath);
// fs.Close();
//}
int i=0;
ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
foreach (string n in _tNames)
{
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 1];
RangeExcel.Value = _tNames[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 2];
RangeExcel.Value = _tTels[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 3];
RangeExcel.Value = _tTels2[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 4];
RangeExcel.Value = _tTels3[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 5];
RangeExcel.Value = _tEmail[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 6];
RangeExcel.Value = _tDiv[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 7];
RangeExcel.Value = _tPos[i];
i++;
}
WorkBookExcel.Close(true, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
}
static void Main(string[] args)
{
var ExcelPath = @"C:\Users\SUJITH KALARI\Documents\Visual Studio 2015\Projects\ExcelJson\ExcelJson\bin\Debug\Bookks.xlsx";
var sheetName = "";
var destinationPath = @"C:\Users\SUJITH KALARI\Documents\Visual Studio 2015\Projects\ExcelJson\ExcelJson\bin\Debug\E2J.json";
var json = "";
Dictionary <string, int> sheet = new Dictionary <string, int>();
sheet.Add("Text", 1);
sheet.Add("Image", 2);
sheet.Add("Audio", 3);
sheet.Add("Video", 4);
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Range range = null;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(ExcelPath);
var connectionString = String.Format(@"
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};
Extended Properties=""Excel 12.0 Xml;HDR=YES""", ExcelPath);
File.AppendAllText(destinationPath, "{ AssetList :[");
foreach (System.Collections.Generic.KeyValuePair <string, int> sheetInfo in sheet)
{
sheetName = sheetInfo.Key;
int sheetNo = sheetInfo.Value;
File.AppendAllText(destinationPath, sheetName);
//Creating and opening a data connection to the Excel sheet
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = String.Format(
@"SELECT * FROM [{0}$]",
sheetName
);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNo);
range = xlWorkSheet.UsedRange;
int rowRange = range.Rows.Count;
int columnRange = range.Columns.Count;
int columnCount = 0;
Console.WriteLine(columnRange);
Console.WriteLine(rowRange);
using (var rdr = cmd.ExecuteReader())
{
var query =
(from DbDataRecord row in rdr
select row).Select(x =>
{
//dynamic item = new ExpandoObject();
Dictionary <string, object> item = new Dictionary <string, object>();
for (columnCount = 0; columnCount < columnRange; columnCount++)
{
item.Add(rdr.GetName(columnCount), x[columnCount]);
}
return(item);
});
//Generates JSON from the LINQ query
json = JsonConvert.SerializeObject(query);
//Write the file to the destination path
File.AppendAllText(destinationPath, json);
}
}
File.AppendAllText(destinationPath, "]");
}
File.AppendAllText(destinationPath, "}");
}
void outDataFimlsExcel()
{
//TH có dữ liệu để ghi
if (dtgvRevenue.Rows.Count > 0)
{
//Khai báo và khởi tạo các đối tượng
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Định dạng chung
Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1];
tenCuaHang.Font.Size = 14;
tenCuaHang.Font.Bold = true;
tenCuaHang.Font.Color = Color.Blue;
tenCuaHang.Value = "BẢNG DANH SÁCH DOANH THU PHIM";
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1];
dcCuaHang.Font.Size = 13;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Blue;
dcCuaHang.Value = "Copyright: Nguyễn Văn Cung";
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1];
dtCuaHang.Font.Size = 13;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Blue;
dtCuaHang.Value = "Điện thoại: 0399544543";
Excel.Range header = (Excel.Range)exSheet.Cells[5, 2];
exSheet.get_Range("B5:G5").Merge(true);
header.Font.Size = 13;
header.Font.Bold = true;
header.Font.Color = Color.Red;
header.Value = "DANH SÁCH DOANH THU PHIM";
//Định dạng tiêu đề bảng
exSheet.get_Range("A7:E7").Font.Bold = true;
exSheet.get_Range("A7:E7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A7").Value = "STT";
exSheet.get_Range("B7").Value = "Tên Phim";
exSheet.get_Range("B7").ColumnWidth = 20;
exSheet.get_Range("C7").Value = "Thể loại";
exSheet.get_Range("C7").ColumnWidth = 30;
exSheet.get_Range("D7").Value = "Ngày khởi chiếu";
exSheet.get_Range("D7").ColumnWidth = 20;
exSheet.get_Range("E7").Value = "Ngày kết thúc";
exSheet.get_Range("E7").ColumnWidth = 20;
exSheet.get_Range("F7").Value = "Tổng chi phí";
exSheet.get_Range("F7").ColumnWidth = 20;
exSheet.get_Range("G7").Value = "Đạo diễn";
exSheet.get_Range("G7").ColumnWidth = 20;
exSheet.get_Range("H7").Value = "Tổng chi phí";
exSheet.get_Range("I7").Value = "Doanh thu";
//In dữ liệu
for (int i = 0; i < dtgvRevenue.Rows.Count - 1; i++)
{
exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false;
exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString();
exSheet.get_Range("B" + (i + 8).ToString()).Value =
dtgvRevenue.Rows[i].Cells[0].Value;
exSheet.get_Range("C" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[1].Value;
exSheet.get_Range("D" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[2].Value;
exSheet.get_Range("E" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[3].Value;
exSheet.get_Range("F" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[4].Value;
exSheet.get_Range("G" + (i + 8).ToString()).Value =
dtgvRevenue.Rows[i].Cells[0].Value;
exSheet.get_Range("H" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[5].Value;
exSheet.get_Range("I" + (i + 8).ToString()).Value = dtgvRevenue.Rows[i].Cells[6].Value;
}
exSheet.Name = "Danh sách doanh thu phim";
exBook.Activate();
dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*";
dlgSave.FilterIndex = 1;
dlgSave.AddExtension = true;
dlgSave.DefaultExt = ".xls";
if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exBook.SaveAs(dlgSave.FileName.ToString());
}
exApp.Quit();
}
else
{
MessageBox.Show("Không có danh sách hàng để in");
}
}
/// <summary>
/// Method to add an excel rows
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
static void Main(string[] args)
{
//Open Chrome Driver
IWebDriver driver = new ChromeDriver(@"C:\Users\Anneline\source\repos\");
//Open Excel Spreadsheet to read data
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook xlWorkbook = excelApp.Workbooks.Open(@"C:\DATA\UserLoginData.xlsx");
Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++) //Rowcount
{
for (int j = 1; j <= colCount; j++) //Columncount
{
//Open CaMS Dev Environment
var UrlLink = (Excel.Range)xlWorksheet.Cells[i, j];
string WebLink = UrlLink.Value2.ToString();
driver.Navigate().GoToUrl(WebLink);
driver.Manage().Window.Maximize();
driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(10);
j = j + 1;
//Enter the Username
var UserN = (Excel.Range)xlWorksheet.Cells[i, j];
string Username = UserN.Value2.ToString();
IWebElement UserName = driver.FindElement(By.Id("Input_Email"));
UserName.SendKeys(Username);
j = j + 1;
//Enter the Password
var PasSWord = (Excel.Range)xlWorksheet.Cells[i, j];
string PassW = PasSWord.Value2.ToString();
IWebElement PassWOrd = driver.FindElement(By.Id("Input_Password"));
PassWOrd.SendKeys(PassW);
j = j + 1;
//Click on the Login Button
IWebElement LoginButton1 = driver.FindElement(By.CssSelector("#account > div:nth-child(4) > div > button"));
LoginButton1.Click();
//Click on the Logout Button
Actions action1 = new Actions(driver);
action1.MoveToElement(driver.FindElement(By.XPath("//*[@id='navbarDropdownProfile']/img"))).Build().Perform();
IWebElement manageProfile = driver.FindElement(By.XPath("//*[@id='navbarDropdownProfile']/img"));
manageProfile.Click();
IWebElement LogInoutButton2 = driver.FindElement(By.XPath("/html/body/div/main/nav/div/div[2]/ul/li[4]/div/a[3]"));
LogInoutButton2.Click();
//Click on the Login Button
IWebElement LoginButton3 = driver.FindElement(By.XPath("/html/body/div/div/div/div[1]/div/div[2]/div[2]/div[1]/div[2]/a"));
LoginButton3.Click();
}
}
//Cleanup and Quit Excel Spreadsheet
GC.Collect();
GC.WaitForPendingFinalizers(); //release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet); //close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook); //quit and release
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
//Quit Browser
driver.Quit();
}
void Application_SheetSelectionChange(object Sh, ExcelRaw.Range Target)
{
bbTransformations.clearTransformationsRibbon(this);
}
public string convertFiles(IList <string> filePaths)
{
m_filePaths = filePaths;
m_fileCount = m_filePaths.Count;
int fileIndex = 0;
ArrayList sheet1ValueList = new ArrayList();
ArrayList sheet2ValueList = new ArrayList();
Cross_TopTag topTag;
Cross_Data[] data;
Cross_Paragraphs[][] paragraphs;
Cross_Qas[][][] qas;
Cross_Answers[][][][] answers;
ETRI_TopTag EtopTag;
object[,] sheet1ValueArray;
object[,] sheet2ValueArray;
int totalCountQas = 0;
int totalCountParagraphs = 0;
int sheet1TotalRowCount = 0;
int sheet2TotalRowCount = 0;
int exceptionDatacount = 0;
int exceptionDow = 0;
ArrayList splitedFileName = new ArrayList();
foreach (var item in m_filePaths)
{
string[] temp;
m_path = item;
var missing = Type.Missing;
temp = m_path.Split('_');
splitedFileName.Add(temp);
objApp = new Excel.Application();
objWorkbooks = objApp.Workbooks;
int countParagraphs = 0;
int countQas = 0;
int currentRow = 0;
bool excelOpen = false;
try
{
if (m_currentConvertingMode == convertingMode.CJSONToCExcel)
{
#region JSON -> Excel 변환
// ** name1 영역 파싱
topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path));
// name2 영역 파싱
data = new Cross_Data[topTag.data.Count];
for (int i = 0; i < data.Length; i++)
{
data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString());
}
// ** name3 영역 파싱
paragraphs = new Cross_Paragraphs[data.Length][];
for (int i = 0; i < data.Length; i++)
{
paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count];
for (int j = 0; j < data[i].paragraphs.Count; j++)
{
paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString());
countParagraphs++;
totalCountParagraphs++;
}
}
// ** name4 영역 파싱
qas = new Cross_Qas[data.Length][][];
for (int i = 0; i < data.Length; i++)
{
qas[i] = new Cross_Qas[paragraphs[i].Length][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count];
for (int k = 0; k < paragraphs[i][j].qas.Count; k++)
{
qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString());
countQas++;
totalCountQas++;
}
}
}
// ** name5 영역 파싱
answers = new Cross_Answers[data.Length][][][];
for (int i = 0; i < data.Length; i++)
{
answers[i] = new Cross_Answers[paragraphs[i].Length][][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
answers[i][j] = new Cross_Answers[qas[i][j].Length][];
for (int k = 0; k < qas[i][j].Length; k++)
{
answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count];
for (int m = 0; m < qas[i][j][k].answers.Count; m++)
{
answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString());
}
}
}
}
// ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정
sheet1RowCount = countQas;
sheet2RowCount = countParagraphs;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];
// ** sheet1ValueArray & sheet2ValueArray에 데이터 입력
// * paragraph 순번 & name1 영역
for (int row = 0; row < sheet2RowCount; row++)
{
sheet2ValueArray[row, 0] = sheet2TotalRowCount + 1;
sheet2ValueArray[row, 1] = topTag.version;
sheet2ValueArray[row, 2] = topTag.creator;
sheet2ValueArray[row, 3] = topTag.progress;
sheet2ValueArray[row, 4] = topTag.formatt;
sheet2ValueArray[row, 5] = topTag.time;
sheet2ValueArray[row, 6] = topTag.check;
sheet2ValueArray[row, 7] = topTag.firstfile;
sheet2ValueArray[row, 8] = topTag.secondfile;
sheet2TotalRowCount++;
}
// * name2 & name3 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
sheet2ValueArray[currentRow, 9] = data[d].title;
sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context;
sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged;
currentRow++;
}
}
// * name4 영역
currentRow = 0;
int currentParaNum = 1;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
sheet1ValueArray[currentRow, 0] = sheet1TotalRowCount + 1;
sheet1ValueArray[currentRow, 1] = qas[d][p][q].id;
sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1;
sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1;
sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1;
sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1;
sheet1ValueArray[currentRow, 6] = qas[d][p][q].question;
sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en;
sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1;
sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1;
sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1;
sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1;
sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1;
sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2;
sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2;
sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2;
sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2;
sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; //
sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; //
sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; //
sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; //
sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2;
sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3;
sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3;
sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3;
sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3;
sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3;
sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3;
sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3;
sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3;
sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3;
sheet1ValueArray[currentRow, 37] = currentParaNum;
currentRow++; sheet1TotalRowCount++;
}
currentParaNum++;
}
}
// * name5 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
if (qas[d][p][q].answers.Count > 3)
{
return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + m_path);
}
int answerStartColNum = 31;
for (int a = 0; a < answers[d][p][q].Length; a++)
{
sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text;
sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en;
sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged;
sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn;
sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start;
sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end;
answerStartColNum += 6;
}
currentRow++;
}
}
}
if ((++fileIndex) < m_fileCount)
{
sheet1ValueList.Add(sheet1ValueArray);
sheet2ValueList.Add(sheet2ValueArray);
continue;
}
//마지막 파일 ADD
sheet1ValueList.Add(sheet1ValueArray);
sheet2ValueList.Add(sheet2ValueArray);
// 여러 sheetValueArray들을 각 작업량의 따라 나눠 하나로 통합
string[] separator = { "(", ")", "-", " " }; //제외할 요소들
int totalRowCount_sheet1 = 0;
int totalRowCount_sheet2 = 0;
for (int i = 0; i < fileIndex; i++)
{
string[] _temp = (string[])splitedFileName[i];
string[] splited = _temp[2].Split(separator, StringSplitOptions.RemoveEmptyEntries);
//sheet1 작업
int startIndex = Convert.ToInt32(splited[0]);
int endIndex = Convert.ToInt32(splited[1]);
int length = endIndex - startIndex + 1;
totalRowCount_sheet1 += length;
int rowIndex_sheet1 = 0;
int rowIndex_sheet2 = 0;
object[,] temp_arrList = (object[, ])sheet1ValueList[i];
object[,] tempSheet1Value = new object[length, sheet1ColCount];
for (int j = startIndex - 1; j < endIndex; j++)
{
for (int k = 0; k < sheet1ColCount; k++)
{
tempSheet1Value[rowIndex_sheet1, k] = temp_arrList[j, k];
}
rowIndex_sheet1++;
}
//sheet2 작업
startIndex = (int)tempSheet1Value[0, 37];
endIndex = (int)tempSheet1Value[rowIndex_sheet1 - 1, 37];
length = endIndex - startIndex + 1;
totalRowCount_sheet2 += length;
temp_arrList = (object[, ])sheet2ValueList[i];
object[,] tempSheet2Value = new object[length, sheet2ColCount];
for (int j = startIndex - 1; j < endIndex; j++)
{
for (int k = 0; k < sheet2ColCount; k++)
{
tempSheet2Value[rowIndex_sheet2, k] = temp_arrList[j, k];
}
rowIndex_sheet2++;
}
sheet1ValueList.RemoveAt(i);
sheet2ValueList.RemoveAt(i);
sheet1ValueList.Insert(i, tempSheet1Value);
sheet2ValueList.Insert(i, tempSheet2Value);
}
sheet1RowCount = totalRowCount_sheet1;
sheet2RowCount = totalRowCount_sheet2;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];
int sheet1RowIndex = 0;
int sheet2RowIndex = 0;
int _sheet1RowCount;
int _sheet2RowCount;
for (int i = 0; i < sheet1ValueList.Count; i++)
{
object[,] tempSheet1Value = (object[, ])sheet1ValueList[i];
object[,] tempSheet2Value = (object[, ])sheet2ValueList[i];
_sheet1RowCount = (int)(tempSheet1Value.Length / sheet1ColCount);
for (int j = 0; j < _sheet1RowCount; j++)
{
for (int k = 0; k < sheet1ColCount; k++)
{
sheet1ValueArray[sheet1RowIndex, k] = tempSheet1Value[j, k];
}
sheet1RowIndex++;
}
_sheet2RowCount = (int)(tempSheet2Value.Length / sheet2ColCount);
for (int j = 0; j < _sheet2RowCount; j++)
{
for (int k = 0; k < sheet2ColCount; k++)
{
sheet2ValueArray[sheet2RowIndex, k] = tempSheet2Value[j, k];
}
sheet2RowIndex++;
}
}
//엑셀파일에 writting
excelOpen = true;
objWorkbook = objWorkbooks.Add(missing);
objWorksheets = objWorkbook.Worksheets;
// * sheet2 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1);
objWorksheet.Name = "Paragraphs";
range = objWorksheet.get_Range("A1", "M1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet2ColHeader;
Marshal.ReleaseComObject(range);
Excel.Range c1 = objWorksheet.Cells[2, 1];
Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet2ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
// * sheet1 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
objWorksheet.Name = "Qas";
range = objWorksheet.get_Range("A1", "AL1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet1ColHeader;
Marshal.ReleaseComObject(range);
c1 = objWorksheet.Cells[2, 1];
c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet1ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
m_savePath = Path.ChangeExtension(m_path, "xlsx");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists)
{
fi.Delete();
}
objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
#endregion
}
else
{
#region Excel -> JSON 변환
// ** Excel 파일 불러와서 object 이중배열에 데이터 입력
excelOpen = true;
objWorkbook = objWorkbooks.Open(m_path);
objWorksheets = objWorkbook.Worksheets;
objWorksheet = (Excel.Worksheet)objWorksheets[1];
range = objWorksheet.UsedRange;
sheet1ValueArray = (object[, ])range.get_Value(missing);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
objWorksheet = (Excel.Worksheet)objWorksheets[2];
range = objWorksheet.UsedRange;
sheet2ValueArray = (object[, ])range.get_Value(missing);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
// ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력
// * topTag 객체 데이터 입력
EtopTag = new ETRI_TopTag();
EtopTag.version = sheet2ValueArray[2, 2] == null ? "" : sheet2ValueArray[2, 2].ToString();
EtopTag.creator = sheet2ValueArray[2, 3] == null ? "" : sheet2ValueArray[2, 3].ToString();
EtopTag.data = new List <object>();
// * topTag 객체 내의 Data 객체 리스트 입력
IList <object> titleList = new List <object>();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
object tempTitle = sheet2ValueArray[r, 10];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
if (!titleList.Contains(tempTitle))
{
titleList.Clear();
titleList.Add(tempTitle);
}
ETRI_Data tempData = new ETRI_Data();
tempData.title = tempTitle == null ? "" : tempTitle.ToString();
tempData.paragraphs = new List <object>();
EtopTag.data.Add(tempData);
}
// * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
int dataCount = 0;
object currentTitle = sheet2ValueArray[2, 10];
List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs();
tempParagraphs.context = sheet2ValueArray[r, 11] == null ? "" : sheet2ValueArray[r, 11].ToString();
tempParagraphs.context_en = sheet2ValueArray[r, 12] == null ? "" : sheet2ValueArray[r, 12].ToString();
tempParagraphs.context_tagged = sheet2ValueArray[r, 13] == null ? "" : sheet2ValueArray[r, 13].ToString();
tempParagraphs.qas = new List <object>();
if (sheet2ValueArray[r, 10] == null || sheet2ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 10] == null ? "" : sheet2ValueArray[r, 7].ToString();
}
else if (sheet2ValueArray[r, 10].ToString().Equals(currentTitle))
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++; exceptionDatacount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 10].ToString();
}
exceptionDow++;
}
EtopTag.data = tempDataList.Cast <object>().ToList();
// * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
dataCount = 0;
int paragraphCount = 0;
int currentParagraph = 1;
tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
List <ETRI_Qas> tempQasList = new List <ETRI_Qas>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
ETRI_Qas tempQas = new ETRI_Qas();
tempQas.id = sheet1ValueArray[r, 2] == null ? "" : sheet1ValueArray[r, 2].ToString();
tempQas.question = sheet1ValueArray[r, 7] == null ? "" : sheet1ValueArray[r, 7].ToString();
tempQas.question_en = sheet1ValueArray[r, 8] == null ? "" : sheet1ValueArray[r, 8].ToString();
tempQas.question_tagged = sheet1ValueArray[r, 27] == null ? "" : sheet1ValueArray[r, 27].ToString();
tempQas.questionType = sheet1ValueArray[r, 28] == null ? "" : sheet1ValueArray[r, 28].ToString();
tempQas.questionFocus = sheet1ValueArray[r, 29] == null ? "" : sheet1ValueArray[r, 29].ToString();
tempQas.questionSAT = sheet1ValueArray[r, 30] == null ? "" : sheet1ValueArray[r, 30].ToString();
tempQas.questionLAT = sheet1ValueArray[r, 31] == null ? "" : sheet1ValueArray[r, 31].ToString();
int ansStartColNum = 32;
ETRI_Answers tempAnswers = new ETRI_Answers();
tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? "" : sheet1ValueArray[r, ansStartColNum].ToString();
tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? "" : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? "" : sheet1ValueArray[r, ansStartColNum + 2].ToString();
tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? "" : sheet1ValueArray[r, ansStartColNum + 3].ToString();
tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);
List <ETRI_Answers> tempAnswersList = new List <ETRI_Answers>();
tempAnswersList.Add(tempAnswers);
tempQas.answers = tempAnswersList.Cast <object>().ToList();
tempQasList.Add(tempQas);
currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 38]); //36
if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
{
if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 38])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
{
EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
tempQasList = new List <ETRI_Qas>();
if (paragraphCount < EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
{
paragraphCount++;
}
else // 도달하고 난 후에는 이렇게 처리
{
dataCount++;
paragraphCount = 0;
}
}
}
if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때
{
EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList();
}
}
// ** JSON 파일로 저장
m_savePath = Path.ChangeExtension(m_path, "json");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists) // 파일이 이미 존재하면 삭제
{
fi.Delete();
}
string saveJSONText;
bool m_EtoJNullRemoveCheck = false;
if (m_EtoJNullRemoveCheck)
{
saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거
}
);
}
else
{
saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Include // Null값 객체 포함
}
);
}
using (StreamWriter sw = new StreamWriter(m_savePath))
{
sw.Write(saveJSONText);
}
#endregion
}
}
catch (Exception e)
{
if (excelOpen)
{
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
}
return("-------------------------------------------------------\n예외처리 된 오류 발생.\r\n파일: " + m_path +
"-------------------------------------------------------\n데이터카운트: " + exceptionDatacount + "\n열 번호: " + exceptionDow +
"-------------------------------------------------------\n오류 이유:" + e.ToString());
}
}
return("모든 파일 변환 성공");
}
public void CreateReport(DataTable data, string saveToPath)
{
if (data == null)
{
messager.ErrorMessage("Ошибка получения отчетной таблицы! Повторите попытку или обратитесь к разработчику.");
return;
}
if (string.IsNullOrEmpty(saveToPath) || string.IsNullOrWhiteSpace(saveToPath))
{
messager.ErrorMessage(string.Format("Ошибка в указанном пути файла:{1}\"{0}\",{1}укажите правильный путь и имя файла!",
saveToPath,
Environment.NewLine
));
return;
}
if (!Directory.Exists(Path.GetDirectoryName(saveToPath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(saveToPath));
}
try
{
Excel.Application eApp = new Excel.Application();
Excel.Workbook wBook = eApp.Workbooks.Add();
Excel.Worksheet wSheet = wBook.Worksheets.Add();
for (int i = 0; i < data.Columns.Count; i++)
{
wSheet.Cells[1, i + 1] = data.Columns[i].ColumnName;
}
for (int i = 0; i < data.Rows.Count; i++)
{
for (int j = 0; j < data.Columns.Count; j++)
{
wSheet.Cells[i + 2, j + 1] = data.Rows[i][j];
}
}
//finding a last cell
Excel.Range r1 = wSheet.Cells.get_End(Excel.XlDirection.xlToRight);
Excel.Range r2 = wSheet.Cells.get_End(Excel.XlDirection.xlDown);
string rightEnd = r1.get_Address().Split('$')[1]; //letter
string bottomEnd = r2.get_Address().Split('$')[2]; //number
//last cell address
string lastCell = rightEnd + bottomEnd;
//painting grid
List <Excel.XlBordersIndex> borders = new List <Excel.XlBordersIndex>()
{
Excel.XlBordersIndex.xlEdgeRight,
Excel.XlBordersIndex.xlEdgeLeft,
Excel.XlBordersIndex.xlEdgeBottom,
Excel.XlBordersIndex.xlEdgeTop,
Excel.XlBordersIndex.xlInsideHorizontal,
Excel.XlBordersIndex.xlInsideVertical
};
foreach (Excel.XlBordersIndex brd in borders)
{
Excel.Range r = wSheet.get_Range("A1", lastCell);
r.Borders[brd].Weight = Excel.XlBorderWeight.xlThin;
r.Borders[brd].LineStyle = Excel.XlLineStyle.xlContinuous;
r.Borders[brd].ColorIndex = 0;
}
wSheet.get_Range("A1", lastCell).Font.Size = 14;
wSheet.Columns.AutoFit();
wBook.SaveAs(saveToPath);
eApp.Visible = true;
}
catch
{
messager.ErrorMessage(string.Format("Ошибка создания документа Microsoft Office Excel - \"{0}\"{1}{1}Обратитесь к разработчику!",
Path.GetFileName(saveToPath),
Environment.NewLine
));
}
}
// Вставка формул
#region Формулы
private static void sumTableExcel(Excel.Worksheet ws, int numberFile, int firstRow, int lastRow)
{
switch (numberFile)
{
case 1:
// Диапазоны для вставки формул
Excel.Range rngCol1Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы столбца "Покупки включая НДС"
Excel.Range rngCol2Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 9], ws.Cells[lastRow, 9]]; // Формула для подсчета суммы стобца "Покупки без НДС"
Excel.Range rngCol3Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 10], ws.Cells[lastRow, 10]]; // Формула для подсчета суммы столбца "Сумма НДС"
// Указываем диапазон для формул
rngCol1Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})";
rngCol2Sum.Formula = $"=SUM(r{firstRow}c{9}:r{lastRow - 1}c{9})";
rngCol3Sum.Formula = $"=SUM(r{firstRow}c{10}:r{lastRow - 1}c{10})";
break;
case 2:
// Диапазоны для вставки формул
Excel.Range rngCol4Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 7], ws.Cells[lastRow, 7]]; // Формула для подсчета суммы столбца "Покупки включая НДС"
Excel.Range rngCol5Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы стобца "Покупки без НДС"
Excel.Range rngCol6Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 9], ws.Cells[lastRow, 9]]; // Формула для подсчета суммы столбца "Сумма НДС"
// Указываем диапазон для формул
rngCol4Sum.Formula = $"=SUM(r{firstRow}c{7}:r{lastRow - 1}c{7})";
rngCol5Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})";
rngCol6Sum.Formula = $"=SUM(r{firstRow}c{9}:r{lastRow - 1}c{9})";
break;
case 3:
// Диапазоны для вставки формул
Excel.Range rngCol7Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 3], ws.Cells[lastRow, 3]]; // Формула для подсчета суммы столбца "Выручка, руб."
Excel.Range rngCol8Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы стобца "Валовая прибыль, руб."
// Указываем диапазон для формул
rngCol7Sum.Formula = $"=SUM(r{firstRow}c{3}:r{lastRow - 1}c{3})";
rngCol8Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})";
break;
case 4:
// Диапазоны для вставки формул
Excel.Range rngCol9Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 3], ws.Cells[lastRow, 3]]; // Формула для подсчета суммы столбца "Выручка, руб."
Excel.Range rngCol10Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы стобца "Премиальная часть, руб."
// Указываем диапазон для формул
rngCol9Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})";
rngCol10Sum.Formula = $"=SUM(r{firstRow}c{5}:r{lastRow - 1}c{5})";
break;
case 5:
// Диапазоны для вставки формул
Excel.Range rngCol11Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма без НДС, руб."
Excel.Range rngCol12Sum = (Excel.Range)ws.Range[ws.Cells[lastRow + 1, 6], ws.Cells[lastRow + 1, 6]]; // Формула для подсчета произведения стобца "Сумма без НДС, руб." строки "НДС 18%"
Excel.Range rngCol13Sum = (Excel.Range)ws.Range[ws.Cells[lastRow + 2, 6], ws.Cells[lastRow + 2, 6]]; // Формула для подсчета суммы столца "Сумма без НДС, руб." строки "НДС 18%" + "ИТОГО"
// Указываем диапазон для формул
rngCol11Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 1}c{6})";
rngCol12Sum.Formula = $"=r{lastRow}c{6}*0.20";
rngCol13Sum.Formula = $"=r{lastRow + 1}c{6}+r{lastRow}c{6}";
break;
case 6:
// Диапазоны для вставки формул
Excel.Range rngCol14Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма, руб."
// Указываем диапазон для формул
rngCol14Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 1}c{6})";
break;
case 7:
// Диапазоны для вставки формул
Excel.Range rngCol15Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 33], ws.Cells[lastRow, 33]]; // Формула для подсчета суммы столбца "Мест, штук"
Excel.Range rngCol16Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 38], ws.Cells[lastRow, 38]]; // Формула для подсчета суммы стобца "Количество"
Excel.Range rngCol17Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 44], ws.Cells[lastRow, 44]]; // Формула для подсчета суммы столца "Сумма без учета НДС, руб. коп."
Excel.Range rngCol18Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 50], ws.Cells[lastRow, 50]]; // Формула для подсчета суммы столбца "НДС - сумма, руб. коп."
Excel.Range rngCol19Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 54], ws.Cells[lastRow, 54]]; // Формула для подсчета суммы столбца "Сумма с учетом НДС, руб. коп."
// Указываем диапазон для формул
rngCol15Sum.Formula = $"=SUM(r{firstRow}c{33}:r{lastRow - 1}c{33})";
rngCol16Sum.Formula = $"=SUM(r{firstRow}c{38}:r{lastRow - 1}c{38})";
rngCol17Sum.Formula = $"=SUM(r{firstRow}c{44}:r{lastRow - 1}c{44})";
rngCol18Sum.Formula = $"=SUM(r{firstRow}c{50}:r{lastRow - 1}c{50})";
rngCol19Sum.Formula = $"=SUM(r{firstRow}c{54}:r{lastRow - 1}c{54})";
break;
case 8:
// Диапазоны для вставки формул
Excel.Range rngCol20Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 6], ws.Cells[lastRow, 6]]; // Формула для подсчета суммы столбца "Сумма, руб. коп."
Excel.Range rngCol20_1Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 5], ws.Cells[lastRow, 5]]; // Формула для подсчета суммы столбца "Штук"
Excel.Range rngCol20_2Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 4], ws.Cells[lastRow, 4]]; // Формула для подсчета суммы столбца "Цена"
// Указываем диапазон для формул
rngCol20Sum.Formula = $"=SUM(r{firstRow}c{6}:r{lastRow - 2}c{6})";
rngCol20_1Sum.Formula = $"=SUM(r{firstRow}c{5}:r{lastRow - 2}c{5})";
rngCol20_2Sum.Formula = $"=SUM(r{firstRow}c{4}:r{lastRow - 1}c{4})";
break;
case 9:
// Диапазоны для вставки формул
Excel.Range rngCol21Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 7], ws.Cells[lastRow, 7]]; // Формула для подсчета суммы столбца "Мест, штук"
Excel.Range rngCol22Sum = (Excel.Range)ws.Range[ws.Cells[lastRow, 8], ws.Cells[lastRow, 8]]; // Формула для подсчета суммы стобца "Количество"
// Указываем диапазон для формул
rngCol21Sum.Formula = $"=SUM(r{firstRow}c{7}:r{lastRow - 1}c{7})";
rngCol22Sum.Formula = $"=SUM(r{firstRow}c{8}:r{lastRow - 1}c{8})";
break;
}
}
// Печатает таблицу без вставки текста
public static void ExportToExcelNew(Form form, DataGridView dgv, int fRow, int fColumn, string nameTable)
{
string baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
string path = baseDirectory + "doc//Прайс-лист.xlsx";
int firstColumn = fColumn; // Первый столбец
int firstRow = fRow; // Первая строка
int lastRow = firstRow + dgv.RowCount; // Последняя строка
int lastColumn = firstColumn + dgv.ColumnCount; // Последний столбец
Excel.Application xl = new Excel.Application(); // Создаем экземпляр Excel'а
try
{
Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Open(path, false, false); // Открываем файл Excel
Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet; // Указываем ссылку на активный лист файла Excel
form.Cursor = Cursors.WaitCursor;
// Очистка старого форматирования
Excel.Range rngDEL = (Excel.Range)ws.Range[ws.Cells[1, 1], ws.Cells[7, dgv.ColumnCount + 4]];
rngDEL.Clear();
// Заполняем массив заголовками
object[] headers = new object[dgv.ColumnCount];
for (int i = 0; i < dgv.ColumnCount; i++)
{
headers[i] = dgv.Columns[i].HeaderText;
}
// Заполняем массив данными
object[,] rc = new object[dgv.RowCount, dgv.ColumnCount];
for (int i = 0; i < dgv.RowCount; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
rc[i, j] = dgv.Rows[i].Cells[j].Value;
}
}
// Вставка текста в ячейки шаблона
ws.Cells[1, 1] = DateTime.Now.ToShortDateString();
ws.Cells[3, 1] = nameTable;
// Форматирование
Excel.Range rngName = ws.Range[ws.Cells[3, 1], ws.Cells[3, 1]];
rngName.Font.Size = 16;
rngName.Font.Bold = true;
// Добавляем пустые строки со сдвигом вниз
Excel.Range insertColumn = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 2, lastColumn - 1]];
insertColumn.Insert(Excel.XlDirection.xlDown);
// Вставка рамок
Excel.Range pasteBorders = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]];
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
pasteBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
// Вставляем заголовки из массива в Excel
Excel.Range pasteCaptions = ws.Range[ws.Cells[firstRow - 1, firstColumn], ws.Cells[firstRow - 1, lastColumn]];
pasteCaptions.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, headers);
pasteCaptions.Font.Bold = true;
// Вставляем текст из массива в Excel
Excel.Range pasteText = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 1, lastColumn - 1]];
pasteText.Font.Bold = false;
pasteText.Font.Name = "Arial";
pasteText.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, rc);
// Удаляем последнюю пустую строку со сдвигом вверх
Excel.Range deleteRow = ws.Range[ws.Cells[lastRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]];
deleteRow.Delete(Excel.XlDirection.xlUp);
// Удаляем последний столбец
Excel.Range deleteColumn = ws.Range[ws.Cells[firstRow - 1, lastColumn], ws.Cells[lastRow, lastColumn]];
deleteColumn.Clear();
// Автоподбор ширины столбцов
Excel.Range rngAF = ws.Range[ws.Cells[firstRow - 1, 1], ws.Cells[lastRow, lastColumn]];
rngAF.Columns.AutoFit();
rngAF.Rows.AutoFit();
form.Cursor = Cursors.Default;
xl.Visible = true; // Делаем книгу Excel видимой
xl.UserControl = true; // Делаем книгу Excel активной
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n" + ex, "Ошибка");
}
}
/// <summary>
/// 有条件导出
/// </summary>
/// <param name="details"></param>
/// <param name="type"></param>
private void ExportExcel(IList <Model.ProduceInDepotDetail> details, string type)
{
try
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 17]);
r.MergeCells = true;//合并单元格
//Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter = -4108;
//Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium= -4138;
//Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic= -4105;
switch (type)
{
case "防霧":
excel.Cells[1, 1] = "強化/防霧 工作日報表";
excel.Cells[3 + details.Count, 17] = "QR8-06-05-1";
break;
case "品檢":
excel.Cells[1, 1] = "品檢日報表";
excel.Cells[3 + details.Count, 17] = "QR8-03-01-1";
break;
case "組A(半)":
excel.Cells[1, 1] = "組裝半成品日報表";
excel.Cells[3 + details.Count, 17] = "QR8-03-02-1";
break;
case "組A":
excel.Cells[1, 1] = "組裝成品日報表";
break;
}
#region Set Header
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20;
excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 17]).HorizontalAlignment = -4108;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 17]).ColumnWidth = 12;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 2]).ColumnWidth = 20;
excel.get_Range(excel.Cells[2, 3], excel.Cells[2, 3]).ColumnWidth = 40;
excel.get_Range(excel.Cells[2, 11], excel.Cells[2, 12]).ColumnWidth = 20;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 17]).Interior.Color = 12566463;
excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 17]).RowHeight = 20;
excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 17]).Font.Size = 13;
excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 17]).WrapText = true;
excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 17]).EntireRow.AutoFit();
excel.Cells[2, 1] = "入庫日期";
excel.Cells[2, 2] = "入庫單號";
excel.Cells[2, 3] = "產品名稱";
excel.Cells[2, 4] = "型號";
excel.Cells[2, 5] = "公司部門";
excel.Cells[2, 6] = "單位";
excel.Cells[2, 7] = "生產數量";
excel.Cells[2, 8] = "合計生產";
excel.Cells[2, 9] = "合計合格";
excel.Cells[2, 10] = "合計入庫";
excel.Cells[2, 11] = "合計轉生產";
excel.Cells[2, 12] = "加工單";
excel.Cells[2, 13] = "客戶訂單號";
excel.Cells[2, 14] = "生產數量";
excel.Cells[2, 15] = "合格數量";
excel.Cells[2, 16] = "轉生產數量";
excel.Cells[2, 17] = "入庫數量";
#endregion
#region 取商品對應的母件型號
foreach (var item in details)
{
if (string.IsNullOrEmpty(item.CustomerProductName))
{
//item.CustomerProductName = (this.manager as BL.ProduceInDepotDetailManager).SelectCustomerProductNameByPronoteHeaderId(item.PronoteHeaderId); 如果一个订单里面多个商品同时用到某个子件，在物料需求里面该子件会合并计算为一笔，其实它对应有多个主件
item.CustomerProductName = new Help().GetCustomerProductNameByPronoteHeaderId(item.PronoteHeaderId, item.ProductId);
}
}
#endregion
for (int i = 0; i < details.Count; i++)
{
excel.Cells[i + 3, 1] = details[i].mProduceInDepotDate.HasValue ? details[i].mProduceInDepotDate.Value.ToString("yyyy-MM-dd") : "";
excel.Cells[i + 3, 2] = details[i].ProduceInDepotId;
excel.Cells[i + 3, 3] = details[i].ProductName;
excel.Cells[i + 3, 4] = details[i].CustomerProductName;
excel.Cells[i + 3, 5] = details[i].WorkHousename;
excel.Cells[i + 3, 6] = details[i].ProductUnit;
excel.Cells[i + 3, 7] = details[i].PronoteHeaderSum;
excel.Cells[i + 3, 8] = details[i].HeJiProceduresSum;
excel.Cells[i + 3, 9] = details[i].HeJiCheckOutSum;
excel.Cells[i + 3, 10] = details[i].HeJiProduceQuantity;
excel.Cells[i + 3, 11] = details[i].HeJiProduceTransferQuantity;
excel.Cells[i + 3, 12] = details[i].PronoteHeaderId;
excel.Cells[i + 3, 13] = details[i].CusXOId;
excel.Cells[i + 3, 14] = details[i].ProceduresSum;
excel.Cells[i + 3, 15] = details[i].CheckOutSum;
excel.Cells[i + 3, 16] = details[i].ProduceTransferQuantity;
excel.Cells[i + 3, 17] = details[i].ProduceQuantity;
}
excel.Visible = true;//是否打开该Excel文件
excel.WindowState = XlWindowState.xlMaximized;
}
catch
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}
public void OpenExcel(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Application excel = new Application();//lauch excel application
if (excel == null)
{
Console.Out.Write("Can't access excel");
}
else
{
excel.Visible = false; excel.UserControl = true;
// 以只讀的形式打開EXCEL文檔
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一個工作薄
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
Excel.Range xlRange = ws.UsedRange;
//Console.Out.WriteLine("Row = " + ws.UsedRange.Rows.Count);
//Console.Out.WriteLine("Col = " + ws.UsedRange.Columns.Count);
teachernum = Convert.ToInt16(xlRange.Cells[1, 1].Value2);
data = new string[teachernum][];
errordata = new string[teachernum][];
subclass = new string[teachernum][];
checkdata = new string[teachernum][];
teachername = new string[teachernum];
for (int i = 0; i < teachernum; i++)
{
data[i] = new string[classnum];
errordata[i] = new string[classnum];
subclass[i] = new string[classnum];
checkdata[i] = new string[classnum];
}
string[][] sa = new string[ws.UsedRange.Rows.Count][];
for (int i = 0; i < ws.UsedRange.Rows.Count; i++)
{
sa[i] = new string[ws.UsedRange.Columns.Count];
}
for (int i = 1; i <= ws.UsedRange.Rows.Count; i++)
{
for (int j = 1; j <= ws.UsedRange.Columns.Count; j++)
{
//new line
//if (j == 1)
// Console.Write("\r\n");
//write the value to the console
// if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
// Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
//add useful things here!
if (i > 1)
{
if (xlRange.Cells[i, j].Value2 == null)
{
sa[i - 2][j - 1] = "";
}
else
{
sa[i - 2][j - 1] = xlRange.Cells[i, j].Value2.ToString();
}
}
}
}
for (int i = 0; i < teachernum; i++)
{
teachername[i] = sa[i][0];
for (int j = 1; j <= classnum; j++)
{
string class_string = sa[i][j];
data[i][j - 1] = class_string;
}
}
for (int i = 0; i < 3; i++)
{
for (int j = 1; j <= classnum; j++)
{
subclass[i][j - 1] = sa[teachernum + i][j];
}
}
}
excel.Quit(); excel = null;
GC.Collect();
}
private void LoadExcel(string path)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path);
Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets[1];
Excel.Range xlRange = xlWorkSheet.UsedRange;
int n = 1;
for (int i = 2; i <= xlRange.Rows.Count; i++)
{
if (xlRange.Cells[i, 1].Value2 != null)
{
string ChucNangKhac = "";
string NVBang = "5NV";
string Moc = "Moc0";
int server = 0;
int qq = 4;
var username = xlRange.Cells[i, 1].Value2.ToString();
var pwd = xlRange.Cells[i, 2].Value2.ToString();
if (xlRange.Cells[i, 3].Value2 != null)
{
server = int.Parse(xlRange.Cells[i, 3].Value2.ToString());
}
if (xlRange.Cells[i, 4].Value2 != null)
{
NVBang = xlRange.Cells[i, 4].Value2.ToString();
}
if (xlRange.Cells[i, 5].Value2 != null)
{
Moc = xlRange.Cells[i, 5].Value2.ToString();
}
if (xlRange.Cells[i, 6].Value2 != null)
{
ChucNangKhac = xlRange.Cells[i, 6].Value2.ToString();
}
if (xlRange.Cells[i, 7].Value2 != null)
{
qq = int.Parse(xlRange.Cells[i, 7].Value2.ToString());
}
listAccounts.Add(new User()
{
Index = n,
Username = username,
Password = pwd,
Server = server,
NVBang = NVBang,
Moc = Moc,
ChucNangKhac = ChucNangKhac,
QQ = qq,
});
n++;
}
}
DataGridUsers.ItemsSource = listAccounts;
ketThuc = listAccounts.Count - 1;
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorkSheet);
xlWorkBook.Close();
Marshal.ReleaseComObject(xlWorkBook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
currentUser = 0;
}
private static void ToExcel(DataGridView ExportGrid, string p_ReportName)
{
//如果网格尚未数据绑定
if (ExportGrid == null)
{
return;
}
// 列索引，行索引
int colIndex = 0;
int rowIndex = 0;
int j;
//总可见列数，总可见行数
//int colCount = ExportGrid.Columns.GetColumnCount(DataGridViewElementStates.Visible);
//int rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);
int rowCount = 0;
int colCount = SelectedColumns.Count;
if (PrintAllRows) //打印全部
{
rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);
}
else //打印选择
{
rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Selected);
}
//如果DataGridView中没有行，返回
if (rowCount == 0)
{
return;
}
//保存对话框
if (!SaveFileDialog())
{
return;
}
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动", "系统信息");
return;
}
// 创建Excel工作薄
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题，实测中发现执行设置字体大小和将字体设置为粗体的语句耗时较长，故注释掉了
string[] sTitle = p_ReportName.Split(new char[1] {
';'
});
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = sTitle[0];
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
// 单据汇总
// 创建缓存数据
object[,] objData = new object[rowCount + 2 + sTitle.Length, colCount];
for (j = 1; j < sTitle.Length; j++)
{
//Microsoft.Office.Interop.Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, colCount]);
//range1.MergeCells = true;
objData[rowIndex, 0] = sTitle[j];
rowIndex++;
}
objData[rowIndex, 0] = "";
rowIndex++;
// 获取列标题，隐藏的列不处理
for (int i = 0; i < ExportGrid.ColumnCount; i++)
{
if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[i].HeaderText))
{
continue;
}
if (ExportGrid.Columns[i].Visible)
{
objData[rowIndex, colIndex++] = ExportGrid.Columns[i].HeaderText;
}
}
// 获取数据，隐藏的列的数据忽略
for (int i = 1; i <= ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); i++)
{
if (!PrintAllRows && !ExportGrid.Rows[i - 1].Selected)
{
continue;
}
rowIndex++;
colIndex = 0;
for (j = 0; j < ExportGrid.ColumnCount; j++)
{
if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[j].HeaderText))
{
continue;
}
if (ExportGrid.Columns[j].Visible)
{
if (ExportGrid[j, i - 1].Value != null)
{
objData[rowIndex, colIndex++] = ExportGrid[j, i - 1].Value.ToString();
}
else
{
objData[rowIndex, colIndex++] = "";
}
}
}
System.Windows.Forms.Application.DoEvents();
}
// 写入Excel
//xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowCount + +2 + sTitle.Length, colCount]);
range.Value2 = objData;
// 保存
try
{
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//xlApp.Visible = true;
xlBook.Saved = true;
xlBook.SaveCopyAs(FullFileName);
MessageBox.Show("导出成功！", "系统信息");
}
catch
{
MessageBox.Show("保存出错，请检查文件是否被正使用！", "系统信息");
//return false;
}
finally
{
xlApp.Quit();
GC.Collect();
//KillProcess("excel");
}
//return true;
}
/// <summary>
/// Method to make columns auto fit according to data
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
private void WorksheetProjects_Change(Excel.Range Target)
{
Debug.WriteLine("Projects Worksheet changed");
}
/// <summary>
/// Given an IP address and a network specification, returns a boolean indicating whether the single
/// IP address exists within the network.
/// </summary>
/// <param name="Address1">First IP address to test</param>
/// <param name="Address2">Second IP address to test (can be a network block in VLSM slash-style notation)</param>
/// <param name="Netmask">Netmask (required if the network block is not in VLSM format)</param>
/// <returns></returns>
public object IsInSameNetwork4(string Address1, string Address2, [Optional] object Netmask)
{
IPAddress _address1 = null;
if (!IPAddress.TryParse(Address1, out _address1))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
IPAddress _address2 = null;
IPAddress _netmask = null;
if (Address2.IndexOf('/') > 0)
{
string[] _networkArg = Address2.Split('/');
if (!IPAddress.TryParse(_networkArg[0], out _address2))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
try
{
_netmask = _ConvertBitlenToMask4(Convert.ToInt32(_networkArg[1]));
}
catch (ArgumentException)
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
}
else
{
if (!IPAddress.TryParse(Address2, out _address2))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
}
if (!(Netmask is System.Reflection.Missing))
{
string _netmaskStr = "";
if (Netmask is string)
{
_netmaskStr = (string)Netmask;
}
if (Netmask is Excel.Range)
{
Excel.Range _netmaskRng = (Excel.Range)Netmask;
_netmaskStr = (string)_netmaskRng.Value;
}
if (!IPAddress.TryParse((string)_netmaskStr, out _netmask))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
}
if (_netmask == null)
{
return(new ErrorWrapper((int)CVErrNum.ErrNull));
}
IPAddress _addr1Masked = _PerformBitmask(_address1, _netmask);
IPAddress _addr2Masked = _PerformBitmask(_address2, _netmask);
if (_addr1Masked.Equals(_addr2Masked))
{
return(true);
}
return(false);
}
private void button1_Click(object sender, EventArgs e)
{
button1.Enabled = false;
numericUpDown1.Enabled = false;
numericUpDown2.Enabled = false;
checkBox2.Enabled = false;
checkBox1.Enabled = false;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
///
/// Копирую таблицы со структурой
/// Правила - двумерная структура
/// Сетевые объекты - трёхмерная (двумерная таблица с объектом-списком)
/// Сервисы - двумерная таблица
///
Rulesd_Delegate d = null;
d = new Rulesd_Delegate(Rulesd);
IAsyncResult R = null;
R = d.BeginInvoke(openFileDialog1.FileName, null, null);
Rules = d.EndInvoke(R);
/*
* Это N правил + 1 отрезаем кончающие правила
* Далее начальные
*/
if (checkBox2.Checked &&
numericUpDown1.Value < numericUpDown2.Value &&
numericUpDown2.Value < Rules.Count)
{
Rules.RemoveRange(Convert.ToInt32(numericUpDown2.Value) + 1, Rules.Count - Convert.ToInt32(numericUpDown2.Value) - 1);
Rules.RemoveRange(0, Convert.ToInt32(numericUpDown1.Value));
}
else
if (checkBox2.Checked)
{
MessageBox.Show("Проверьте правильно ли Вы выставили значения диапазона интересующих правил");
return;
}
AddTextToStatus("Скопировал Таблицу правил");
NetworkObjd_Delegate ND = null;
ND = new NetworkObjd_Delegate(NetworkObjd);
IAsyncResult NR = null;
NR = ND.BeginInvoke(openFileDialog1.FileName, null, null);
NetworkObj = ND.EndInvoke(NR);
AddTextToStatus("Скопировал Таблицу Сетевых имён");
for (int k = 0; k < NetworkObj.Count; k++)
{
for (int l = 0; l < NetworkObj[l].Count; l++)
{
NetworkObj[k][l] = NetworkObj[k][l].Distinct();
}
}
Servicesd_Delegate SD = null;
SD = new Servicesd_Delegate(Servicesd);
IAsyncResult SR = null;
SR = SD.BeginInvoke(openFileDialog1.FileName, null, null);
Services = SD.EndInvoke(SR);
AddTextToStatus("Скопировал Таблицу сервисов");
///
/// Открываем книгу Excel.
///
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Workbook WB;
Worksheet WS;
WB = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
WS = (Worksheet)WB.Sheets[1];
int i = 1;
AddTextToStatus("Открыл ExCel");
progressBar1.Maximum = Rules.Count;
progressBar1.Value = 0;
try
{
foreach (List <string> row in Rules)
{
if (i == 1)
{
WS.Cells[i, 1].Value = "Сервис";
WS.Cells[i, 2].Value = "Сегмент источника";
WS.Cells[i, 3].Value = "IP Источника";
WS.Cells[i, 4].Value = "Сегмент назначения";
WS.Cells[i, 5].Value = "IP назначения";
WS.Cells[i, 6].Value = "Порты";
WS.Cells[i, 7].Value = "Решение";
WS.Cells[i, 8].Value = "Установлено";
WS.Cells[i, 9].Value = "Время";
WS.Cells[i, 10].Value = "Комментарий";
i++;
continue;
}
string[] split = { "\r\n", "\r", "\n" };
///
/// Это для Source
/// Есть структура данных ResolvedNetObj, которая содержит
/// отрезолвленные члены группового объекта
/// Создадим список из структуры ResolvedNetObj который будет содержать
/// все объекты ячейки источник из политики
///
List <string> CurrentSourceNetObj = row[2].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList();
List <ResolvedNetObj> CurrentSrc = GetResolvedNetObj(CurrentSourceNetObj);
AddTextToStatus("Отрезолвил Объекты Источники для " + i.ToString() + " правила");
foreach (ResolvedNetObj oneNetObj in CurrentSrc)
{
if (oneNetObj.ResolvedObj.Count > 1)
{
foreach (List <string> OneResNetObj in oneNetObj.ResolvedObj)
{
if (ToNodeList.Where(n => n.NetObjName == OneResNetObj[0]).Count() == 0)
{
List <ResolvedNetObj> tmpone = GetResolvedNetObj(new List <string> {
OneResNetObj[0]
});
ToNodeList.AddRange(tmpone);
if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0)
{
ToNodeList.Add(oneNetObj);
}
}
else
{
continue;
}
}
}
else
if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0)
{
ToNodeList.Add(oneNetObj);
}
}
///
/// Это для Destination
///
List <string> CurrentDestNetObj = row[3].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList();
List <ResolvedNetObj> CurrentDst = GetResolvedNetObj(CurrentDestNetObj);
AddTextToStatus("Отрезолвил Объекты Назначения для " + i.ToString() + " правила");
foreach (ResolvedNetObj oneNetObj in CurrentDst)
{
if (oneNetObj.ResolvedObj.Count > 1)
{
foreach (List <string> OneResNetObj in oneNetObj.ResolvedObj)
{
if (ToNodeList.Where(n => n.NetObjName == OneResNetObj[0]).Count() == 0)
{
List <string> one = new List <string> {
OneResNetObj[0]
};
List <ResolvedNetObj> tmpone = GetResolvedNetObj(one);
ToNodeList.AddRange(tmpone);
if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0)
{
ToNodeList.Add(oneNetObj);
}
}
else
{
continue;
}
}
}
else
if (ToNodeList.Where(n => n.NetObjName == oneNetObj.NetObjName).Count() == 0)
{
ToNodeList.Add(oneNetObj);
}
}
///
/// Это для портов
///
List <string> CurrenPortObj = row[5].Split(split, StringSplitOptions.RemoveEmptyEntries).ToList();
List <List <string> > CurrentPortObjResolved = new List <List <string> >();
progressBar2.Maximum = CurrenPortObj.Count;
progressBar2.Value = 0;
foreach (string OnePortObj in CurrenPortObj)
{
string s = OnePortObj.Replace("Not ", "").Trim();
List <List <string> > tmp = ResolvePorts(s);
if (OnePortObj.Contains("Not "))
{
for (int b = 0; b < tmp.Count; b++)
{
tmp[b][0] = "Not " + tmp[b][0];
}
}
CurrentPortObjResolved.AddRange(tmp);
if (progressBar2.InvokeRequired)
{
progressBar2.BeginInvoke(new MethodInvoker(() => progressBar2.Value++));
}
else
{
progressBar2.Value++;
}
System.Windows.Forms.Application.DoEvents();
}
AddTextToStatus("Отрезолвил Объекты порты для " + i.ToString() + " правила");
for (int a = 0; a < row.Count; a++)
{
if (a == 1)
{
WS.Cells[i, 1].Value = row[a];
}
if (a == 2)
{
AddNetworkObj(CurrentSrc, WB, WS, i, 2);
}
if (a == 3)
{
AddNetworkObj(CurrentDst, WB, WS, i, 4);
}
if (a == 5)
{
string ThisCell = "";
foreach (List <string> PortTableRow in CurrentPortObjResolved)
{
ThisCell += PortTableRow[0] + " (" + PortTableRow[1] + "/" + PortTableRow[2] + ")" + Environment.NewLine;
}
WS.Cells[i, 6].Value = ThisCell.Replace("Icmp (Icmp/)", "ICMP")
.Replace("(ALL_DCE_RPC/DCE-RPC)", "(Все DCE-RPC)")
.Replace("Any (Any/)", "Any")
.Replace("l_ALL_DCE_RPC(l_ALL_DCE_RPC / DCE - RPC)", "l_ALL_DCE_RPC (Все DCE-RPC)")
.Trim();
}
if (a == 6)
{
WS.Cells[i, 7].Value = row[a];
}
if (a == 8)
{
WS.Cells[i, 8].Value = row[a].Replace("\r\n", "\n").Replace("\n\r", "\n").Replace("\r\r", "\n").Replace("\n\n", "\n").Trim();
}
if (a == 9)
{
WS.Cells[i, 9].Value = row[a];
}
if (a == 10)
{
WS.Cells[i, 10].Value = row[a].Replace(" ", "").Trim();
}
if (row[0].Contains("Disabled"))
{
Microsoft.Office.Interop.Excel.Range c1 = WS.Cells[i, 1];
Microsoft.Office.Interop.Excel.Range c2 = WS.Cells[i, 10];
Range oRange = (Microsoft.Office.Interop.Excel.Range)WS.get_Range(c1, c2);
oRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
oRange.Font.Bold = true;
}
if (a == 4 || a == 7)
{
continue;
}
}
AddTextToStatus("Записал " + i.ToString() + " правило");
i++;
if (progressBar1.InvokeRequired)
{
progressBar1.BeginInvoke(new MethodInvoker(() => progressBar1.Value++));
}
else
{
progressBar1.Value++;
}
System.Windows.Forms.Application.DoEvents();
}
AddTextToStatus("Добавляю лист Node");
AddNodeList(WB);
if (checkBox3.Checked)
{
AddAllNodeList(WB);
}
if (CurrentErrorObjResolved.Count != 0)
{
PolycyConverter.Form3 form3 = new PolycyConverter.Form3();
form3.ShowDialog(this);
}
else
{
PolycyConverter.Form2 form2 = new PolycyConverter.Form2();
form2.ShowDialog(this);
}
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
ObjExcel.Columns.AutoFit();
ObjExcel.Rows.AutoFit();
PolycyConverter.Form3 form3 = new PolycyConverter.Form3();
form3.ShowDialog(this);
return;
}
finally
{
WS.Activate();
Microsoft.Office.Interop.Excel.Range c1 = WS.Cells[1, 2];
Microsoft.Office.Interop.Excel.Range c2 = WS.Cells[i + 1, 10];
Range oRange = (Microsoft.Office.Interop.Excel.Range)WS.get_Range(c1, c2);
oRange.Columns.AutoFit();
oRange.Rows.AutoFit();
WS.Application.ActiveWindow.SplitRow = 1;
WS.Application.ActiveWindow.FreezePanes = true;
ObjExcel.Visible = true;
ObjExcel.UserControl = true;
Rules.Clear();
NetworkObj.Clear();
Services.Clear();
button1.Enabled = true;
numericUpDown1.Enabled = true;
numericUpDown2.Enabled = true;
checkBox2.Enabled = true;
checkBox1.Enabled = true;
Marshal.ReleaseComObject(WS);
WS = null;
Marshal.ReleaseComObject(WB);
WB = null;
Marshal.ReleaseComObject(ObjExcel);
ObjExcel = null;
GC.Collect();
}
}
}
private void Excel_SheetSelectionChange(object worksheet, Excel.Range range)
{
//#if DEBUG
// Log.Logger.Debug("SheetSelectionChange");
//#endif
}
/// <summary>
/// Given a range of dotted-quad IP addresses, finds a single subnet that encapsulates all
/// of the addresses listed.
/// </summary>
/// <param name="AddrRange">Range of IP addresses in dotted-quad format</param>
/// <returns>Network block, in VLSM slash-style format</returns>
public object Summarize4(Excel.Range AddrRange)
{
IPAddress _min = IPAddress.Any;
IPAddress _max = IPAddress.Any;
AddressFamily AF = AddressFamily.InterNetwork;
IPAddressComparer AC = new IPAddressComparer();
object Values = AddrRange.Value;
if (AddrRange.Rows.Count == 1 && AddrRange.Columns.Count == 1)
{
IPAddress _addr;
if (!(IPAddress.TryParse(Values.ToString(), out _addr)))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
_min = _addr;
_max = _addr;
}
else
{
object[,] _array = (object[, ])Values;
for (int r = 1; r <= _array.GetLength(0); r++)
{
for (int c = 1; c <= _array.GetLength(1); c++)
{
object cellObj = _array.GetValue(r, c);
string cellValue = cellObj.ToString();
IPAddress _addr = null;
if (!(IPAddress.TryParse(cellValue, out _addr)))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
if (AF == AddressFamily.Unknown)
{
AF = _addr.AddressFamily;
}
else
{
if (AF != _addr.AddressFamily)
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
}
if (_min == IPAddress.Any || AC.Compare(_min, _addr) < 0)
{
_min = _addr;
}
if (_max == IPAddress.Any || AC.Compare(_max, _addr) > 0)
{
_max = _addr;
}
}
}
}
for (int i = 32; i >= 0; i--)
{
IPAddress mask = _ConvertBitlenToMask4(i);
IPAddress _network = _PerformBitmask(_min, mask);
if (_network.Equals(_PerformBitmask(_max, mask)))
{
return(String.Format("{0}/{1}", _network, i));
}
}
// Flow of the program should never get here.
return("0.0.0.0/0");
}
private void WorksheetFloorGridSize_Change(Excel.Range Target)
{
Debug.WriteLine("Floor Grid Size Worksheet changed");
}
/// <summary>
/// Attempts a TCP connection to the given IP address and port.
/// </summary>
/// <param name="Address">IP address of the target</param>
/// <param name="Port">Target TCP port</param>
/// <param name="Timeout">Timeout in milliseconds. (Defaults to 3000ms.)</param>
/// <returns></returns>
public object TCPConnect(string Address, decimal Port, [Optional] object Timeout)
{
IPAddress _addr;
if (!IPAddress.TryParse(Address, out _addr))
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
int _port = (int)Port;
if (_port < 0 || _port > 65535)
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
int _timeout = 3000;
if (!(Timeout is System.Reflection.Missing))
{
if (Timeout is decimal || Timeout is double)
{
_timeout = Convert.ToInt32(Timeout);
}
if (Timeout is Excel.Range)
{
Excel.Range _timeoutRng = (Excel.Range)Timeout;
_timeout = Convert.ToInt32(_timeoutRng.Value);
}
}
if (_timeout < 1)
{
return(new ErrorWrapper((int)CVErrNum.ErrValue));
}
IPEndPoint _ep = new IPEndPoint(_addr, _port);
Socket _sock = new Socket(_addr.AddressFamily, SocketType.Stream, ProtocolType.Tcp);
_sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Linger, new LingerOption(true, 0));
//_sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.DontLinger, true);
_sock.Blocking = false;
bool _success = false;
DateTime _tZero = DateTime.Now;
try
{
IAsyncResult _ar = _sock.BeginConnect(_ep, null, null);
_ar.AsyncWaitHandle.WaitOne(_timeout, true);
_success = _sock.Connected;
if (_success)
{
_sock.EndConnect(_ar);
}
}
catch (SocketException e)
{
if (e.ErrorCode == 10013)
{
return("Failed");
}
return(String.Format("Cannot connect: {0}: {1}", e.ErrorCode, e.Message));
}
TimeSpan _timeTaken = DateTime.Now - _tZero;
//_sock.Close(0);
if (_success)
{
return(String.Format("Success: {0}ms", _timeTaken.TotalMilliseconds));
}
else
{
return("Failed");
}
}
private void sendexcel(DataGridView drg)
{
int cellfin;
cellfin = dtgDetalle.ColumnCount;
//LLAMA
copyall();
Microsoft.Office.Interop.Excel.Application excell;
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet Sheet;
object miobj = System.Reflection.Missing.Value;
excell = new Excel.Application();
excell.Visible = true;
col = drg.Columns.Count / 26;
string Letracol = "ABCDEFEHIJKLMNOPQRSTUVWXYZ";
// string Complementocol;
//Determinando la letra que se usara despues de la columna 26
//if (col > 0)
//{
// Columnas = drg.Columns.Count - (26 * col);
// Complementocol = Letracol.ToString().Substring(col - 1, 1);
//}
//else
//{
// Columnas = drg.Columns.Count;
// Complementocol = "";
//}
//string ColumnaFinal;
//incre = Encoding.ASCII.GetBytes("A")[0];
string activecell = "A1";
string activecell2 = Regex.Replace(activecell, @"[^\d]", "");
int cant_lt = (activecell.Replace("$", "").Length) - activecell2.Length;
string letra = activecell.Substring(0, cant_lt);
int indx = Letracol.IndexOf(letra.Substring(0, 1));
if (col > 0)
{
Columnas = drg.Columns.Count - (26 * col);
Complementocol = Letracol.ToString().Substring(col - 1, 1);
}
if (cant_lt > 1)
{
indx2 = Letracol.IndexOf(letra.Substring(1, 1));
if (cant_lt == 2)
{
if (drg.Columns.Count + indx > 26)
{
Columnas = drg.Columns.Count - 1;
Complementocol = Letracol.ToString().Substring(indx2 + 1, 1);
incre = Encoding.ASCII.GetBytes(letra.Substring(0, 1))[0];
incre = incre - drg.Columns.Count;
}
else
{
Columnas = drg.Columns.Count;
Complementocol = Letracol.ToString().Substring(indx2, 1);
incre = Encoding.ASCII.GetBytes(letra.Substring(1, 1))[0];
}
}
}
else
if (drg.Columns.Count + indx > 26)
{
Columnas = drg.Columns.Count - 1;
Complementocol = Letracol.ToString().Substring(0, 1);
incre = Encoding.ASCII.GetBytes(Complementocol)[0];
}
else
{
Columnas = drg.Columns.Count;
Complementocol = "";
incre = Encoding.ASCII.GetBytes(letra)[0];
}
string ColumnaFinal;
ColumnaFinal = Complementocol.ToString() + Convert.ToChar(incre + Columnas - 1).ToString();
workbook = excell.Workbooks.Add(miobj);
Sheet = workbook.Worksheets.get_Item(1);
Excel.Range rg = Sheet.Cells[5, 1];
Excel.Range Enc;
Excel.Range det;
Excel.Range RN;
Excel.Range Report;
Excel.Range Reportxt;
rg.Select();
// obtener colummnas de encabezado
for (int c = 0; c < drg.Columns.Count; c++)
{
//datos = nombre de la datatable que se ocupa en el grid
Sheet.Cells[4, c + 1] = String.Format("{0}", dtpF.Columns[c].Caption);
}
Sheet.PasteSpecial(rg, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
try
{
// nombre de la empresa
RN = Sheet.get_Range("A1", ColumnaFinal + "1");
RN.Font.Name = "Arial Rounded MT Bold";
//rango.Font.Color = Color.Blue;
RN.Font.Size = 14;
Sheet.Cells[1, 1] = "LESA S.A. DE C.V.";
RN.Merge();
RN.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Nombre del Reporte
Report = Sheet.get_Range("A2", ColumnaFinal + "2");
Report.Font.Name = "Arial Rounded MT Bold";
Report.Font.Size = 12;
Sheet.Cells[2, 1] = "REGISTRO DE ORDENES" + " FECHA " + desde + " a " + hasta;
Report.Select();
Report.Merge();
Report.Font.Bold = true;
Report.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Reportxt = Sheet.get_Range("A3", ColumnaFinal + "3");
Reportxt.Font.Name = "Arial Rounded MT Bold";
Reportxt.Font.Size = 12;
//Sheet.Cells[3, 1] = "BODEGAS " + Bodegaini + " a " + Bodegafin + " ";
//Reportxt.Select();
//Reportxt.Merge();
//Reportxt.Font.Bold = true;
//Reportxt.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//ENCABEZDO DE COLUMNAS
Enc = Sheet.get_Range("A4", ColumnaFinal + 4);
Enc.Font.Name = "Arial Rounded MT Bold";
Enc.Font.Size = 9;
Enc.Borders.LineStyle = Excel.XlLineStyle.xlDouble;
Enc.Font.Bold = true;
//DETALLE
//ENCABEZDO DE COLUMNAS
}
catch (SystemException exec)
{
MessageBoxButtons bt1 = MessageBoxButtons.OK;
DialogResult result = MessageBox.Show(exec.ToString(), "!!!!ERROR!!!!", bt1, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
}
public void Spread(Excel.Range cell)
{
cell.Value2 = "{object}";
}
private static void copyAndInsertTotalFooter(Worksheet _worksheet, int _totalRows)
{
Microsoft.Office.Interop.Excel.Range _copyRange = _worksheet.Range["B165:E168"];
Microsoft.Office.Interop.Excel.Range _insertRange = _worksheet.Range["B" + (_totalRows + 3) + ":E" + (_totalRows + 6) + ""];
_insertRange.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, _copyRange.Cut());
}
public bool OnRightClick(Excel.Range target)
{
return(false);
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "D1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "2020";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Năm";
cl1.ColumnWidth = 13.5;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Tổng tiền";
cl2.ColumnWidth = 25.0;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "B3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
//-------------------------------------------------------------------------
/* Get data from Range to Parent.DT, field names to Fields */
void SheetToTable(Excel.Worksheet ws, Excel.Range rg = null, bool fieldsOnly = false)
{
Fields = new List <string>();
if (!fieldsOnly)
{
Parent.DT = new DataTable();
}
if (rg == null)
{
rg = ws.UsedRange;
}
if (rg.Columns.Count == 0 || rg.Rows.Count == 0)
{
return;
}
DataTable dt = new DataTable();
int r0 = (FirstLineNames ? 1 : 0);
// fields
for (int c = 1; c <= rg.Columns.Count; c++)
{
Type type = typeof(String);
if (FirstLineNames && rg.Cells[1, c] != null &&
rg.Cells[1, c].Value != null && !string.IsNullOrEmpty(rg.Cells[1, c].Value.ToString().Trim()))
{
string colName = rg.Cells[1, c].Value.ToString().Trim();
string tmpName = colName;
int suff = 0;
while (dt.Columns.OfType <DataColumn>().Count(x => x.ColumnName == colName) > 0)
{
colName = tmpName + "_" + (++suff).ToString();
}
dt.Columns.Add(colName, type);
}
else
{
dt.Columns.Add(GetRCName((rg.Cells[1, c] as Excel.Range).get_Address(), false), type);
}
}
Fields.AddRange(dt.Columns.OfType <DataColumn>().Select(x => x.ColumnName));
// data
if (!fieldsOnly)
{
object[,] dtArr; // for data from Range
if (rg.Columns.Count == 1 && rg.Rows.Count == 1) // because if it's only one cell get_Value() not return an array
{
dtArr = (object[, ])Array.CreateInstance(typeof(Object), new[] { 1, 1 }, new[] { 1, 1 });
dtArr[1, 1] = rg.get_Value();
}
else
{
dtArr = (object[, ])rg.get_Value();
}
Parent.DT = dt.Clone();
object[] dtRow = new object[dtArr.GetLength(1)]; // array for table row
for (int r = r0; r < dtArr.GetLength(0); r++)
{
for (int c = 0; c < dtArr.GetLength(1); c++)
{
dtRow[c] = dtArr[r + 1, c + 1];
}
Parent.DT.LoadDataRow(dtRow, true);
}
}
}
public void ecxelPoster()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "Poster";
_Excel.Application oXL = null;;
_Excel._Workbook oWB;
_Excel._Worksheet oSheetPoster;
_Excel._Worksheet oSheetRegnskab;
_Excel.Window oWindow;
_Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xlsx";
var JournalPoster = from h in Program.karPosteringer
join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1
from x in details1.DefaultIfEmpty()
orderby h.Nr
select new clsJournalposter
{
ds = (x.Type == "Drift") ? "D" : "S",
k = IUAP(x.Type, x.DK),
Konto = h.Konto.ToString() + "-" + x.Kontonavn,
Dato = h.Dato,
Bilag = h.Bilag,
Nr = h.Nr,
Id = h.Id,
Tekst = h.Tekst,
Beløb = h.Nettobeløb,
};
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new _Excel.Application();
//oXL.Visible = false;
oXL.Visible = true; //For debug
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
Program.frmMain.MainformProgressBar.Value = 0;
Program.frmMain.MainformProgressBar.Minimum = 0;
Program.frmMain.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count();
Program.frmMain.MainformProgressBar.Step = 1;
Program.frmMain.MainformProgressBar.Visible = true;
foreach (clsJournalposter m in JournalPoster)
{
Program.frmMain.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheetPoster.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "Trans2Summa3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheetPoster.Cells[1, col] = heading;
}
}
}
}
}
oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = _Excel.Constants.xlCenter;
oRng.VerticalAlignment = _Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "D" + row.ToString();
oRng = oSheetPoster.get_Range("D2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheetPoster.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.FreezePanes = true;
oSheetPoster.get_Range("A1", Missing.Value).Select();
oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
//oXL.Visible = true; //For debug
_Excel.Range x1 = oSheetPoster.Cells[1, 1];
_Excel.Range x2 = oSheetPoster.Cells[row, 9];
_Excel.Range xx = oSheetPoster.get_Range(x1, x2);
_Excel.PivotField _pvtField = null;
_Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard(
_Excel.XlPivotTableSourceType.xlDatabase, //SourceType
xx, //SourceData
oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination
"PivotTable1", //TableName
System.Type.Missing, //RowGrand
System.Type.Missing, //CollumnGrand
System.Type.Missing, //SaveData
System.Type.Missing, //HasAutoformat
System.Type.Missing, //AutoPage
System.Type.Missing, //Reserved
System.Type.Missing, //BackgroundQuery
System.Type.Missing, //OptimizeCache
System.Type.Missing, //PageFieldOrder
System.Type.Missing, //PageFieldWrapCount
System.Type.Missing, //ReadData
System.Type.Missing); //Connection
_pvtField = (_Excel.PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField;
_pvtField.Function = _Excel.XlConsolidationFunction.xlSum;
_pvtField.NumberFormat = "#,##0";
oSheetRegnskab.Name = "Regnskab";
oRng = oSheetRegnskab.get_Range("D3", Missing.Value);
oRng.Select();
bool[] Periods = { false, false, false, false, true, false, true };
oRng.Group(true, true, Missing.Value, Periods);
oRng = oSheetRegnskab.get_Range("D4", "P4");
oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight;
oSheetRegnskab.PageSetup.LeftHeader = "&14" + rec_regnskab.Navn;
oSheetRegnskab.PageSetup.CenterHeader = "";
oSheetRegnskab.PageSetup.RightHeader = "&P af &N";
oSheetRegnskab.PageSetup.LeftFooter = "&Z&F";
oSheetRegnskab.PageSetup.CenterFooter = "";
oSheetRegnskab.PageSetup.RightFooter = "&D&T";
oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.PrintHeadings = false;
oSheetRegnskab.PageSetup.PrintGridlines = true;
oSheetRegnskab.PageSetup.CenterHorizontally = false;
oSheetRegnskab.PageSetup.CenterVertically = false;
oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed;
oWB.ShowPivotTableFieldList = false;
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
_Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i);
if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster"))
{
oSheetWrk.Delete();
}
}
oSheetRegnskab.get_Range("A1", Missing.Value).Select();
oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
Program.frmMain.MainformProgressBar.Visible = false;
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
/// <summary>
/// 射出日报表
/// </summary>
/// <param name="details"></param>
private void ExportExcelForSheChu(IList <Model.ProduceInDepotDetail> details)
{
try
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 18]);
r.MergeCells = true;//合并单元格
//Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter = -4108;
//Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium= -4138;
//Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic= -4105;
excel.Cells[1, 1] = "射出日報表";
#region Set Header
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20;
excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 18]).HorizontalAlignment = -4108;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 18]).ColumnWidth = 12;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 2]).ColumnWidth = 20;
excel.get_Range(excel.Cells[2, 3], excel.Cells[2, 3]).ColumnWidth = 30;
excel.get_Range(excel.Cells[2, 11], excel.Cells[2, 12]).ColumnWidth = 20;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 18]).Interior.Color = 12566463;
excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 18]).RowHeight = 20;
excel.get_Range(excel.Cells[2, 1], excel.Cells[details.Count + 2, 18]).Font.Size = 13;
excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 18]).WrapText = true;
excel.get_Range(excel.Cells[3, 1], excel.Cells[details.Count + 2, 18]).EntireRow.AutoFit();
excel.Cells[2, 1] = "入庫日期";
excel.Cells[2, 2] = "入庫單號";
excel.Cells[2, 3] = "產品名稱";
excel.Cells[2, 4] = "公司部門";
excel.Cells[2, 5] = "單位";
excel.Cells[2, 6] = "生產數量";
excel.Cells[2, 7] = "合計生產";
excel.Cells[2, 8] = "合計合格";
excel.Cells[2, 9] = "合計入庫";
excel.Cells[2, 10] = "合計轉生產";
excel.Cells[2, 11] = "加工單";
excel.Cells[2, 12] = "客戶訂單號";
excel.Cells[2, 13] = "生產數量";
excel.Cells[2, 14] = "合格數量";
excel.Cells[2, 15] = "轉生產數量";
excel.Cells[2, 16] = "入庫數量";
excel.Cells[2, 17] = "班別";
excel.Cells[2, 18] = "機台";
#endregion
for (int i = 0; i < details.Count; i++)
{
excel.Cells[i + 3, 1] = details[i].mProduceInDepotDate.HasValue ? details[i].mProduceInDepotDate.Value.ToString("yyyy-MM-dd") : "";
excel.Cells[i + 3, 2] = details[i].ProduceInDepotId;
excel.Cells[i + 3, 3] = details[i].ProductName;
excel.Cells[i + 3, 4] = details[i].WorkHousename;
excel.Cells[i + 3, 5] = details[i].ProductUnit;
excel.Cells[i + 3, 6] = details[i].PronoteHeaderSum;
excel.Cells[i + 3, 7] = details[i].HeJiProceduresSum;
excel.Cells[i + 3, 8] = details[i].HeJiCheckOutSum;
excel.Cells[i + 3, 9] = details[i].HeJiProduceQuantity;
excel.Cells[i + 3, 10] = details[i].HeJiProduceTransferQuantity;
excel.Cells[i + 3, 11] = details[i].PronoteHeaderId;
excel.Cells[i + 3, 12] = details[i].CusXOId;
excel.Cells[i + 3, 13] = details[i].ProceduresSum;
excel.Cells[i + 3, 14] = details[i].CheckOutSum;
excel.Cells[i + 3, 15] = details[i].ProduceTransferQuantity;
excel.Cells[i + 3, 16] = details[i].ProduceQuantity;
excel.Cells[i + 3, 17] = details[i].BusinessHoursType;
excel.Cells[i + 3, 18] = details[i].Machine;
}
excel.Visible = true;//是否打开该Excel文件
excel.WindowState = XlWindowState.xlMaximized;
}
catch
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}
/*
* inputs: category
* date range
* Dates must be format: yyyymmdd
* output: sum of purchases
*/
public double calculateSum(string category, string fromDate, string toDate)
{
int maxIndex = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
double sum = 0;
int fromDateInt = Int32.Parse(fromDate);
int toDateInt = Int32.Parse(toDate);
category = category.ToLower();
foreach (Excel.Range row in xlWorkSheet.UsedRange.Rows)
{
dateCell = xlWorkSheet.Cells[row.Row, 1];
catCell = xlWorkSheet.Cells[row.Row, 2];
valCell = xlWorkSheet.Cells[row.Row, 3];
if (row.Row == 1) continue;
if (category == "all")
{
if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt)
{
sum += valCell.Value;
}
}
else if (category == "other")
{
if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt
&& !catCell.Value.ToLower().Contains("housing") &&
!catCell.Value.ToLower().Contains("food") &&
!catCell.Value.ToLower().Contains("savings") &&
!catCell.Value.ToLower().Contains("transportation") &&
!catCell.Value.ToLower().Contains("past expenses"))
{
sum += valCell.Value;
}
}
else
{
if (dateCell.Value >= fromDateInt && dateCell.Value <= toDateInt
&& catCell.Value.ToLower().Contains(category))
{
sum += valCell.Value;
}
}
releaseObject(catCell);
releaseObject(dateCell);
releaseObject(valCell);
}
return sum;
}
// Форматирование + формулы
#region Форматирование
private static void GetSum(Excel.Worksheet ws, int firstRow, int lastRow, bool getSum, string nameFile)
{
switch (nameFile)
{
case "Книга покупок.xlsx": // Форматирование для книги "Книга покупок"
if (getSum == true)
{
sumTableExcel(ws, 1, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol2 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 5]]; // Столбцы 2-7
Excel.Range rngCol3 = (Excel.Range)ws.Range[ws.Cells[firstRow, 6], ws.Cells[lastRow, 6]]; // Столбец 8
Excel.Range rngCol4 = (Excel.Range)ws.Range[ws.Cells[firstRow, 8], ws.Cells[lastRow, 10]]; // Столбцы 9-11
// Форматирование выравнивания
rngCol1.HorizontalAlignment = Excel.Constants.xlRight;
rngCol2.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol3.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol4.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol4.NumberFormat = "#,##0.00";
break;
case "Книга продаж.xlsx": // Форматирование для книги "Книга продаж"
if (getSum == true)
{
sumTableExcel(ws, 2, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol5 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбцы 1-2
Excel.Range rngCol5_1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol6 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 5]]; // Столбцы 3-5
Excel.Range rngCol8 = (Excel.Range)ws.Range[ws.Cells[firstRow, 7], ws.Cells[lastRow, 9]]; // Столбцы 6-8
// Форматирование выравнивания
rngCol5.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol5_1.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol6.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol8.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol8.NumberFormat = "#,##0.00";
break;
case "Отчет по продажам.xlsx": // Форматирование для книги "Отчет по продажам"
if (getSum == true)
{
sumTableExcel(ws, 3, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol9 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3
Excel.Range rngCol10 = (Excel.Range)ws.Range[ws.Cells[firstRow, 6], ws.Cells[lastRow, 6]]; // Столбец 6
Excel.Range rngCol10_1 = (Excel.Range)ws.Range[ws.Cells[firstRow, 7], ws.Cells[lastRow, 9]]; // Столбцы 7-9
// Форматирование выравнивания
rngCol9.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol10.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol10_1.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol10_1.NumberFormat = "#,##0.00";
break;
case "Отчет по менеджерам.xlsx": // Форматирование для книги "Отчет по менеджерам"
if (getSum == true)
{
sumTableExcel(ws, 4, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol11 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 2]]; // Столбцы 1-2
Excel.Range rngCol12 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 5]]; // Столбцы 3-5
// Форматирование выравнивания
rngCol11.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol12.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol12.NumberFormat = "#,##0.00";
break;
case "Счет - Накладная.xlsx": // Форматирование для книги "Счет - накладная"
if (getSum == true)
{
sumTableExcel(ws, 5, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol13 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol14 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2
Excel.Range rngCol15 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 4]]; // Столбцы 10-11
Excel.Range rngCol16 = (Excel.Range)ws.Range[ws.Cells[firstRow, 5], ws.Cells[lastRow, 6]]; // Столбцы 12-13
// Форматирование выравнивания
rngCol13.HorizontalAlignment = Excel.Constants.xlRight;
rngCol14.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol15.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol16.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol16.NumberFormat = "#,##0.00";
break;
case "Товарный чек.xlsx": // Форматирование для книги "Товарный чек"
if (getSum == true)
{
sumTableExcel(ws, 6, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol17 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol18 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2
Excel.Range rngCol19 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 4]]; // Столбцы 3-4
Excel.Range rngCol20 = (Excel.Range)ws.Range[ws.Cells[firstRow, 5], ws.Cells[lastRow, 6]]; // Столбцы 12-13
// Форматирование выравнивания
rngCol17.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol18.HorizontalAlignment = Excel.Constants.xlRight;
rngCol19.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol20.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol20.NumberFormat = "#,##0.00";
break;
case "ТОРГ-12 (Товарная накладная).xlsx": // Форматирование для книги "ТОРГ-12"
if (getSum == true)
{
sumTableExcel(ws, 7, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol21 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol22 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 4]]; // Столбец 4
Excel.Range rngCol23 = (Excel.Range)ws.Range[ws.Cells[firstRow, 18], ws.Cells[lastRow, 24]]; // Столбцы 18-24
Excel.Range rngCol24 = (Excel.Range)ws.Range[ws.Cells[firstRow, 27], ws.Cells[lastRow, 54]]; // Столбцы 27-54
// Форматирование выравнивания
rngCol21.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol22.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol23.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol24.HorizontalAlignment = Excel.Constants.xlRight;
// Установка формата
rngCol24.NumberFormat = "#,##0.00";
break;
case "ТОРГ-13 (Перемещение).xlsx": // Форматирование для книги "ТОРГ-13"
if (getSum == true)
{
sumTableExcel(ws, 8, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol25 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol26 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3
Excel.Range rngCol29 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 6]]; // Столбцы 4-6
// Форматирование выравнивания
rngCol25.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol25.VerticalAlignment = Excel.Constants.xlTop;
rngCol26.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol29.HorizontalAlignment = Excel.Constants.xlRight;
// Высота ячеек
rngCol25.RowHeight = 12;
// Установка формата
rngCol29.NumberFormat = "#,##0.00";
break;
case "Прайс-лист.xlsx": // Форматирование для книги "Прайс-лист"
// Указание диапазонов для редактирования
Excel.Range rngCol30 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol31 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 2]]; // Столбец 2
Excel.Range rngCol32 = (Excel.Range)ws.Range[ws.Cells[firstRow, 3], ws.Cells[lastRow, 3]]; // Столбец 3
Excel.Range rngCol33 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 4]]; // Столбец 4
// Форматирование выравнивания
rngCol30.HorizontalAlignment = Excel.Constants.xlRight;
rngCol30.Font.Size = 10;
rngCol31.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol31.Font.Size = 10;
rngCol32.HorizontalAlignment = Excel.Constants.xlCenter;
rngCol32.Font.Size = 10;
rngCol33.HorizontalAlignment = Excel.Constants.xlRight;
rngCol33.Font.Size = 10;
// Установка формата
rngCol33.NumberFormat = "#,##0.00";
break;
case "Остаток товара на складе.xlsx": // Форматирование для книги "Остаток товара на складе"
if (getSum == true)
{
sumTableExcel(ws, 9, firstRow, lastRow);
}
// Указание диапазонов для редактирования
Excel.Range rngCol34 = (Excel.Range)ws.Range[ws.Cells[firstRow, 1], ws.Cells[lastRow, 1]]; // Столбец 1
Excel.Range rngCol35 = (Excel.Range)ws.Range[ws.Cells[firstRow, 2], ws.Cells[lastRow, 3]]; // Столбцы 2-3
Excel.Range rngCol36 = (Excel.Range)ws.Range[ws.Cells[firstRow, 4], ws.Cells[lastRow, 8]]; // Столбцы 4-8
// Форматирование выравнивания
rngCol34.HorizontalAlignment = Excel.Constants.xlRight;
rngCol34.Font.Size = 11;
rngCol34.Font.Name = "Calibri";
rngCol35.HorizontalAlignment = Excel.Constants.xlLeft;
rngCol35.Font.Size = 11;
rngCol35.Font.Name = "Calibri";
rngCol36.HorizontalAlignment = Excel.Constants.xlRight;
rngCol36.Font.Size = 11;
rngCol36.Font.Name = "Calibri";
// Установка формата
rngCol36.NumberFormat = "#,##0.00";
break;
}
}
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();
}