public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath)
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Your file is saved" + SaveFilePath);
}
/// <summary>
/// Create an empty Excel spreadsheet, with no associated file
/// </summary>
public ExcelFile()
{
if (_xlApp == null)
_xlApp = new Excel.Application();
var workBooks = _xlApp.Workbooks;
_xlWorkbook = workBooks.Add();
_xlDefaultWorkSheet = _xlWorkbook.Worksheets[1];
}
//private void wrtBySht(List<string> workList)
private void wrtBySht(string shtName)
{
xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName);
xlWorkSht.Activate();
string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index
Excel.Range rng = xlWorkSht.get_Range(idx, idx);
DataTable dt;
if (shtName == "InstrumentClassData")
dt = Util.DbConn.SqlTsk.GetTable("procGetInstData");
else
dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName);
int j = 1;
int i = 0;
foreach (DataRow row in dt.Rows)
{
for (i = 0; i < dt.Columns.Count; i++)
{
rng[j + 1, i + 1].Value = row[i].ToString();
}
j++;
if (j > dt.Rows.Count)
{
break;
}
}
rng[j + 1, 1].Value = "end";
}
private void button1_Click(object sender, EventArgs e)
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(path);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
BindingList<Dompet> DompetList = new BindingList<Dompet>();
for (int index = 2; index <= lastrow; index++)
{
System.Array MyValues =
(System.Array)MySheet.get_Range
("A" + index.ToString(),"F" + index.ToString()).Cells.Value;
DompetList.Add(new Dompet {
JmlPemesanan = MyValues.GetValue(1,1).ToString(),
JmlPekerja = MyValues.GetValue(1,2).ToString(),
Peralatan = MyValues.GetValue(1,3).ToString(),
JenisKulit = MyValues.GetValue(1,4).ToString(),
ModelDompet = MyValues.GetValue(1,5).ToString(),
Prediksi = MyValues.GetValue(1,6).ToString()
});
}
dataGridView1.DataSource = (BindingList<Dompet>)DompetList;
dataGridView1.AutoResizeColumns();
}
public TaskPriority()
{
InitializeComponent();
missing = System.Reflection.Missing.Value;
config_data.ConfigFile = Environment.GetEnvironmentVariable("USERPROFILE")+"\\IntCallBack.xls";
xlApp = new msexcel.Application();
time_wasting = false;
if (File.Exists(config_data.ConfigFile))
{
xlWorkBook = xlApp.Workbooks.Open(config_data.ConfigFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
xlWorkSheet = (msexcel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
double dummy = (double) (xlWorkSheet.Cells[1, 2] as msexcel.Range ).Value ;
config_data.PopUp = ((int)dummy == 1) ? true : false;
config_data.RFrequency = (int)(xlWorkSheet.Cells[2, 2] as msexcel.Range).Value;
config_data.Urgent_Hrs = (int)(xlWorkSheet.Cells[3, 2] as msexcel.Range).Value;
config_data.Urgent_Mins = (int)(xlWorkSheet.Cells[4, 2] as msexcel.Range).Value;
config_data.task1 = (string) (xlWorkSheet.Cells[5, 2] as msexcel.Range).Value;
config_data.task2 = (string)(xlWorkSheet.Cells[6, 2] as msexcel.Range).Value;
config_data.task3 = (string)(xlWorkSheet.Cells[7, 2] as msexcel.Range).Value;
config_data.task4 = (string)(xlWorkSheet.Cells[8, 2] as msexcel.Range).Value;
re_load_flag = true;
}
else
{
xlWorkBook = xlApp.Workbooks.Add(missing);
xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
config_data.PopUp = true;
config_data.RFrequency = 3;
config_data.Urgent_Hrs = 8;
config_data.Urgent_Mins = 0;
config_data.task1 = config_data.task2 = config_data.task3 = config_data.task4 = "";
xlWorkSheet.Cells[1, 1] = "PopUP";
xlWorkSheet.Cells[2, 1] = "Frequency";
xlWorkSheet.Cells[3, 1] = "Urgent Hrs";
xlWorkSheet.Cells[4, 1] = "Urgent Mins";
xlWorkSheet.Cells[5,1] = "Task 1";
xlWorkSheet.Cells[6,1] = "Task 2";
xlWorkSheet.Cells[7,1] = "Task 3";
xlWorkSheet.Cells[8,1] = "Task 4";
xlWorkSheet.Cells[1, 2] = (config_data.PopUp == true) ? "1" : "2";
xlWorkSheet.Cells[2, 2] = config_data.RFrequency.ToString();
xlWorkSheet.Cells[3, 2] = config_data.Urgent_Hrs.ToString();
xlWorkSheet.Cells[4, 2] = config_data.Urgent_Mins.ToString();
xlWorkSheet.Cells[5, 2] = config_data.task1;
xlWorkSheet.Cells[6, 2] = config_data.task1;
xlWorkSheet.Cells[7, 2] = config_data.task1;
xlWorkSheet.Cells[8, 2] = config_data.task1;
xlWorkBook.SaveAs(config_data.ConfigFile, msexcel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, msexcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
//xlWorkBook.Close();
re_load_flag = false;
}
}
public void export()
{
try
{
//excelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
//wb = excelApp.Workbooks.Add();
wb = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 데이타 넣기
int r = 1;
foreach (var d in this.exceptionList_)
{
ws.Cells[r, 1] = d.Message;
r++;
}
//this.wb.Worksheets.Add(ws);
}
finally
{
// Clean up
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}
public ReportGenerator(Excel.Worksheet src_worksheet, Excel.Worksheet dest_worksheet)
{
_src_worksheet = src_worksheet;
_dest_worksheet = dest_worksheet;
writeProjectNamesToDestSheet();
}
public getData readData()
{
getData.dateT.Clear();
getData.prj.Clear();
getData.descr.Clear();
getData.taskTime.Clear();
notes.Clear();
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
//excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1
// WorkSheetExcel.Cells[i, 1].Text.ToString() != ""
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString());
getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString());
getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString());
getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString());
}
WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя
ExcelApp.Quit();
GC.Collect();
getData.dateT.Reverse();
getData.prj.Reverse();
getData.descr.Reverse();
getData.taskTime.Reverse();
return getData;
}
/// <summary>
/// Creates a spreadsheet in the give xls filename.
/// </summary>
/// <param name="filename">The complete filename with the absolute path.</param>
/// <param name="sheetname">The name of the sheet e.g. Hidden</param>
/// <returns>True if succeeded, false if failed.</returns>
public static bool createWorksheet(String filename, String sheetname, bool needsToBeHidden = false)
{
successStatus = false;
try
{
successStatus = openXlApp();
CurrentSpreadSheet css = CurrentSpreadSheet.Instance;
//checking if the call is being made for the currently open worbook. this is less expensive.
if ((css.CurrentWorkBook != null) && (css.CurrentWorkBook.FullName == filename))
{
xlSheets = css.CurrentWorkBook.Sheets as Excel.Sheets;
}
else
{
xlWorkbook = openXlWorkBook(filename);
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
}
xlSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlSheets.Count + 1]);
xlSheet.Name = sheetname;
if (needsToBeHidden) xlSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
xlWorkbook.Save();
successStatus = quitXlApp();
}
finally
{
garbageCollect();
}
return successStatus;
}
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelBase(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
public Matrix_Worksheet(Matrix_Workbook matrixWorkbook, Microsoft.Office.Interop.Excel.Worksheet workSheet)
{
_matrixWorkbook = matrixWorkbook;
_worksheet = workSheet;
InitializeControlData();
}
public void BadTaskSpecExtractionAndTaskFactoryParsingTest()
{
ws = (Excel.Worksheet)wss["TestTasks2"];
IEnumerable<Specification> actualTaskSpecs;
Assert.IsFalse(excelController.ExtractData(out actualTaskSpecs));
}
private void Form1_Load(object sender, EventArgs e)
{
app = new Excel.Application();
app.Visible = false;
workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls");
worksheet = workbook.ActiveSheet;
int i = 0;
string today = DateTime.Now.ToString("dd.MM");
string tomorrow = DateTime.Now.AddDays(1).ToString("dd.MM");
for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
{
if (worksheet.Cells[2][i].Value == "Іб та ПД" || worksheet.Cells[3][i].Value == "Іб та ПД" || worksheet.Cells[2][i].Value == "Інформаційної безпеки та передачі даних" || worksheet.Cells[3][i].Value == "Інформаційної безпеки та передачі даних")
{
string birthday = worksheet.Cells[4][i].Value.ToString("dd.MM");
if (birthday == today)
{
label1.Text = "";
label1.Text += "Сегодня свой день рождения отмечает " + worksheet.Cells[1][i].Value;
}
if (birthday == tomorrow)
{
label1.Text = "";
label1.Text += "Завтра свой день рождения отмечает " + worksheet.Cells[1][i].Value;
}
}
}
app.Quit();
}
public static void InitializeExcel()
{
_myApp = new Excel.Application {Visible = false};
_myBook = _myApp.Workbooks.Open(ExcelPath);
_mySheet = (Excel.Worksheet)_myBook.Sheets[1]; // Explict cast is not required here
_lastRow = _mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
}
private void Init()
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
public Sheet(Excel.Worksheet _worksheet)
{
this.Base = _worksheet;
this.CurrentRow = 1;
this.CurrentCol = 1;
this.Valid = ReadValues();
}
static void GetVisibleFormExcel()
{
//Console.WriteLine("please choose Excel File");
//OpenFileDialog ofD = new OpenFileDialog(); ofD.ShowDialog();
//string fName = ofD.FileName;
//Excel.Application xApp = new Excel.Application();
//xApp.Visible = true;
//Excel.Workbook xbook = xApp.Workbooks.Open("C:\\share\\temp.xlsx");
xSheet = xbook.Sheets[2];
xSheet.Select();
Excel.Range xRange = null;
string appKeyStr = null; string visibleStr = null;
int i = 2;
xRange = xSheet.Cells[i, 1]; appKeyStr = xRange.Value2;
while (!String.IsNullOrEmpty(appKeyStr))
{
appKeyArr.Add(appKeyStr.ToString().Trim());
xRange = xSheet.Cells[i, 3]; visibleStr = xRange.Value2.ToString().Trim(); VisibleArr.Add(visibleStr);
//Console.WriteLine("Name:{0} IE:{1} FireFox:{2} Chrome:{3}", appName,ie,firefox,chrome);
i++;
xRange = xSheet.Cells[i, 1]; appKeyStr = xRange.Value2;
}
//Console.WriteLine("Done");
xSheet = null;
//xbook = null; xApp.Quit(); xApp = null;
}
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 ComExportExcel(string templatePath)
{
xlApp = new ComExcel.Application();
xlWorkBook = xlApp.Workbooks.Open(templatePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0);
//xlWorkBook = xlApp.Workbooks.Open(templatePath, ReadOnly:false, Editable:true );
xlWorkSheet = (ComExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
public void ReadProcesses()
{
count = 0;
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
//Читаем данные по проектам
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes
List<string> row = new List<string>();
int n = 6;
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
row = new List<string>();
for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j
Mas.Add(row); //строка добавляется в массив
count++;
}
//test = WorkSheetExcel.Cells[2, 1];
WorkBookExcel.Close(false, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
}
//加载
private void Form1_Load(object sender, EventArgs e)
{
panel1.Visible = false;
app = new MSExcel.Application();
app.Visible = false;
book = app.Workbooks.Open(@"D:\template.xls");
sheet = (MSExcel.Worksheet)book.ActiveSheet;
//串口设置默认选择项
cbSerial.SelectedIndex = 1; //note：获得COM9口，但别忘修改
cbBaudRate.SelectedIndex = 5;
cbDataBits.SelectedIndex = 3;
cbStop.SelectedIndex = 0;
cbParity.SelectedIndex = 0;
//sp1.BaudRate = 9600;
Control.CheckForIllegalCrossThreadCalls = false; //这个类中我们不检查跨线程的调用是否合法(因为.net 2.0以后加强了安全机制,，不允许在winform中直接跨线程访问控件的属性)
sp1.DataReceived += new SerialDataReceivedEventHandler(sp1_DataReceived);
//sp1.ReceivedBytesThreshold = 1;
radio1.Checked = true; //单选按钮默认是选中的
rbRcvStr.Checked = true;
//准备就绪
sp1.DtrEnable = true;
sp1.RtsEnable = true;
//设置数据读取超时为1秒
sp1.ReadTimeout = 1000;
sp1.Close();
}
internal OrderingSheet(bool isUnscheduled)
{
if (!Globals.DataSet.IsLastDayComplete())
{
throw new ApplicationException(Globals.ThisWorkbook.IncompleteDataMessage);
}
this.orderDate = Globals.DataSet.MaxDate;
string worksheetName;
if (isUnscheduled)
{
worksheetName = ExcelHelpers.CreateValidWorksheetName(
String.Format(
CultureInfo.CurrentUICulture,
Properties.Resources.UnscheduledOrderSheetName,
this.orderDate.ToShortDateString()));
}
else
{
worksheetName = ExcelHelpers.CreateValidWorksheetName(
String.Format(
CultureInfo.CurrentUICulture,
Properties.Resources.WeeklyOrderSheetName,
this.orderDate.ToShortDateString()));
}
Excel.Worksheet worksheet = null;
// 如果该名称已经存在，则在创建工作表时将引发异常。
try
{
worksheet = Globals.ThisWorkbook.CreateWorksheet(worksheetName);
}
catch (Exception ex)
{
string message;
if (isUnscheduled)
{
message = String.Format(
CultureInfo.CurrentUICulture,
Properties.Resources.UnscheduledOrderSheetCreationError,
worksheetName);
}
else
{
message = String.Format(
CultureInfo.CurrentUICulture,
Properties.Resources.WeeklyOrderSheetCreationError,
worksheetName);
}
throw new ApplicationException(message, ex);
}
this.worksheet = worksheet;
CreateOrder(isUnscheduled);
}
public ClusteringManager()
{
//prepare Excel objects:
m_ObjExcel = new Microsoft.Office.Interop.Excel.Application();
m_ObjWorkBook = m_ObjExcel.Workbooks.Open(
MethodInputResponse.INPUT_FILE_PATH,
0,
false,
5,
"",
"",
false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
m_ObjWorkSheet1 = (myExcel.Worksheet)m_ObjWorkBook.Sheets[1];
myExcel.Sheets xlSheets = m_ObjWorkBook.Sheets as myExcel.Sheets;
for (int i = 3; i < MAX_CLUSTERS_NUMBER; i++)
{
xlSheets.Add(Type.Missing, m_ObjWorkSheet1, Type.Missing, Type.Missing);
}
}
private void drawGraph()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook
while (thread1.IsAlive)
{
//son satır bulunuyor excel dosyasındaki
Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlWorkSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz
// alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
string[] HeatingData = ReceiveData.Split(':');
string[] HeatingData2 = HeatingData[1].Split('D');
var result = HeatingData2[0];
double heating = Convert.ToDouble(result);
theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");
Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor!
this.Invoke((MethodInvoker)delegate
{
chart1.Series["Series1"].Points.AddY(result);
// excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
});
}
}
catch
{
// MessageBox.Show("Dosya bulunamadı");
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Zaman";
xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";
xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
}
}
public static void InitializeExcel()
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
}
private void EnsureOneSheet()
{
if (String.IsNullOrEmpty(sheetName) || !hasSuch(sheetName))
{
workSheet = Globals.ThisAddIn.Application.Worksheets.Add();
sheetName = workSheet.Name;
}
}
public ThreadData(ref Mutex mut, ref Excel.Worksheet activeWorksheet, long startIndex, long endIndex, int threadId)
{
_mut = mut;
_activeWorksheet = activeWorksheet;
_startIndex = startIndex;
_endIndex = endIndex;
_threadId = threadId;
}
public ThreadData(ref Mutex mut, ref Excel.Worksheet activeWorksheet, long index, bool setTS, int threadId)
{
_mut = mut;
_activeWorksheet = activeWorksheet;
_index = index;
_setTS = setTS;
_threadId = threadId;
}
/// <summary>
/// 打开一个Excel文件
/// </summary>
/// <param name="FileName"></param>
public static void Open( string fileName )//
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add( fileName );
ws = wb.Worksheets[ 1 ] as Worksheet;
mFilename = fileName;
}
void exportBaoGia()
{
if (treeData.AllNodesCount == 0)
{
return;
}
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\SX." + Quotation.Code + ".xls";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\BaoGia.xls";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: " + Environment.NewLine + ex.Message);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo giá..."))
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
try
{
app = new Excel.Application();
app.Workbooks.Open(localPath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
DataTable dtItem = LibQLSX.Select("select * from vC_QuotationDetail_SX with(nolock) where ParentID = 0 and C_QuotationID = " + Quotation.ID);
for (int i = dtItem.Rows.Count - 1; i >= 0; i--)
{
int id = TextUtils.ToInt(dtItem.Rows[i]["ID"]);
string moduleCodeP = TextUtils.ToString(dtItem.Rows[i]["ModuleCode"]);
string moduleNameP = TextUtils.ToString(dtItem.Rows[i]["ModuleName"]);
decimal qtyP = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]);
decimal priceP = TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]);
string hangP = TextUtils.ToString(dtItem.Rows[i]["Manufacture"]);
DataTable dtC = LibQLSX.Select("select * from vC_QuotationDetail_SX with(nolock) where ParentID = " + id);
if (dtC.Rows.Count == 0)//nếu thiết bị không có thiết bị con
{
#region Add Parent
if (moduleCodeP.ToUpper().StartsWith("TPAD.") && moduleCodeP.Length == 10)
{
try
{
string spec = TextUtils.ToString(TextUtils.ExcuteScalar("select top 1 Specifications from Modules where Code = '" + moduleCodeP + "'"));
if (spec.Length > 0)
{
string[] thongSo = spec.Split('\n');
for (int k = thongSo.Length - 1; k >= 0; k--)
{
workSheet.Cells[16, 3] = thongSo[k];
((Excel.Range)workSheet.Rows[16]).Insert();
}
}
else
{
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
}
}
catch
{
}
}
else
{
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
}
workSheet.Cells[16, 2] = (i + 1);
workSheet.Cells[16, 3] = moduleNameP.ToUpper();
workSheet.Cells[16, 4] = moduleCodeP.ToUpper();
workSheet.Cells[16, 5] = hangP;
workSheet.Cells[16, 6] = "BỘ";
workSheet.Cells[16, 7] = qtyP.ToString("n0");
workSheet.Cells[16, 8] = priceP.ToString("n0");
workSheet.Cells[16, 9] = qtyP * priceP;
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Insert();
#endregion
}
else
{
decimal sumPrice = 0;
#region Add child module
for (int j = dtC.Rows.Count - 1; j >= 0; j--)
{
string moduleCode = TextUtils.ToString(dtC.Rows[j]["ModuleCode"]);
string moduleName = TextUtils.ToString(dtC.Rows[j]["ModuleName"]);
string qty = TextUtils.ToDecimal(dtC.Rows[j]["QtyT"]).ToString("n0");
string price = TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]).ToString("n0");
string hang = TextUtils.ToString(dtC.Rows[j]["Manufacture"]);
//string total = TextUtils.ToDecimal(dtC.Rows[j]["TotalTPA"]).ToString("n0");
sumPrice += TextUtils.ToDecimal(qty) * TextUtils.ToDecimal(price);
if (moduleCode.ToUpper().StartsWith("TPAD.") && moduleCode.Length == 10)
{
try
{
string spec = TextUtils.ToString(TextUtils.ExcuteScalar("select top 1 Specifications from Modules where Code = '" + moduleCode + "'"));
if (spec.Length > 0)
{
string[] thongSo = spec.Split('\n');
for (int k = thongSo.Length - 1; k >= 0; k--)
{
workSheet.Cells[16, 3] = thongSo[k];
((Excel.Range)workSheet.Rows[16]).Insert();
}
}
else
{
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
}
}
catch
{
}
}
else
{
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
}
workSheet.Cells[16, 2] = (i + 1) + "." + (j + 1);
workSheet.Cells[16, 3] = moduleName; //.ToUpper();
workSheet.Cells[16, 4] = moduleCode; //.ToUpper();
workSheet.Cells[16, 5] = hang;
workSheet.Cells[16, 6] = "";
workSheet.Cells[16, 7] = qty;
workSheet.Cells[16, 8] = price;
//workSheet.Cells[16, 9] = total;
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Insert();
}
#endregion
#region Add Parent
workSheet.Cells[16, 3] = "'* Thông số kỹ thuật chi tiết";
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Insert();
for (int j = dtC.Rows.Count - 1; j >= 0; j--)
{
string moduleName = TextUtils.ToString(dtC.Rows[j]["ModuleName"]);
string qty = TextUtils.ToDecimal(dtC.Rows[j]["QtyT"]).ToString("n0");
workSheet.Cells[16, 3] = qty + " " + moduleName;
((Excel.Range)workSheet.Rows[16]).Insert();
}
workSheet.Cells[16, 3] = "'* Danh mục thiết bị";
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
((Excel.Range)workSheet.Rows[16]).Insert();
workSheet.Cells[16, 3] = "'* Nội dung thực hành";
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Insert();
workSheet.Cells[16, 3] = "'- Xuất xứ: Việt Nam";
((Excel.Range)workSheet.Rows[16]).Insert();
workSheet.Cells[16, 3] = "'- Hãng sản xuất: TPA";
((Excel.Range)workSheet.Rows[16]).Insert();
workSheet.Cells[16, 3] = "'- Model: " + moduleCodeP;
((Excel.Range)workSheet.Rows[16]).Insert();
workSheet.Cells[16, 2] = (i + 1);
workSheet.Cells[16, 3] = moduleNameP.ToUpper();
workSheet.Cells[16, 4] = moduleCodeP.ToUpper();
workSheet.Cells[16, 5] = hangP;
workSheet.Cells[16, 6] = "BỘ";
workSheet.Cells[16, 7] = qtyP.ToString("n0");
workSheet.Cells[16, 8] = sumPrice.ToString("n0");
workSheet.Cells[16, 9] = qtyP * sumPrice;
((Excel.Range)workSheet.Rows[16]).Font.Bold = true;
((Excel.Range)workSheet.Rows[16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);// Color.Yellow;
((Excel.Range)workSheet.Rows[16]).Insert();
#endregion
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
if (File.Exists(localPath))
{
Process.Start(localPath);
}
}
}
private void BrokerOperation(FormPortfolio.OperType operation)
{
if (IsBrokerActiveSheet(this.Application.ActiveWorkbook))
{
if (this.Application.Selection != null)
{
Excel.Range cells = this.Application.Selection;
Excel.Worksheet BrokerSheet = this.Application.ActiveWorkbook.ActiveSheet;
string ticker = "";
string account = "";
string name = "";
if (cells.Row > 1)
{
ticker = BrokerSheet.Cells[cells.Row, 2].Text;
account = BrokerSheet.Cells[cells.Row, 1].Text;
name = BrokerSheet.Cells[cells.Row, 3].Text;
}
FormPortfolio dlg1 = new FormPortfolio();
dlg1.thisAddIn = this;
dlg1.ticker = ticker;
dlg1.seektxt = name;
dlg1.account = account;
dlg1.operation = operation;
if ((dlg1.ShowDialog() == DialogResult.OK) && (dlg1.count > 0))
{
// добавляем строку в excel
int max_rows = BrokerSheet.UsedRange.Rows.Count;
int max_columns = BrokerSheet.UsedRange.Columns.Count;
max_rows++;
BrokerSheet.Cells[max_rows, 1] = dlg1.account;
BrokerSheet.Cells[max_rows, 2] = dlg1.ticker;
BrokerSheet.Cells[max_rows, 3] = dlg1.name;
BrokerSheet.Cells[max_rows, 4] = dlg1.InstrumentTypeName;
switch (dlg1.operation)
{
case FormPortfolio.OperType.Buy:
BrokerSheet.Cells[max_rows, 5] = "Покупка";
BrokerSheet.Cells[max_rows, 7] = dlg1.count;
BrokerSheet.Cells[max_rows, 11] = dlg1.summa;
break;
case FormPortfolio.OperType.PlanBuy:
BrokerSheet.Cells[max_rows, 5] = "ПЛАН";
BrokerSheet.Cells[max_rows, 7] = dlg1.count;
BrokerSheet.Cells[max_rows, 11] = dlg1.summa;
break;
case FormPortfolio.OperType.Sell:
default:
BrokerSheet.Cells[max_rows, 5] = "Продажа";
BrokerSheet.Cells[max_rows, 7] = -dlg1.count;
BrokerSheet.Cells[max_rows, 11] = -dlg1.summa;
break;
}
BrokerSheet.Cells[max_rows, 6] = DateTime.Today;
if (dlg1.nominal > 0)
{
BrokerSheet.Cells[max_rows, 8] = dlg1.nominal;
}
BrokerSheet.Cells[max_rows, 9] = dlg1.currency;
BrokerSheet.Cells[max_rows, 10].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 10].FormulaR1C1;
BrokerSheet.Cells[max_rows, 13] = dlg1.price;
BrokerSheet.Cells[max_rows, 14].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 14].FormulaR1C1;
BrokerSheet.Cells[max_rows, 15].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 15].FormulaR1C1;
BrokerSheet.Cells[max_rows, 16].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 16].FormulaR1C1;
BrokerSheet.Cells[max_rows, 17].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 17].FormulaR1C1;
BrokerSheet.Cells[max_rows, 18].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 18].FormulaR1C1;
BrokerSheet.Cells[max_rows, 19].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 19].FormulaR1C1;
MSExcel.RangeBold(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]], false);
MSExcel.RangeBorder(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]]);
dlg1.Dispose();
}
}
}
}
private void Button1_Click(object sender, EventArgs e)
{
if (dgv.Rows.Count > 0) //TH có dữ liệu để ghi
{
//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 ĐIỂM ";
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1];
dcCuaHang.Font.Size = 14;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Blue;
dcCuaHang.Value = "Copyright-Cao Thi Luyên";
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1];
dtCuaHang.Font.Size = 14;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Blue;
dtCuaHang.Value = "Điện thoại: 0912403345";
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 ĐIỂM CÁC MÔN HỌC";
//Đị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 = "Mã Lớp";
exSheet.get_Range("B7").Value = "Doanh Thu";
//In dữ liệu
for (int i = 0; i < dgv.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 =
dgv.Rows[i].Cells[0].Value;
}
exSheet.Name = "Điểm thi";
exBook.Activate(); //Kích hoạt file Excel
//Thiết lập các thuộc tính của SaveFileDialog
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()); //Lưu file Excel
}
exApp.Quit(); //Thoát khỏi ứng dụng
}
else
{
MessageBox.Show("Không có danh sách hàng để in");
}
}
private modelOrganization GetOrganization(MSExcel.Worksheet ws, int row)
{
string pic = ws.Cells[row, 1].Text;
if (pic == "")
{
return(null);
}
string partnername = ws.Cells[row, 2].Text;
string acronym = ws.Cells[row, 3].Text;
string country = ws.Cells[row, 4].Text;
string representative_firstname = ws.Cells[row, 5].Text;
string representative_surname = ws.Cells[row, 6].Text;
string representative_gender = ws.Cells[row, 7].Text;
string representative_function = ws.Cells[row, 8].Text;
string legal_status = ws.Cells[row, 9].Text;
string regno = ws.Cells[row, 10].Text;
string address = ws.Cells[row, 11].Text;
string zip = ws.Cells[row, 12].Text;
string city = ws.Cells[row, 13].Text;
string vat = ws.Cells[row, 14].Text;
string email = ws.Cells[row, 15].Text;
string phone = ws.Cells[row, 16].Text;
string partner_type = ws.Cells[row, 17].Text;
string partner_description = ws.Cells[row, 18].Text;
string org_description = ws.Cells[row, 19].Text;
string activities = ws.Cells[row, 20].Text;
string keystaff = ws.Cells[row, 21].Text;
string contractnum = ws.Cells[row, 22].Text;
var organization = new modelOrganization();
if (int.TryParse(pic, out int pc))
{
organization.PIC = pc;
}
organization.LegalName = partnername;
organization.LegalNameLatinChar = "";
organization.EnglishName = "";
organization.FriendlyName = partnername;
organization.Acronym = acronym;
organization.LegalForm = legal_status;
organization.Address = address;
organization.PostCode = zip;
organization.City = city;
organization.Country = Program.GData.dataLOV.getCountries.First(c => c.Country == country).ID;
organization.Email = email;
organization.Website = "";
organization.Phone = phone;
organization.TypeOfOrganization = 1;
organization.PublicBody = false;
organization.NonProfit = false;
organization.Accreditation = "";
organization.FirstName = representative_firstname;
organization.SurName = representative_surname;
organization.Gender = representative_gender;
organization.RepresentativeFunction = representative_function;
organization.LegalStatus = legal_status;
organization.RegistrationNumber = regno;
organization.VAT = vat;
organization.GeneralDescription = partner_description;
organization.Description = org_description;
organization.Activities = activities;
organization.KeyStaff = keystaff;
organization.ContractNumber = contractnum;
return(organization);
}
private void button2_Click(object sender, RibbonControlEventArgs e)
{
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
string AktifSayfa = activeWorksheet.Name;
if (IslemSayfasi == AktifSayfa) //Doğru sayfada olup olmadığı kontrol edilir.
{ //veriler sıfırlanır.
bool Hata_Yakalama = false;
EKOCT_TOPLAM = 0;
EKCT_TOPLAM = 0;
KCT_TOPLAM = 0;
KOCT_TOPLAM = 0;
NKCT_TOPLAM = 0;
ONCT_TOPLAM = 0;
CVPSZCT_TOPLAM = 0;
ARADCT_TOPLAM = 0;
//bütün soruları işaretlemeyen cevaplayıcılar tespit edilir.
Bos_Satirlar = new ArrayList();
int Bos_Satir_Sayisi = 0;
for (int i = 1; i <= Katilimci_Sayisi; i++)
{//Bütün boş satırların sayısını ve indis numaralarını bulur.
int sayac = 0;
for (int j = 1; j <= Madde_Sayisi; j++)
{
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki == "" || siradaki == " " || siradaki == "0")
{
sayac++;
}
}
if (sayac == Madde_Sayisi)
{
Bos_Satir_Sayisi++;
Bos_Satirlar.Add(i + 1);
}
}
Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range row;
int[] rows = new int[Bos_Satirlar.Count];
int SilinenSayac = 0;
foreach (var item in Bos_Satirlar)
{//Bütün soruları işaretlemeyen cevaplayıcılar silinir.
row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) - SilinenSayac];
row.Delete();
SilinenSayac++;
}
Katilimci_Sayisi = Katilimci_Sayisi - Bos_Satir_Sayisi;
Sonuclar = new double[Katilimci_Sayisi, 9];
for (int i = 1; i <= Katilimci_Sayisi; i++)
{
Hata_Yakalama = false;
ArrayList Katilimci_Cevaplari = new ArrayList();//Her katılımcı için liste oluşturulur.
for (int j = 1; j <= Madde_Sayisi; j++)
{
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki == "" || siradaki == " ")
{
Katilimci_Cevaplari.Add(siradaki);
}
else
{
try
{//Doğru aralıkta veri girişi yapılmış ise katılımcının cevapları bir listeye eklenir.
//yanlış veri girişi yapılmış ise program hata mesajı verir.
if (Convert.ToInt32(siradaki) > Anket_Tipi || Convert.ToInt32(siradaki) < 0)
{
i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break;
}
else
{
Katilimci_Cevaplari.Add(siradaki);
}
}
catch (Exception ex) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; }
}
}
//formüllerin hesaplanması için ilgili metod çağırılır.
EKOCT_Metod(Katilimci_Cevaplari, i);
EKCT_Metod(Katilimci_Cevaplari, i);
ECT = EKOCT + EKCT;//Ekstrem Cevaplama Tarzı.
Sonuclar[(i - 1), 0] = ECT;
if (Anket_Tipi % 2 == 1)
{//Orta Noktası Bulunan Anket Türleri İçin.
KCT_Metod(Katilimci_Cevaplari, i);
KCT_TOPLAM += KCT;
KOCT_Metod(Katilimci_Cevaplari, i);
KOCT_TOPLAM += KOCT;
ONCT_Metod(Katilimci_Cevaplari, i);
ONCT_TOPLAM += ONCT;
}
NKCT = KCT - KOCT;
NKCT_TOPLAM += NKCT;
Sonuclar[(i - 1), 5] = NKCT;
CVPSZCT_Metod(Katilimci_Cevaplari, i);
CVPSZCT_TOPLAM += CVPSZCT;
ARADCT = 1 - ECT;
ARADCT_TOPLAM += ARADCT;
Sonuclar[(i - 1), 8] = ARADCT;
EKOCT_TOPLAM += EKOCT;
EKCT_TOPLAM += EKCT;
}
if (Hata_Yakalama == false)//bir hata ile karşılaşılmadı ise devam eder.
{
Excel.Worksheet ws1 = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range range = (Excel.Range)ws1.Range[ws1.Cells[2, Madde_Sayisi + 3], ws1.Cells[Katilimci_Sayisi + 1, Madde_Sayisi + 11]];
range.Value2 = Sonuclar;
//her formül için ortalama değerleri bulunur.
EKOCT_TOPLAM = EKOCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 5, EKOCT_TOPLAM.ToString("0.#####"));
EKCT_TOPLAM = EKCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 4, EKCT_TOPLAM.ToString("0.#####"));
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 3, (EKCT_TOPLAM + EKOCT_TOPLAM).ToString("0.#####"));
KCT_TOPLAM = KCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 6, KCT_TOPLAM.ToString("0.#####"));
KOCT_TOPLAM = KOCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 7, KOCT_TOPLAM.ToString("0.#####"));
NKCT_TOPLAM = NKCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 8, NKCT_TOPLAM.ToString("0.#####"));
ONCT_TOPLAM = ONCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 9, ONCT_TOPLAM.ToString("0.#####"));
CVPSZCT_TOPLAM = CVPSZCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 10, CVPSZCT_TOPLAM.ToString("0.#####"));
ARADCT_TOPLAM = ARADCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 11, ARADCT_TOPLAM.ToString("0.#####"));
//Hücreleri kilitleme ve renk ayarı.
ArrayList harfler = new ArrayList();
//kullanılmayan alanlar kullanıma kapatılır.
harfler.Add("A"); harfler.Add("B"); harfler.Add("C"); harfler.Add("D"); harfler.Add("E"); harfler.Add("F"); harfler.Add("G"); harfler.Add("H"); harfler.Add("I"); harfler.Add("J"); harfler.Add("K"); harfler.Add("L"); harfler.Add("M"); harfler.Add("N"); harfler.Add("O"); harfler.Add("P"); harfler.Add("Q"); harfler.Add("R");
harfler.Add("S"); harfler.Add("T"); harfler.Add("U"); harfler.Add("V"); harfler.Add("W"); harfler.Add("X"); harfler.Add("Y"); harfler.Add("Z"); harfler.Add("AA"); harfler.Add("AB"); harfler.Add("AC"); harfler.Add("AD"); harfler.Add("AE"); harfler.Add("AF"); harfler.Add("AG"); harfler.Add("AH"); harfler.Add("AI"); harfler.Add("AJ");
harfler.Add("AK"); harfler.Add("AL"); harfler.Add("AM"); harfler.Add("AN"); harfler.Add("AO"); harfler.Add("AP"); harfler.Add("AQ"); harfler.Add("AR"); harfler.Add("AS"); harfler.Add("AT"); harfler.Add("AU"); harfler.Add("AV"); harfler.Add("AW"); harfler.Add("AX"); harfler.Add("AY"); harfler.Add("AZ"); harfler.Add("BA"); harfler.Add("BB");
harfler.Add("BC"); harfler.Add("BD"); harfler.Add("BE"); harfler.Add("BF"); harfler.Add("BG"); harfler.Add("BH"); harfler.Add("BI"); harfler.Add("BJ"); harfler.Add("BK"); harfler.Add("BL"); harfler.Add("BM"); harfler.Add("BN"); harfler.Add("BO"); harfler.Add("BP"); harfler.Add("BQ"); harfler.Add("BR"); harfler.Add("BS"); harfler.Add("BT");
string sutun = harfler[Madde_Sayisi + 10].ToString() + (Katilimci_Sayisi + 2).ToString();
Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range fr;
fr = ws.get_Range("A1", sutun);
fr.Borders.Weight = 2;
fr.Borders.Color = Color.Black;
fr.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
Globals.ThisAddIn.Application.Cells.Locked = true;
Globals.ThisAddIn.Application.get_Range("A1", sutun).Locked = false;
Excel.Worksheet Sheet1 = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Sheet1.Protect();
}
}
else
{
MessageBox.Show("Hesaplama İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void MissingTemizlemeMetodu()
{
MTemizlemeFormu.progressBar1.Maximum = Convert.ToInt32(Katilimci_Sayisi) * Convert.ToInt32(Madde_Sayisi); //progressbar ın maksimum değerini atar.
if (Convert.ToInt32(Secilen_Liste.Count.ToString()) > 0) //Veri temizleme yapmak için en az bir tane soru seçilmesi gerekir.
{
MTemizlemeFormu.progressBar1.Value = 0; //işlem sürecini gösteren progressbar sıfırlanır.
MTemizlemeFormu.Cursor = Cursors.No;
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
string AktifSayfa = activeWorksheet.Name; //aktif sayfanın adına ulaşır.
if (IslemSayfasi == AktifSayfa) //aktif sayfa işlem sayfası ise yani verilerin girildiği sayfa açık ise veri temizleme işlemine devam eder.
{
SilinecekIndisler = new ArrayList();
for (int i = 1; i <= Katilimci_Sayisi; i++)
{ //Her satır için.
for (int j = 1; j <= Madde_Sayisi; j++) //Her Sütun için.
{ //Her hücredeki değer için tek tek bakar ve seçilen listede varsa indis numarasını tutar.
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki == "0" || siradaki == "")
{
int kolon = j, flag = 0;
foreach (var item in Secilen_Liste)
{
if (kolon.ToString() == item.ToString())
{
flag++;
}
}
if (flag > 0)
{
SilinecekIndisler.Add(i); break;
}
}
try
{
MTemizlemeFormu.progressBar1.Value++;
}
catch { }
}
}
MTemizlemeFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi;//işlem sürecini gösteren progressbarı günceller.
int[,] sil = new int[Convert.ToInt32(SilinecekIndisler.Count), Madde_Sayisi];
for (int i = 0; i < SilinecekIndisler.Count; i++)
{
for (int j = 0; j < Madde_Sayisi; j++)
{//silinecek verileri bir dizide tutar.
sil[i, j] = Convert.ToInt32(Hucrelerden_Okuma_Metodu((Convert.ToInt32(SilinecekIndisler[i]) + 1), (j + 2)));
try
{
MTemizlemeFormu.progressBar1.Value++;
}
catch { }
}
}
MTemizlemeFormu.progressBar1.Maximum += SilinecekIndisler.Count; //işlem sürecini gösteren progressbarı günceller.
Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet; //aktif sayfaya ulaşır.
Excel.Range row;
int[] rows = new int[SilinecekIndisler.Count];
int SilinenSayac = 0;//bu değişkenin amacı excelde bir satır silinince yerine bir alttaki satır geldiği için listedeki silinecek satırdan
//daha önce silinenlerin sayısını çıkartmamız gerekir.
foreach (var item in SilinecekIndisler)
{//silinecek indisleri bulup siler.
row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) + 1 - SilinenSayac];
row.Delete();
SilinenSayac++;
try
{
MTemizlemeFormu.progressBar1.Value++;
}
catch { }
}
if (SilinenSayac > 0) //devam etmesi için en az 1 tane veri silinmesi gerekir.
{
Katilimci_Sayisi = Katilimci_Sayisi - SilinecekIndisler.Count; //katılımcı sayısı silinenler olduğu için güncellenir.
Excel.Workbook wsTemizlenenler = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet x = wsTemizlenenler.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
x.Name = YeniTemizlenenlerSayfasiOlustur();//yeni bir temizlenenler sayfası oluşturulur.
for (int i = 0; i < SilinecekIndisler.Count; i++)
{
for (int j = 0; j < Madde_Sayisi; j++)
{//yeni temizlenenler sayfasına silinecek veriler taşınır.
x.Cells[i + 1, j + 1] = sil[i, j].ToString();
try
{
MTemizlemeFormu.progressBar1.Value++;
}
catch { }
}
}
if (MTemizlemeFormu.progressBar1.Value < MTemizlemeFormu.progressBar1.Maximum)
{
for (int i = MTemizlemeFormu.progressBar1.Value; i < MTemizlemeFormu.progressBar1.Maximum; i++)
{
MTemizlemeFormu.progressBar1.Value++;
}
}
MTemizlemeFormu.Cursor = Cursors.Default;
//Bilgi mesajı verir.
MessageBox.Show(SilinecekIndisler.Count.ToString() + " Veri " + x.Name.ToString() + " Sayfasına Taşındı.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{//Hiç veri silinmedi ise bilgi verir.
MTemizlemeFormu.Cursor = Cursors.Default;
MessageBox.Show("Silinecek Hiç Veri Bulunamadı.");
if (MTemizlemeFormu.progressBar1.Value < MTemizlemeFormu.progressBar1.Maximum)
{
for (int i = MTemizlemeFormu.progressBar1.Value; i < MTemizlemeFormu.progressBar1.Maximum; i++)
{
MTemizlemeFormu.progressBar1.Value++;
}
}
}
}
else
{
MessageBox.Show("Veri Temizleme İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Madde Listesinden Seçim Yapınız.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// The sourcesheet and the destsheet need to be in the same workbook
/// </summary>
/// <param name="sourceSheet"></param>
/// <param name="cellBegin"></param>
/// <param name="cellEnd"></param>
/// <param name="destSheet"></param>
/// <param name="positionInsert"></param>
public static void InsertRange(Excel.Worksheet sourceSheet, string cellBegin, string cellEnd, Excel.Worksheet destSheet, string positionInsert)
{
Excel.Range destRange = destSheet.Range[positionInsert];
Excel.Range sourceRange = sourceSheet.Range[cellBegin, cellEnd];
sourceRange.EntireColumn.Select();
destRange.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
Excel.Range destRange1 = destSheet.Range[positionInsert];
sourceRange.Copy(destRange1);
ReleaseComObject(destRange);
ReleaseComObject(sourceRange);
ReleaseComObject(destRange1);
}
private void montaCabeceras(int fila, ref Excel.Worksheet hoja, string nombreCabecera)
{
try
{
Excel.Range rango;
int cantColumnas = tablaProcesadoresGeneracion.Rows.Count + 2;
//** Montamos el título en la línea 1 **
hoja.Cells[fila, 1] = nombreCabecera;
hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Merge();
hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Interior.Color = Color.FromArgb(196, 89, 19);//FromArgb(255, 132, 0)
hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Style.Font.Bold = true;
hoja.Range[hoja.Cells[fila + 1, 1], hoja.Cells[fila + 1, cantColumnas - 1]].Interior.Color = Color.FromArgb(196, 89, 19);
hoja.Range[hoja.Cells[fila + 1, cantColumnas], hoja.Cells[fila + 1, cantColumnas]].Interior.Color = Color.FromArgb(130, 61, 10);
hoja.Range[hoja.Cells[fila + 2, 1], hoja.Cells[fila + 2, cantColumnas]].Interior.Color = Color.FromArgb(252, 228, 215);
hoja.Range[hoja.Cells[fila + 3 + cantModeloProcesador, 1], hoja.Cells[fila + 3 + cantModeloProcesador, cantColumnas]].Interior.Color = Color.FromArgb(252, 228, 215);
//Centramos los textos
rango = hoja.Rows[fila];
rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int k = 0; k < cantGeneraciones; k++)
{
string genPro = tablaProcesadoresGeneracion.Rows[k]["descripcion"].ToString();
hoja.Cells[fila + 1, k + 2] = genPro;
}
hoja.Cells[fila + 1, cantGeneraciones + 2] = "Total en Almacen";
hoja.Cells[fila + 1, 1] = "Generación";
hoja.Cells[fila + 2, 1] = "LAPTOP";
for (int k = 0; k < cantModeloProcesador; k++)
{
string modPro = tablaProcesadoresModelos.Rows[k]["nombre"].ToString();
hoja.Cells[fila + 3 + k, 1] = modPro;
}
hoja.Cells[fila + 3 + cantModeloProcesador, 1] = "MACKBOOK";
for (int k = 0; k < cantModeloProcesador; k++)
{
string modPro = tablaProcesadoresModelos.Rows[k]["nombre"].ToString();
hoja.Cells[fila + 3 + cantModeloProcesador + 1 + k, 1] = modPro;
}
int fila2 = fila + 2;
rango = hoja.Rows[fila2];
rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int j = 0; j < cantColumnas + 2; j++)
{
rango = hoja.Columns[j + 1];
rango.ColumnWidth = 15;
}
rango = hoja.Rows[fila + 3 + cantModeloProcesador];
rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int j = 0; j < cantColumnas + 2; j++)
{
rango = hoja.Columns[j + 1];
rango.ColumnWidth = 15;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error de redondeo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btRecorder_Click(object sender, EventArgs e) //记录数据
{
#region 获取网站的中的数据，并存到 List 中
webBrowser1.Url = new System.Uri("http://219.140.196.71/sq/data/sc.action?scid=cjh.sq"); //刷新网站
string strText = webBrowser1.Document.Body.InnerText;
if (strText == null)
{
return;
}
int indexFront = strText.IndexOf("寸滩");
//int indexAfter = strText.IndexOf("防汛测报");
string text = null;
if (indexFront > 0)
{
text = strText.Substring(indexFront); //获取有用的部分
}
else
{
return;
}
string[] strLines = new string[20];
strLines = text.Split(new char[] { '\n' }); //将内容按回车分割成数组
List <Info> content = new List <Info>(); //获取数据的每一个部分
for (int i = 0; i < strLines.Length; i++)
{
string[] parts = new string[6]; //一般是六部分
if (strLines[i].Length < 6)
{
break;
}
parts = strLines[i].Split(new char[] { ' ' }); //分成三部分：名称+日期+数据
Info inner = new Info();
//名称内容
inner.name = parts[0];
if (inner.name == "茅坪(二)" || inner.name == "龙王庙")
{
continue; //这两个不加入,直接跳过!
}
//日期内容
inner.date = parts[1] + parts[2] + parts[3];
//水位数据，基本都有
inner.water = parts[4];
//流量数据，有时候没有为“-”，有时候存在负数,判断存在“-”，并且并且“-”后面没有内容时候为空值
if (!parts[5].Contains('-'))
{
inner.stream = parts[5];
}
else
{
inner.stream = "0";
}
if (inner.stream.Contains('(')) //包括(入) 和 (出)
{
inner.stream = inner.stream + strLines[i + 1]; //将下一行的(出)也加进来,然后i自加!
i++;
}
else if (inner.name.Contains('-'))
{
inner.stream = "0";
}
content.Add(inner);
}
#endregion
int countDay = 0;
int countNight = 0;
foreach (Info info in content)
{
if (info.date.Contains("8时")) //获取8时的数据
{
countDay++;
}
else if (info.date.Contains("20时"))
{
countNight++;
}
}
#region 定义 lunarMonth 和 lunarDay
Hashtable lunarMonth = new Hashtable();
lunarMonth.Add(1, "正");
lunarMonth.Add(2, "二");
lunarMonth.Add(3, "三");
lunarMonth.Add(4, "四");
lunarMonth.Add(5, "五");
lunarMonth.Add(6, "六");
lunarMonth.Add(7, "七");
lunarMonth.Add(8, "八");
lunarMonth.Add(9, "九");
lunarMonth.Add(10, "十");
lunarMonth.Add(11, "十一");
lunarMonth.Add(12, "十二");
Hashtable lunarDay = new Hashtable();
lunarDay.Add(1, "初一");
lunarDay.Add(2, "初二");
lunarDay.Add(3, "初三");
lunarDay.Add(4, "初四");
lunarDay.Add(5, "初五");
lunarDay.Add(6, "初六");
lunarDay.Add(7, "初七");
lunarDay.Add(8, "初八");
lunarDay.Add(9, "初九");
lunarDay.Add(10, "初十");
lunarDay.Add(11, "十一");
lunarDay.Add(12, "十二");
lunarDay.Add(13, "十三");
lunarDay.Add(14, "十四");
lunarDay.Add(15, "十五");
lunarDay.Add(16, "十六");
lunarDay.Add(17, "十七");
lunarDay.Add(18, "十八");
lunarDay.Add(19, "十九");
lunarDay.Add(20, "二十");
lunarDay.Add(21, "二十一");
lunarDay.Add(22, "二十二");
lunarDay.Add(23, "二十三");
lunarDay.Add(24, "二十四");
lunarDay.Add(25, "二十五");
lunarDay.Add(26, "二十六");
lunarDay.Add(27, "二十七");
lunarDay.Add(28, "二十八");
lunarDay.Add(29, "二十九");
lunarDay.Add(30, "三十");
#endregion
if (countDay == 11) //countDay == 11
{
#region 8时
string excelPath = saveFilePath;
eWorkbook = ex.Workbooks.Open(excelPath); //打开文件，赋值到工作簿
ex.Visible = false; //程序显示
eWorksheet = (Excel.Worksheet)eWorkbook.Sheets[1]; //获取第一个工作表
//新格式
string date = DateTime.Today.Month + "月" + DateTime.Today.Day + "日";
ChineseLunisolarCalendar lunar = new ChineseLunisolarCalendar();
string dateLunar = lunarMonth[GetMonthFromYear(DateTime.Today.Date)] + "月" +
lunarDay[lunar.GetDayOfMonth(DateTime.Today.Date)];
int row = ex.Application.get_Range("B65535", Type.Missing).get_End(Excel.XlDirection.xlUp).Row;
eWorksheet.get_Range("A" + (row + 1).ToString()).Value = date;
eWorksheet.get_Range("B" + (row + 1).ToString()).Value = DateTime.Now.Hour + "时";
eWorksheet.get_Range("C" + (row + 1).ToString()).Value = dateLunar;
eWorksheet.get_Range("D" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].water);
if (content[0].stream != "0")
{
eWorksheet.get_Range("E" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].stream);
}
else
{
eWorksheet.get_Range("E" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("F" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].water);
if (content[1].stream != "0")
{
eWorksheet.get_Range("G" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].stream);
}
else
{
eWorksheet.get_Range("G" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("H" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].water);
if (content[2].stream != "0")
{
eWorksheet.get_Range("I" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].stream);
}
else
{
eWorksheet.get_Range("I" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("J" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].water);
if (content[3].stream != "0")
{
eWorksheet.get_Range("K" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].stream);
}
else
{
eWorksheet.get_Range("K" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("L" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].water);
if (content[4].stream != "0")
{
eWorksheet.get_Range("M" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].stream);
}
else
{
eWorksheet.get_Range("M" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("N" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].water);
if (content[5].stream != "0")
{
eWorksheet.get_Range("O" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].stream);
}
else
{
eWorksheet.get_Range("O" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("P" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].water);
if (content[6].stream != "0")
{
eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].stream);
}
else
{
eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("R" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].water);
if (content[7].stream != "0")
{
eWorksheet.get_Range("S" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].stream);
}
else
{
eWorksheet.get_Range("S" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("T" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].water);
if (content[8].stream != "0")
{
eWorksheet.get_Range("U" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].stream);
}
else
{
eWorksheet.get_Range("U" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("V" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].water);
if (content[9].stream.Contains('('))
{
int inFront = content[9].stream.IndexOf('(');
int inBack = content[9].stream.IndexOf(')');
int outFront = content[9].stream.LastIndexOf('(');
string streamIn = content[9].stream.Substring(0, inFront);
string streamOut = content[9].stream.Substring(inBack + 1, outFront - inBack - 1);
eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut);
eWorksheet.get_Range("X" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn);
}
else
{
eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].stream);
}
eWorksheet.get_Range("Y" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].water);
if (content[10].stream.Contains('('))
{
int inFront = content[10].stream.IndexOf('(');
int inBack = content[10].stream.IndexOf(')');
int outFront = content[10].stream.LastIndexOf('(');
string streamIn = content[10].stream.Substring(0, inFront);
string streamOut = content[10].stream.Substring(inBack + 1, outFront - inBack - 1);
eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut);
eWorksheet.get_Range("AA" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn);
}
else
{
eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].stream);
}
eWorkbook.Save();
ex.Quit();
this.Focus();
timer2.Enabled = false;
lbTimer2.Text = "计时器2关闭！";
timer1.Enabled = true;
lbTimer1.Text = "计时器1开启！";
label4.Text = DateTime.Now.ToShortDateString() + " 8时 记录完毕！";
countRecorderDay++;
#endregion
}
if (countNight == 11)
{
#region 20时
string excelPath = saveFilePath;
eWorkbook = ex.Workbooks.Open(excelPath); //打开文件，赋值到工作簿
ex.Visible = false; //程序显示
eWorksheet = (Excel.Worksheet)eWorkbook.Sheets[1]; //获取第一个工作表
//新格式
string date = DateTime.Today.Month + "月" + DateTime.Today.Day + "日";
ChineseLunisolarCalendar lunar = new ChineseLunisolarCalendar();
string dateLunar = lunarMonth[GetMonthFromYear(DateTime.Today.Date)] + "月" +
lunarDay[lunar.GetDayOfMonth(DateTime.Today.Date)];
int row = ex.Application.get_Range("B65535", Type.Missing).get_End(Excel.XlDirection.xlUp).Row;
//eWorksheet.get_Range("A" + (row + 1).ToString()).Value = date;
eWorksheet.get_Range("B" + (row + 1).ToString()).Value = DateTime.Now.Hour + "时";
//eWorksheet.get_Range("C" + (row + 1).ToString()).Value = dateLunar;
eWorksheet.get_Range("D" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].water);
if (content[0].stream != "0")
{
eWorksheet.get_Range("E" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].stream);
}
else
{
eWorksheet.get_Range("E" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("F" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].water);
if (content[1].stream != "0")
{
eWorksheet.get_Range("G" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].stream);
}
else
{
eWorksheet.get_Range("G" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("H" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].water);
if (content[2].stream != "0")
{
eWorksheet.get_Range("I" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].stream);
}
else
{
eWorksheet.get_Range("I" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("J" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].water);
if (content[3].stream != "0")
{
eWorksheet.get_Range("K" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].stream);
}
else
{
eWorksheet.get_Range("K" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("L" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].water);
if (content[4].stream != "0")
{
eWorksheet.get_Range("M" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].stream);
}
else
{
eWorksheet.get_Range("M" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("N" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].water);
if (content[5].stream != "0")
{
eWorksheet.get_Range("O" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].stream);
}
else
{
eWorksheet.get_Range("O" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("P" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].water);
if (content[6].stream != "0")
{
eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].stream);
}
else
{
eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("R" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].water);
if (content[7].stream != "0")
{
eWorksheet.get_Range("S" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].stream);
}
else
{
eWorksheet.get_Range("S" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("T" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].water);
if (content[8].stream != "0")
{
eWorksheet.get_Range("U" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].stream);
}
else
{
eWorksheet.get_Range("U" + (row + 1).ToString()).Value = "";
}
eWorksheet.get_Range("V" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].water);
if (content[9].stream.Contains('('))
{
int inFront = content[9].stream.IndexOf('(');
int inBack = content[9].stream.IndexOf(')');
int outFront = content[9].stream.LastIndexOf('(');
string streamIn = content[9].stream.Substring(0, inFront);
string streamOut = content[9].stream.Substring(inBack + 1, outFront - inBack - 1);
eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut);
eWorksheet.get_Range("X" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn);
}
else
{
eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].stream);
}
eWorksheet.get_Range("Y" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].water);
if (content[10].stream.Contains('('))
{
int inFront = content[10].stream.IndexOf('(');
int inBack = content[10].stream.IndexOf(')');
int outFront = content[10].stream.LastIndexOf('(');
string streamIn = content[10].stream.Substring(0, inFront);
string streamOut = content[10].stream.Substring(inBack + 1, outFront - inBack - 1);
eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut);
eWorksheet.get_Range("AA" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn);
}
else
{
eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].stream);
}
eWorkbook.Save();
ex.Quit();
this.Focus();
//Application.Exit();
timer2.Enabled = false;
lbTimer2.Text = "计时器2关闭！";
timer1.Enabled = true;
lbTimer1.Text = "计时器1开启！";
label4.Text = DateTime.Now.ToShortDateString() + " 20时 记录完毕！";
countRecorderNight++;
#endregion
}
}
/// <summary>
/// The sourcesheet and the destsheet need to be in the same workbook
/// </summary>
/// <param name="sourceSheet"></param>
/// <param name="cellBegin"></param>
/// <param name="cellEnd"></param>
/// <param name="destSheet"></param>
/// <param name="positionInsert"></param>
public static void CopyRange(Excel.Worksheet sourceSheet, string cellBegin, string cellEnd, Excel.Worksheet destSheet, string positionInsert)
{
Excel.Range destRange = destSheet.Range[positionInsert];
Excel.Range sourceRange = sourceSheet.Range[cellBegin, cellEnd];
sourceRange.Select();
sourceRange.Copy(destRange);
ReleaseComObject(destRange);
ReleaseComObject(sourceRange);
}
/// <summary>
/// ！！！！！！！！！！！！！！！！！！！
/// 注意：该函数存在问题！未进行完善
/// 写入excel数据
/// </summary>
/// <param name="ent">预警规则实体</param>
/// <param name="selectedRowID">选择行编号</param>
/// <returns>是否插入成功?true:false</returns>
public static bool updateInfoToExcelSheet(PreWarningRules ent, int
selectedRowID, string sheetName)
{
bool ret = false;
Excel.Application objExcelApp = null;
Excel.Workbooks objExcelWorkBooks = null;
Excel.Workbook objExcelWorkBook = null;
Excel.Worksheet objExcelWorkSheet = null;
try
{
objExcelApp = new Excel.ApplicationClass();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkBook = objExcelWorkBooks.Open(_excelFilePath, 0,
false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
objExcelWorkSheet =
(Worksheet)objExcelWorkBook.Worksheets[sheetName];
objExcelWorkSheet.Select(Type.Missing);
Worksheet objExcelWorkSheetTemp =
(Worksheet)objExcelApp.ActiveSheet;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 1] =
ent.RuleCode;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 2] =
ent.RuleType;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 3] =
ent.WarningType;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 4] =
ent.WarningLevel;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 5] =
ent.SuitableLocation;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 6] =
ent.RuleDescription;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 7] =
ent.IndicatorType;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 8] =
ent.Operator;
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 9] =
ent.ModifyDate.ToString();
objExcelWorkSheetTemp.Cells[selectedRowID + 1, 10] =
ent.Remarks;//MARK FIELD
//objExcelApp.Visible = true;
objExcelApp.DisplayAlerts = false;
objExcelApp.SaveWorkspace();
ret = true;
}
catch (Exception ex)
{
Alert.alert(ex.Message);
ret = false;
}
finally
{
if (objExcelApp != null)
{
objExcelApp.Quit();
GC.Collect();//不加该行代码Excel不会退出！
}
}
return(ret);
}
public CodeReviewSheet(ExcelInterop.Worksheet sheet) : base(sheet)
{
this.sheet = sheet;
}
public void ExportarDataGridViewExcel(ref Excel.Worksheet hoja_trabajo, string nombreCabecera)
{
Excel.Range rango;
int cantColumnas = tablaProcesadoresGeneracion.Rows.Count + 3;
int filas = vista.RowCount;
int k = cantColumnas - 1 + 64;
char columF = (char)k;
int fila2 = 3;
string filaBorde = fila2.ToString();
char columI = 'A';
//Ponemos borde a las celdas
rango = hoja_trabajo.Range[columI + fila2.ToString(), columF + fila2.ToString()];
rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
rango.Style.Font.Bold = false;
for (int i = 1; i <= this.cantModeloProcesador * 2 + 4; i++)
{
rango = hoja_trabajo.Range[columI + i.ToString(), columF + i.ToString()];
rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
for (int i = 0; i < this.cantGeneraciones; i++)
{
int cantGenLCGeneral = 0;
int cantGenLCApple = 0;
for (int j = 0; j < this.cantModeloProcesador; j++)
{
string generacion = tablaProcesadoresGeneracion.Rows[i]["descripcion"].ToString();
string nombreProcesador = tablaProcesadoresModelos.Rows[j]["nombre"].ToString();
hoja_trabajo.Cells[fila2 + 1 + j, i + 2] = arregloLCGeneral[i][j];
hoja_trabajo.Cells[fila2 + 1 + cantModeloProcesador + 1 + j, i + 2] = arregloLCApple[i][j];
cantGenLCGeneral += arregloLCGeneral[i][j];
cantGenLCApple += arregloLCApple[i][j];
}
hoja_trabajo.Cells[fila2, i + 2] = cantGenLCGeneral;
hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1, i + 2] = cantGenLCApple;
}
for (int j = 0; j < this.cantModeloProcesador; j++)
{
int cantProLCGeneral = 0;
int cantProLCApple = 0;
for (int i = 0; i < this.cantGeneraciones; i++)
{
cantProLCGeneral += arregloLCGeneral[i][j];
cantProLCApple += arregloLCApple[i][j];
}
hoja_trabajo.Cells[fila2 + 1 + j, cantGeneraciones + 2] = cantProLCGeneral;
hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1 + 1 + j, cantGeneraciones + 2] = cantProLCApple;
}
var cantidadLCGeneral = new BindingList <LC>(laptops.Where(p => p.IdMarca != this.idMarcaAppleLC && p.IdMarca != this.idMarcaApplePC && p.Estado == this.estadoDisponible).ToList());
var cantidadLCApple = new BindingList <LC>(laptops.Where(p => p.IdMarca == this.idMarcaAppleLC && p.IdMarca != this.idMarcaApplePC && p.Estado == this.estadoDisponible).ToList());
hoja_trabajo.Cells[fila2, cantGeneraciones + 2] = cantidadLCGeneral.Count;
hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1, cantGeneraciones + 2] = cantidadLCApple.Count;
montaCabeceras(1, ref hoja_trabajo, nombreCabecera);
}
private void btnExeclReport_Click(object sender, EventArgs e)
{
string path = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
path = fbd.SelectedPath;
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKeToan\\BangTheoDoiChungTu.xlsx";
string currentPath = path + "\\BangTheoDoiChungTu.xlsx";
try
{
File.Copy(filePath, currentPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi tạo báo cáo!" + Environment.NewLine + ex.Message,
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo cáo..."))
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
try
{
app = new Excel.Application();
app.Workbooks.Open(currentPath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
for (int i = grvData.RowCount - 1; i >= 0; i--)
{
string number = TextUtils.ToString(grvData.GetRowCellValue(i, colNumber));
workSheet.Cells[6, 1] = i + 1;
workSheet.Cells[6, 2] = number;
workSheet.Cells[6, 3] = TextUtils.ToString(grvData.GetRowCellValue(i, colItemCode));
workSheet.Cells[6, 4] = TextUtils.ToString(grvData.GetRowCellValue(i, colItemName));
workSheet.Cells[6, 5] = TextUtils.ToString(grvData.GetRowCellValue(i, colVoucherName));
workSheet.Cells[6, 6] = TextUtils.ToString(grvData.GetRowCellValue(i, colUserName));
workSheet.Cells[6, 7] = TextUtils.ToString(grvData.GetRowCellDisplayText(i, colCreatedDate));
workSheet.Cells[6, 8] = TextUtils.ToString(grvData.GetRowCellDisplayText(i, colCompletedDateDK));
if (number != "")
{
((Excel.Range)workSheet.Rows[6]).Insert();
}
}
((Excel.Range)workSheet.Rows[5]).Delete();
((Excel.Range)workSheet.Rows[5]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(currentPath);
}
}
public static void GenerateInfoMapByName(Excel.Workbook book)
{
int rowIndex = 1;
int sheetIndex = 1;
Excel.Worksheet sheet = book.Sheets[sheetIndex];
while ((!sheet.Name.Contains("资料")) && (sheetIndex++ < book.Sheets.Count))
{
sheet = book.Sheets[sheetIndex];
}
int nameColumn = 0;
int companyColumn = 0;
int managerColumn = 0;
int costProjColumn = 0;
int bizProjColumn = 0;
int workTypeColumn = 0;
int rankColumn = 0;
int departmentColumn = 0;
int enterDateColumn = 0;
int leaveDateColumn = 0;
#region locate column index
for (int i = 1; i < 20; i++)
{
string cellValue = sheet.Cells[1, i].Value != null ? sheet.Cells[1, i].Value.ToString() : string.Empty;
if (cellValue == "姓名")
{
nameColumn = i;
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("供应商名称"))
{
companyColumn = i;
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("项目经理"))
{
managerColumn = i;
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("人力成本归集"))
{
costProjColumn = i;
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("参与项目"))
{
bizProjColumn = i;
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("外包形式"))
{
workTypeColumn = i;
}
else if (cellValue == "职级")
{
rankColumn = i;
}
else if (cellValue == "所属中心")
{
departmentColumn = i;
}
else if (cellValue == "入场时间")
{
enterDateColumn = i;
}
else if (cellValue == "离场时间")
{
leaveDateColumn = i;
}
} // for (int i = 1; i < 20; i++)
#endregion locate column index
//
// get cell values
List <PersonInfo> personList = new List <PersonInfo>();
for (rowIndex = 1; rowIndex < 5000; rowIndex++)
{
PersonInfo info = new PersonInfo();
// name
info.Name = Convert.ToString(sheet.Cells[rowIndex, nameColumn].Value);
if (string.IsNullOrWhiteSpace(info.Name))
{
continue;
}
// company
info.Company = Convert.ToString(sheet.Cells[rowIndex, companyColumn].Value);
// manager
info.Manager = Convert.ToString(sheet.Cells[rowIndex, managerColumn].Value);
// cost project
info.CostProject = Convert.ToString(sheet.Cells[rowIndex, costProjColumn].Value);
// business project
info.BizProject = Convert.ToString(sheet.Cells[rowIndex, bizProjColumn].Value);
// work type
info.WorkType = Convert.ToString(sheet.Cells[rowIndex, workTypeColumn].Value);
// Rank - 初、中、高、专家
info.Rank = Convert.ToString(sheet.Cells[rowIndex, rankColumn].Value);
// department
info.Department = Convert.ToString(sheet.Cells[rowIndex, departmentColumn].Value);
// enter date
info.OnboardDate = Convert.ToString(sheet.Cells[rowIndex, enterDateColumn].Value); // != null ? sheet.Cells[rowIndex, 10].Value.ToString() : null;
// leave date
info.DimissionDate = Convert.ToString(sheet.Cells[rowIndex, leaveDateColumn].Value); // != null ? sheet.Cells[rowIndex, 11].Value.ToString() : null;
personList.Add(info);
} // for (rowIndex = 1; rowIndex < 5000; rowIndex++)
//
// sort on onboard date by ascend
personList.Sort(
(x, y) =>
{
if (string.IsNullOrWhiteSpace(x.OnboardDate))
{
return(-1); // x - y < 0, put x ahead
}
return(x.OnboardDate.CompareTo(y.OnboardDate));
}
);
//
// map by name, exclude duplicate records. the latest one will take effect
personList.ForEach(delegate(PersonInfo info)
{
lock (InfoMap)
{
InfoMap[info.Name] = info;
}
});
// correct data and set owner system
//CorrectData(InfoMap.Values);
}
public void UpdateSEPValidationCheckList(string adjustedDataSheetName)
{
try
{
Globals.ThisAddIn.lstSEPValidationValues = new List <SEPValidationValues>();
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[adjustedDataSheetName]).Select(Type.Missing);
Excel.Worksheet adjustedDataModelSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Excel.ListObject LO = (Excel.ListObject)adjustedDataModelSheet.ListObjects.get_Item(2);
for (int x = 1; x <= LO.ListColumns.Count; x++)
{
string col = LO.ListColumns[x].Name;
if (!col.StartsWith("Col")) //Need to remove this hack
{
object[,] row1 = new object[1, 6];
int y = 0;
foreach (Excel.ListRow row in LO.ListRows)
{
row1[0, y] = ((Excel.Range)row.Range.Cells[x]).Value2.ToString();
y++;
}
SEPValidationValues newVal = new SEPValidationValues(col, x, 0, Convert.ToDouble(row1[0, 2]),
Convert.ToDouble(row1[0, 1]),
Convert.ToDouble(row1[0, 0]),
Convert.ToDouble(row1[0, 3]),
Convert.ToDouble(row1[0, 4]),
Convert.ToDouble(row1[0, 5]), string.Empty, false);
Globals.ThisAddIn.lstSEPValidationValues.Add(newVal);
}
}
foreach (SEPValidationValues sepVals in Globals.ThisAddIn.lstSEPValidationValues)
{
int count = 0;
foreach (KeyValuePair <string, string> itm in Globals.ThisAddIn.SelectedSourcesBestModelFormulas)
{
if (itm.Value.Contains(sepVals.IndependentVariable))
{
count++;
}
}
if (count > 0)
{
if ((sepVals.MinModel < sepVals.AvgReportYr && sepVals.AvgReportYr < sepVals.MaxModel) || (sepVals.Minus3DevVal < sepVals.AvgReportYr && sepVals.AvgReportYr < sepVals.Plus3DevVal))
{
sepVals.SEPValidationCheck = "Pass";
}
else
{
sepVals.SEPValidationCheck = "Fail";
}
}
else
{
sepVals.SEPValidationCheck = "Not Included In Model";
}
}
}
catch
{
//TODO: Log Implementation
}
}
public static void Ler(Excel.Worksheet wsPlanilha, string tipoGUID)
{
Repository <LV_PLANILHA> repositoryPLanilha = new Repository <LV_PLANILHA>();
string cell = getColuna(3, 1);
var funcao = wsPlanilha.get_Range(cell, cell).Text;
cell = getColuna(4, 1);
var descricaoPlanilha = wsPlanilha.get_Range(cell, cell).Text;
LV_PLANILHA planilha = new LV_PLANILHA()
{
GUID = Guid.NewGuid().ToString(),
GUID_TIPO = tipoGUID,
NOME = wsPlanilha.Name,
FUNCAO = funcao,
DESCRICAO = descricaoPlanilha
};
//if(planilha.NOME.Equals("Prédios - BAS CONSOLIDADO"))
//{
//string pare = "Prédios - BAS CONSOLIDADO";
//}
repositoryPLanilha.Insert(planilha);
Repository <LV_GRUPO> repositoryGrupo = new Repository <LV_GRUPO>();
LV_GRUPO grupo = null;
int ordenarGrupoItem = 0;
int ordenarItemItem = 0;
//for (int colIndex = 1; colIndex < 5; colIndex++)
//{
int colIndex = 1;
for (int rowIndex = 7; rowIndex < 80; rowIndex++)
{
cell = getColuna(rowIndex, colIndex);
string texto = wsPlanilha.get_Range(cell, cell).Text;
if (texto != "")
{
int ordenadorGrupo = 0;
if (int.TryParse(texto, out ordenadorGrupo))
{
if (grupo == null || grupo.ORDENADOR < ordenadorGrupo)
{
cell = getColuna(rowIndex, colIndex + 1);
string nomeGrupo = wsPlanilha.get_Range(cell, cell).Text;
grupo = new LV_GRUPO()
{
GUID = Guid.NewGuid().ToString(),
ORDENADOR = ordenadorGrupo,
GUID_PLANILHA = planilha.GUID,
NOME = nomeGrupo
};
repositoryGrupo.Insert(grupo);
}
}
else if (int.TryParse(texto.Split('.')[0], out ordenarGrupoItem) && int.TryParse(texto.Split('.')[1], out ordenarItemItem))
{
if (grupo.ORDENADOR.Equals(ordenarGrupoItem))
{
cell = getColuna(rowIndex, colIndex + 1);
string descricaoRevisao = wsPlanilha.get_Range(cell, cell).Text;
if (descricaoRevisao != "")
{
Repository <LV_ITEM_REVISAO> repositoryItemRevisao = new Repository <LV_ITEM_REVISAO>();
LV_ITEM_REVISAO itemRevisao = new LV_ITEM_REVISAO()
{
GUID = Guid.NewGuid().ToString(),
GUID_GRUPO = grupo.GUID,
DESCRICAO = descricaoRevisao,
ORDENADOR = ordenarItemItem
};
repositoryItemRevisao.Insert(itemRevisao);
}
}
}
}
}
//}
}
public void KontrolSorusuMetodu()
{ //Bu metodun amacı kullanıcının seçtiği maddeleri işaretleyen kullanıcıları temizlemek.
KSoruFormu.progressBar1.Maximum = Convert.ToInt32(Katilimci_Sayisi) * Convert.ToInt32(Madde_Sayisi);
if (Secilen_Liste.Count > 0) //en az 1 tane madde seçilmesi gerekir.
{
KSoruFormu.progressBar1.Value = 0;
KSoruFormu.Cursor = Cursors.No;
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
string AktifSayfa = activeWorksheet.Name;
if (AktifSayfa == IslemSayfasi)//Doğru Sayfada Hesaplama Yapması İçin Kullanılır.
{
SilinecekIndisler = new ArrayList();
for (int i = 1; i <= Katilimci_Sayisi; i++)
{ //Bütün satırlar için.
for (int j = 1; j <= Madde_Sayisi; j++)
{ //Her bir satırın sütunları için.
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki != "0")
{//sıradaki hücre sıfırdan farklı ise yani kullanıcı soruyu işaretlemiş ise o satırın indis numarası tutulur.
int flag = 0;
foreach (var item in Secilen_Liste)
{
if (j.ToString() == item.ToString())
{
flag++;
}
}
if (flag > 0)
{
SilinecekIndisler.Add(i); break;
}
}
try
{
KSoruFormu.progressBar1.Value++;
}
catch { }
}
}
if (SilinecekIndisler.Count > 0)//silinecek satır var ise devam eder.
{
KSoruFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi + SilinecekIndisler.Count;
int[,] sil = new int[Convert.ToInt32(SilinecekIndisler.Count), Madde_Sayisi];
for (int i = 0; i < SilinecekIndisler.Count; i++)
{
for (int j = 0; j < Madde_Sayisi; j++)
{//silinecek satırların değerlerini tutar.
sil[i, j] = Convert.ToInt32(Hucrelerden_Okuma_Metodu((Convert.ToInt32(SilinecekIndisler[i]) + 1), (j + 2)));
try
{
KSoruFormu.progressBar1.Value++;
}
catch { }
}
}
Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range row;
int[] rows = new int[SilinecekIndisler.Count];
int SilinenSayac = 0;
foreach (var item in SilinecekIndisler)
{//silinmesi gereken satırları siler.
row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) + 1 - SilinenSayac];
row.Delete();
SilinenSayac++;
try
{
KSoruFormu.progressBar1.Value++;
}
catch { }
}
Katilimci_Sayisi = Katilimci_Sayisi - SilinecekIndisler.Count;//katılımcı sayısını günceller.
Excel.Workbook wsTemizlenenler = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet x = wsTemizlenenler.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
x.Name = YeniTemizlenenlerSayfasiOlustur();//yeni temizlenenler sayfası oluşturur.
KSoruFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi;
for (int i = 0; i < SilinecekIndisler.Count; i++)
{
for (int j = 0; j < Madde_Sayisi; j++)
{
x.Cells[i + 1, j + 1] = sil[i, j].ToString();//yeni oluşturulan sayfaya silinecek verileri yazdırır.
try
{
KSoruFormu.progressBar1.Value++;
}
catch { }
}
}
KSoruFormu.Cursor = Cursors.Default;
if (KSoruFormu.progressBar1.Value < KSoruFormu.progressBar1.Maximum)
{
for (int i = KSoruFormu.progressBar1.Value; i < KSoruFormu.progressBar1.Maximum; i++)
{
KSoruFormu.progressBar1.Value++;
}
}
//bilgi mesajı.
MessageBox.Show(SilinecekIndisler.Count.ToString() + " Veri " + x.Name.ToString() + " Sayfasına Taşındı.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{//hiç veri silinmediyse.
KSoruFormu.Cursor = Cursors.Default;
MessageBox.Show("Silinecek Hiç Bir Veri Bulunamadı.");
if (KSoruFormu.progressBar1.Value < KSoruFormu.progressBar1.Maximum)
{
for (int i = KSoruFormu.progressBar1.Value; i < KSoruFormu.progressBar1.Maximum; i++)
{
KSoruFormu.progressBar1.Value++;
}
}
}
}
else
{
MessageBox.Show("Veri Temizleme İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Madde Listesinden Seçim Yapınız.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void createNewExcel(DataSet1.PriProLieferscheinRechnungRow newRow)
{
string destination = getLieferscheinFilename(newRow);
try
{
File.Copy(vorlageFile, destination, true);
newRow.LieferscheinPfad = destination;
}
catch (Exception ex)
{
Helper.LogHelper.WriteDebugLog(ex.ToString());
}
// das Excelsheet offnen
if (File.Exists(destination))
{
try
{
// die Excelanwendung
excelApp = new Excel.Application();
excelApp.DisplayAlerts = false;
// die Excelmappe
excelWorkbook = excelApp.Workbooks.Open(destination,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
int anzahlBlaetter = excelWorkbook.Sheets.Count;
if (anzahlBlaetter > 0)
{
// das Excelblatt
excelSheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
diePlatzhalterBefuellen(excelSheet, newRow);
excelWorkbook.Save();
}
// alles beenden und freigeben
excelWorkbook.Close(false, destination, null);
excelApp.Quit();
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
System.Diagnostics.Process.Start(destination);
}
catch (Exception ex)
{
Helper.LogHelper.WriteDebugLog(ex.ToString());
}
}
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
//ilk önce ölçek nokta sayısı madde sayısı ve cevaplayıcı sayısı doğru girilip girilmediği kontrol edilir.
if (editBox2.Text != "" && Convert.ToInt32(editBox2.Text) >= 1)
{
if (editBox3.Text != "" && Convert.ToInt32(editBox3.Text) >= 1)
{
if (editBox4.Text != "" && Convert.ToInt32(editBox4.Text) >= 1)
{
Globals.ThisAddIn.Application.Cells.Clear();//sayfayı temizler
Anket_Tipi = Convert.ToInt32(editBox2.Text);
Madde_Sayisi = Convert.ToInt32(editBox3.Text);
Secilecek_Liste.Clear();
Secilen_Liste.Clear();
Hucreye_Yazdirma_Metodu(1, 1, "ID");
for (int l = 1; l <= Madde_Sayisi; l++)//veri temizleme işleminde kullanılması için maddeler güncellenir.
{
Secilecek_Liste.Add(l);
}
int i;
for (i = 1; i <= Madde_Sayisi; i++)//Veri giriş ekranı oluşturulur.
{
Hucreye_Yazdirma_Metodu(1, (i + 1), "Madde" + i.ToString());
}
Hucreye_Yazdirma_Metodu(1, (i + 2), "ECT");
Hucreye_Yazdirma_Metodu(1, (i + 3), "EKCT");
Hucreye_Yazdirma_Metodu(1, (i + 4), "EKOCT");
Hucreye_Yazdirma_Metodu(1, (i + 5), "KCT");
Hucreye_Yazdirma_Metodu(1, (i + 6), "KOCT");
Hucreye_Yazdirma_Metodu(1, (i + 7), "NKCT");
Hucreye_Yazdirma_Metodu(1, (i + 8), "ONCT");
Hucreye_Yazdirma_Metodu(1, (i + 9), "CVPSZCT");
Hucreye_Yazdirma_Metodu(1, (i + 10), "ARADCT");
Katilimci_Sayisi = Convert.ToInt32(editBox4.Text);
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 2, "Ortalamalar");
button2.Enabled = true;
button8.Enabled = true;
button9.Enabled = true;
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
IslemSayfasi = activeWorksheet.Name;//hesaplama işleminde kullanılması için işlem sayfası tutulur.
}
else
{
MessageBox.Show("Cevaplayıcı Sayısı Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Madde Sayısı Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ölçek Tipi Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// EXCEL转PDF
/// </summary>
/// <param name="strSourceFile">要转换的EXCEL文件路径</param>
/// <param name="strTargetFile">目标文件</param>
/// <returns>转换结果：TRUE FALSE</returns>
public bool ExcelConvertTOPDF(string strSourceFile, string strTargetFile)
{
bool flag = false;
if (File.Exists(strSourceFile))
{
//转换成的上档格式PDF
EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF;
object targetFile = strTargetFile;
object missing = Type.Missing;
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
EXCEL.Worksheet sheet = null;
try
{
excel = new EXCEL.ApplicationClass();
//excel.DisplayAlerts = true;
workBooks = excel.Workbooks;
workBook = workBooks.Open(strSourceFile, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//设置格式，导出成PDF
sheet = (EXCEL.Worksheet)workBook.Worksheets[1];//下载从1开始
//把sheet设置成横向
//sheet.PageSetup.Orientation = EXCEL.XlPageOrientation.xlLandscape;
//可以设置sheet页的其他相关设置，不列举
sheet.ExportAsFixedFormat(targetType, targetFile, EXCEL.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
flag = true;
}
catch (Exception ex)
{
classLims_NPOI.WriteLog(ex, "");
flag = false;
}
finally
{
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
//Marshal.FinalReleaseComObject(sheet);
sheet = null;
}
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
//flag = KillSpecialExcel(excel);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
public bool uploadInfo(int type, string vCode, string deviceCode) //type 0: 正常上传， type 1: 补充上传
{
bool rtn = false;
string code = "";
bool isCorrect = false;
string errormsg = "";
string name = "";
if (File.Exists(testFile))
{
if (File.Exists(sourceFile))
{
File.Delete(sourceFile);
}
File.Copy(testFile, sourceFile);
FileInfo fileInfo = new FileInfo(sourceFile);
//有最新文件，开始上传数据
//先检查数据源中的校验码是否正确
Excel.ApplicationClass excel = new Excel.ApplicationClass();
//MessageBox.Show(excel.Version);
excel.Visible = false;
excel.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, "halleluja", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)excel.Worksheets[2];
name = ((Excel.Range)ws.Cells[3, 3]).Text.ToString();
sourceFileName = name;
excel.Quit();
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
if (type == 0)
{
if (Patient.w_code != "")
{
code = Patient.w_code;
verifyCode = code;
try
{
string strOrder = webService.isExistOrder_v2(code, Patient.w_username, deviceCode);
if (strOrder.Length > 2)
{
vjList = getValidate(strOrder);
if (vjList.name.Length > 0)
{
isCorrect = true;
}
string pType = vjList.peopleType;
//if (pType == "1")
//{
// isAdult = false;
//}
//else
//{
// isAdult = true;
//}
}
else if (strOrder == "0")
{
errormsg = "诊疗人不存在,";
}
else if (strOrder == "-1")
{
errormsg = "订单不存在,或者被使用";
}
else if (strOrder == "-2")
{
errormsg = "门店用户未设置所属门店,";
}
else if (strOrder == "-3")
{
errormsg = "订单预约门店与使用门店不同,";
}
else if (strOrder == "-4")
{
errormsg = "订单状态不可用,";
}
else if (strOrder == "-5")
{
errormsg = "设备不存在,";
}
else if (strOrder == "-6")
{
errormsg = "设备未绑定服务,";
}
else if (strOrder == "-7")
{
errormsg = "设备使用次数为0,";
}
else if (strOrder == "-8")
{
errormsg = "设备服务未到开始可用时间,";
}
else if (strOrder == "-9")
{
errormsg = "设备服务使用时间已到期,";
}
}
catch (Exception ex)
{
string er = ex.ToString();
LogHelper.WriteLog(ex.ToString());
}
}
else
{
errormsg = "验证码录入有误,";
}
}
else
{
verifyCode = vCode;
isCorrect = true;
}
//上传文件到服务器
//verifyCode = "hahahaha";
if (type == 0)
{
FTPUpLoad();
}
//有更新文件，但是文件有问题
if (!isCorrect)
{
File.Delete(sourceFile);
//SetValue("ChangeTime", latestTime);
LogHelper.WriteLog(sourceFileName + "-" + errormsg + "文件存在问题，云端处理数据失败！");
//XtraMessageBox.Show(sourceFileName + "-" + errormsg + "云端处理数据失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
//SetValue("ChangeTime", latestTime);
LogHelper.WriteLog("开始云端处理数据");
//copyTemp();
string temp = System.Windows.Forms.Application.StartupPath + "/DevExpress.Map.v16.2.dll";
copyStream(reportFile, temp);
//上传数据
//Thread thdSub = new Thread(new ThreadStart(ThreadFun));
//thdSub.Start();
rtn = ThreadFun(deviceCode);
}
return(rtn);
}
// Generate an Excel worksheet with the results in reverse chronological order so the most
// recent activity in the repository is shown on top. Returns true if the operation completed
// successfully, false otherwise. COMException caught and logged in
// %LOCALAPPDATA%\AcTools\Logs\LatestTransactions-YYYY-MM-DD.log on worksheet creation failure.
private static bool report()
{
Excel.Application excel = new Excel.Application();
if (excel == null)
{
MessageBox.Show("Excel installation not found.", "LatestTransactions",
MessageBoxButtons.OK, MessageBoxIcon.Hand);
return(false);
}
bool ret = true; // assume success
excel.DisplayAlerts = false; // don't display the SaveAs dialog box
Excel.Workbook wbook = excel.Workbooks.Add();
Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(1);
Excel.Range rheader = wsheet.get_Range("A1", "F1");
rheader.Font.Bold = true;
rheader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Excel.Range rdate = wsheet.get_Range("B:B");
rdate.NumberFormat = "mmm d, yyyy h:mm:ss am/pm";
Excel.Range rcomment = wsheet.get_Range("F:F");
rcomment.WrapText = true;
wsheet.Cells[1, "A"] = "Depot";
wsheet.Columns["A"].ColumnWidth = 20;
wsheet.Cells[1, "B"] = "Time";
wsheet.Columns["B"].ColumnWidth = 25;
wsheet.Cells[1, "C"] = "Action";
wsheet.Columns["C"].ColumnWidth = 15;
wsheet.Cells[1, "D"] = "User";
wsheet.Columns["D"].ColumnWidth = 15;
wsheet.Cells[1, "E"] = "Trans_ID";
wsheet.Columns["E"].ColumnWidth = 10;
wsheet.Cells[1, "F"] = "Comment";
wsheet.Columns["F"].ColumnWidth = 50;
int row = 2;
foreach (XElement trans in _transactions.OrderByDescending(n => n.acxTime("time")))
{
string depot = trans.Annotation <string>();
int id = (int)trans.Attribute("id");
string action = (string)trans.Attribute("type");
DateTime?time = trans.acxTime("time");
string user = (string)trans.Attribute("user");
string comment = trans.acxComment();
wsheet.Cells[row, "A"] = depot;
wsheet.Cells[row, "B"] = time;
wsheet.Cells[row, "C"] = action;
wsheet.Cells[row, "D"] = user;
wsheet.Cells[row, "E"] = id;
wsheet.Cells[row, "F"] = comment;
row++;
}
string file = String.Empty;
try
{
file = Path.Combine(_fileLocation, _fileName);
wbook.SaveAs(Filename: file, ReadOnlyRecommended: true);
wbook.Close();
}
catch (COMException exc)
{
AcDebug.Log(exc.Message);
MessageBox.Show(exc.Message);
ret = false;
}
finally { excel.Quit(); }
if (ret)
{
MessageBox.Show($"Latest transactions saved to {file}", "LatestTransactions",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(ret);
}
private void UpdateEnPISheet(SwitchModel sm)
{
bool outputPresent = false;
Excel.Worksheet adjustedDataSheet = new Excel.Worksheet();
//Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; //TFS Ticket: 71242
Excel.Worksheet thisSheet = energySheet;
//Update the SEP Validation Check values
string sourceName = energySheet.Name;
sourceName = sourceName.Substring(1, sourceName.Length - 1).ToString().TrimStart();
Globals.ThisAddIn.SelectedSourcesBestModelFormulas[sourceName] = sm.Formula;
string adjustedDatasheetName = string.Empty;
foreach (GroupSheetCollection gsc in Globals.ThisAddIn.masterGroupCollection)
{
bool matchingCollection = false;
foreach (GroupSheet GS in gsc)
{
string GSname = GS.Name;
if (GS.WS.Equals(thisSheet))
{
matchingCollection = true;
}
if (GS.adjustedDataSheet && matchingCollection)
{
adjustedDataSheet = GS.WS;
adjustedDatasheetName = GS.WS.Name;
UpdateSEPValidationCheckList(adjustedDatasheetName);
outputPresent = true;
}
if (GS.outputSheet)
{
populateModelData(thisSheet, GS.WS, sm.ModelNumber);
}
}
}
if (outputPresent)
{
string adjDataSheetName = adjustedDataSheet.Name;
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[adjustedDataSheet.Name]).Select(Type.Missing);
thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
//Update the latest values on Validation Check Table of Model Sheet
try
{
for (int i = 0; i < Globals.ThisAddIn.lstSEPValidationValues.Count; i++)
{
Excel.Range sepValidationChk = thisSheet.get_Range("C13").get_Offset(0, i).get_Resize(1, 1);
sepValidationChk.Value2 = Globals.ThisAddIn.lstSEPValidationValues[i].SEPValidationCheck;
sepValidationChk.NumberFormat = "General";
}
}
catch (Exception ex)
{
}
Excel.ListObject LO = (Excel.ListObject)thisSheet.ListObjects.get_Item(1);
foreach (Excel.ListColumn col in LO.ListColumns)
{
//------------------------------
//modify substring so that regression runs past 9 can switch models
string shortColName = col.Name;
//since sheet names are limited to 29 characters -- see CreateValidWorksheetName in ExcelHelpers.cs
if (col.Name.Length >= 36)
{
shortColName = col.Name.Substring(0, 35);
}
string shtname = sm.SheetName.Substring(2);
if (shortColName.Equals("Modeled " + sm.SheetName.Substring(2)))
//------------------------------
{
col.DataBodyRange.Value2 = "=" + sm.Formula;
//recalculate all of the List objects so numbers are updated.
foreach (Excel.Worksheet WS in WB.Worksheets)
{
string wsname = WS.Name;
foreach (Excel.ListObject ListObj in WS.ListObjects)
{
if (ListObj.DataBodyRange != null)
{
ListObj.DataBodyRange.Dirty();
ListObj.DataBodyRange.Calculate();
}
}
}
}
}
Excel.Range negativeMessageHeader = thisSheet.get_Range("A2");
Excel.Range negativeMessageDescription = thisSheet.get_Range("A3");
if (Globals.ThisAddIn.NegativeCheck(LO, Globals.ThisAddIn.modeledSourceIndex))
{
negativeMessageHeader.EntireRow.Hidden = false;
negativeMessageDescription.EntireRow.Hidden = false;
}
else
{
negativeMessageHeader.EntireRow.Hidden = true;
negativeMessageDescription.EntireRow.Hidden = true;
}
}
}
public static void GetLastRowCol(Excel.Worksheet ws, ref int lastRow, ref int lastCol)
{
lastCol = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Excel.XlSpecialCellsValue.xlTextValues).Column;
lastRow = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Excel.XlSpecialCellsValue.xlTextValues).Row;
}
/// <summary>
/// Delete one row
/// </summary>
/// <param name="sheet"></param>
/// <param name="cellRange"></param>
public static void DeleteRow(Excel.Worksheet sheet, string cellRange)
{
Excel.Range rangeDelete = sheet.Range[cellRange];
rangeDelete.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
ReleaseComObject(rangeDelete);
}
private void ExportExcel(DataSet ds, string saveFileName)
{
try
{
if (ds == null)
{
MessageBox.Show("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application elApp = new Microsoft.Office.Interop.Excel.Application();
if (elApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = elApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//写入字段名称
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数据
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件出错，文件可能正在被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
elApp.Quit();
GC.Collect(); //强制销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName);
MessageBox.Show("成功保存到Excel");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static void InsertRow(Excel.Worksheet sheet, string cell)
{
Excel.Range range = sheet.Range[cell];
range.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
ReleaseComObject(range);
}
private void PopulateModels(Excel.ListObject DataLO)
{
Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
//
if (DataLO != null)
{
int bestModelNumber;
//check to see if model can be switched from current sheet.
try
{
//TFS Ticket 71242
string strBestModel = ((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString();
string[] strArry = strBestModel.Split(new string[] { EnPIResources.bestModel }, StringSplitOptions.RemoveEmptyEntries);
bestModelNumber = Convert.ToInt32(strArry[0]);
//bestModelNumber = Convert.ToInt32(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Substring(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Length - 1));
}
catch
{
this.label1.Text = "In order to switch a model, you must first be on the sheet that the model is listed on.";
this.comboBox1.Enabled = false;
return;
}
int currentModelNumber = 0;
int varCount = 1;
modelCount = 0;
bool bestModel = false;
foreach (Excel.ListRow row in DataLO.ListRows)
{
string[] variables = new string[DataLO.ListRows.Count];
double[] VarPvalue = new double[DataLO.ListRows.Count];
if (((Excel.Range)row.Range.Cells[1]).Value2 != null)
{
varCount = 1;
modelCount++;
//modelCount = row.Range.Count;
currentModelNumber = Convert.ToInt32(((Excel.Range)row.Range.Cells[1]).Value2.ToString());
if (currentModelNumber.Equals(bestModelNumber))
{
bestModel = true;
}
variables[0] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString();
VarPvalue[0] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString());
//SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString())
// , ((Excel.Range)row.Range.Cells[13]).Value2.ToString(), bestModel, thisSheet.Name, modelCount
// , VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString())
// , Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString()));
//Added by suman SEP Changes
SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString())
, ((Excel.Range)row.Range.Cells[14]).Value2.ToString(), bestModel, thisSheet.Name, modelCount
, VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString())
, Convert.ToDouble(((Excel.Range)row.Range.Cells[10]).Value2.ToString()));
sModCol.Add(sm);
bestModel = false;
}
else
{
if (((Excel.Range)row.Range.Cells[3]).Value2 != null)
{
sModCol.Item(sModCol.Count - 1).VariableNames[varCount] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString();
varCount++;
}
//if (((Excel.Range)row.Range.Cells[6]).Value2 != null)
//{
// sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString());
// varCount++;
//}
if (((Excel.Range)row.Range.Cells[7]).Value2 != null)
{
sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString());
varCount++;
}
}
}
}
this.label1.Text = "Select the model you wish to use to calculate the adjusted values on the EnPI Results and SEnPI Results sheets from the drop down below.";// +thisSheet.Name + ":";
int t = DataLO.ListColumns["Model is Appropriate for SEP"]._Default.Length;
}
/// <summary>
/// Delete columns
/// </summary>
/// <param name="sheet"></param>
/// <param name="beginColumn"></param>
/// <param name="endColumn"></param>
public static void DeleteColumns(Excel.Worksheet sheet, string beginColumn, string endColumn)
{
sheet.Range[beginColumn + "1", endColumn + "1"].EntireColumn.Delete();
}
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();
}
