public void CreateWorkbook()
{
var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
@"Templates\TemplateWorkBook.xlsx");
MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
//mWorkbook = mApplication.Workbooks.Add(Missing.Value);
mWorkbook = mApplication.Workbooks.Add(templateFile);
mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
//var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];
//Rename first and second worksheet, delete third worksheet
//mMeasurementDataWorksheet.Name = "Measurement Data";
//mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
//sheet3.Delete();
//Set measurement data worksheet as active worksheet
mMeasurementDataWorksheet.Activate();
//Display windows in splitview
mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);
MainForm.HeadersWritten = false;
MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
}
//Excel.Range wRange;
public DataExcel()
{
// 開啟一個新的應用程式
excelApp = new Excel.Application
{
// 讓Excel文件可見
//Visible = true,
// 停用警告訊息
DisplayAlerts = false
};
// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
// 設定活頁簿焦點
wBook.Activate();
// 引用第一個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
// 命名工作表的名稱
wSheet.Name = "result";
// 設定工作表焦點
wSheet.Activate();
}
public void CreateWorkbook()
{
var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
@"Templates\TemplateWorkBook.xlsx");
MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
//mWorkbook = mApplication.Workbooks.Add(Missing.Value);
mWorkbook = mApplication.Workbooks.Add(templateFile);
mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
//var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];
//Rename first and second worksheet, delete third worksheet
//mMeasurementDataWorksheet.Name = "Measurement Data";
//mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
//sheet3.Delete();
//Set measurement data worksheet as active worksheet
mMeasurementDataWorksheet.Activate();
//Display windows in splitview
mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);
MainForm.HeadersWritten = false;
MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
}
public void PrintWskazniki(List <Wskazniki> listaWskaznikow, string nazwaSpolki)
{
oWB = (Excel._Workbook)(oXL.Workbooks.Open("E:\\raporty\\wyniki.xlsx"));
oSheet = (Excel._Worksheet)oWB.Sheets.Add();
oSheet.Name = nazwaSpolki;
//oRng = oSheet.UsedRange;
oSheet.Activate();
// worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();
for (int j = 0; j < 5; j++)
{
int startRok = 2010;
oSheet.Cells[1, j + 4] = (startRok + j).ToString();
}
for (int i = 0; i < listaWskaznikow.Count(); i++)
{
oSheet.Cells[i + 2, 1] = listaWskaznikow[i].category;
oSheet.Cells[i + 2, 2] = listaWskaznikow[i].name;
oSheet.Cells[i + 2, 3] = listaWskaznikow[i].wzor;
for (int j = 0; j < 5; j++)
{
oSheet.Cells[i + 2, j + 4] = listaWskaznikow[i].value[j];
}
}
oWB.Save();
}
public override void PerformExport(DataLayer dbObject)
{
Excel.Application xA;
Excel._Workbook wb;
try
{
xA = new Excel.Application();
xA.Visible = true;
//add a new workbook...
wb = xA.Workbooks.Add(Missing.Value);
Excel._Worksheet ws = (Excel._Worksheet)wb.Worksheets.Add(null, null, null, null);
BuildUsersWorkSheet(ws, dbObject);
ws.Activate();
xA.Save(Missing.Value);
}
catch (Exception ee)
{
}
}
/// <summary>
/// Reads in headers from Excel File
/// </summary>
private void LoadHeaders()
{
if (IsValidWorkbook(o_workbook))
{
// workbook is valid
// get first sheet (not zero based index so use 1 as first element)
o_first_sheet = o_workbook.Sheets[1];
Utility.Log("First Sheet Name:", o_first_sheet.Name);
o_first_sheet.Activate();
// Must know header row and start column
// calculate number of columns of data
int column_index = column_start;
string value;
while ((value = o_first_sheet.Cells[header_row, column_index].Value2) != null)
{
num_columns++;
// add header names to list
dictionary_headers[value] = column_index;
column_index++;
}
column_end = column_start + num_columns - 1;
Utility.Log("Column end for Headers:", Utility.ConvertNumToColumnLetters(column_end));
}
}
private void Tool_Click(object sender, RoutedEventArgs e)
{
MSWord.Application EXCL1 = new MSWord.Application(); //新建一个应用程序EXC1
EXCL1.Visible = true; //设置EXC1打开后可见
MSWord.Workbooks wbs = EXCL1.Workbooks;
MSWord._Workbook wb = wbs.Add(GlobalData.gFileName); //打开EXCEL
MSWord._Worksheet mySheet;
mySheet = wb.Sheets[wantUseSheetIndex(GlobalData.gMySheetName, wb)]; //找到我要操作的sheet
mySheet.Activate();
//tiaoshi.Text = excelElementRead(mySheet, 1, 5).Interior.Color.ToString();
System.Data.DataTable dt = readNonredunOneColumn(mySheet, GlobalData.gWantUseColumn);
MSWord._Worksheet testPointSheet = wb.Sheets[wantUseSheetIndex(GlobalData.gTestPointSheetName, wb)];
testPointSheet.Activate();
excelElementWrite(testPointSheet, 1, 1, "测试点序号");
excelElementWrite(testPointSheet, 1, 2, "测试点");
for (int i = 0; i < dt.Rows.Count; i++)
{
excelElementWrite(testPointSheet, i + 2, 1, dt.Rows[i][0].ToString());
excelElementWrite(testPointSheet, i + 2, 2, dt.Rows[i][1].ToString());
}
writeTestPointCount(mySheet, GlobalData.gTestPointRange, GlobalData.gWantUseColumn, dt);
wb.Save();
}
private void InitializeExcel()
{
// 開啟一個新的應用程式
excelApp = new Excel.Application();
// 讓Excel文件可見
excelApp.Visible = true;
// 停用警告訊息
excelApp.DisplayAlerts = false;
// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
// 設定活頁簿焦點
wBook.Activate();
// 引用第一個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
// 命名工作表的名稱
wSheet.Name = "角度精度驗證";
// 設定工作表焦點
wSheet.Activate();
// 設定第1列資料
excelApp.Cells[1, 1] = "第一邊";
excelApp.Cells[1, 2] = "第二邊";
excelApp.Cells[1, 3] = "第三邊";
}
// A9: Number of classroom teachers by teaching level of education, employment status, type of institution and sex
void sheetA9(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 1; //row offset
const int PUBLIC = 14;
const int PRIVATE = 17;
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A9"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED, SCHOOLTYPE, GENDER, sum(COUNT) as COUNT
from #TeacherBaseTable
group by ISCED, SCHOOLTYPE, GENDER",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced = rdr.GetString(0);
string schoolType = rdr.GetString(1);
string gender = rdr.GetString(2);
int count = rdr.GetInt32(3);
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", isced, gender, schoolType, count.ToString()));
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row = schoolType == "PUBLIC" ? PUBLIC : PRIVATE + rowOffset;
List <string> columns = new List <string>();
if (isced == "ISCED 24" || isced == "ISCED 34")
{
columns.Add("ISCED 24+34");
columns.Add(isced.Substring(0, 7));
}
else if (isced == "ISCED 25" || isced == "ISCED 35")
{
columns.Add("ISCED 25+35");
columns.Add(isced.Substring(0, 7));
}
else
{
columns.Add(isced);
}
foreach (string column in columns)
{
workSheet.Cells[row, getCol(column)] = workSheet.get_Range(helpers.GetCellAddress(getCol(column), row)).Value2 + count;
}
}
}
}
//private static readonly log4net.ILog log = log4net.LogManager.GetLogger (System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// A2: Number of students by level of education, intensity of participation, type of institution and sex
void sheetA2(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 1; //row offset
const int PUBLIC = 14; //row
const int PRIVATE = 17; //row
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A2"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
SqlCommand cmd = new SqlCommand(
@"select ISCED, SCHOOLTYPE, gender, sum(count) as COUNT from #StudentsTable group by ISCED, SCHOOLTYPE, gender",
sqlConn);
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr.IsDBNull(2))
{
Console.WriteLine("Skipping row, count: " + rdr.GetInt32(3).ToString());
continue;
}
string isced = rdr.GetString(0);
string schoolType = rdr.GetString(1);
string gender = rdr.GetString(2);
int count = rdr.GetInt32(3);
//Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", isced, gender, schoolType, count));
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row = (schoolType == "Public" ? PUBLIC : PRIVATE) + rowOffset;
int column = getCol(isced);
workSheet.Cells[row, column] = count;
Console.WriteLine(row.ToString() + " : " + column.ToString());
}
}
}
public static void CreateExperimentTable_Competitor(ExperimentTable exp, Excel.Application excel)
{
//每個 sheet 以 seq_id 命名
if (exp.SeqID == "")
{
return;
}
Excel._Worksheet xlsht = excel.ActiveWorkbook.Worksheets.Add();
xlsht.Name = exp.SeqID;
xlsht.Activate();
//標題
Excel.Range rng;
rng = xlsht.Range[xlsht.Cells[1, 1], xlsht.Cells[1, 15]];
rng.Style.Font.Size = 12; //整個表格的字型大小
rng.Style.Font.Name = "微軟正黑體"; //整個表格的字型
rng.ColumnWidth = 6f;
rng.Value = exp.CompTool.Name + @"混鍊加工/物性檢測單";
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.Font.Size = 14;//選取範圍的字型大小
rng.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleDouble;
xlsht.get_Range("A:A").ColumnWidth = 7.5;
//rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlDouble;
//實驗單基本資訊(欄位名)
rng = xlsht.Range[xlsht.Cells[2, 1], xlsht.Cells[7, 1]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] {
"工單號", "申請日", "申請人", "建議試驗日期", "客戶名稱", "試驗目的"
});
for (int i = 2; i <= 7; i++)
{
rng = xlsht.Range[xlsht.Cells[i, 1], xlsht.Cells[i, 2]];
rng.Merge();
}
//填入工單號~實驗目的資料
rng = xlsht.Range[xlsht.Cells[2, 3], xlsht.Cells[7, 3]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] {
exp.SeqID, exp.ApplyDate, exp.User.Value, exp.RunDate, exp.Customer.Value, exp.Purpose
});
for (int i = 2; i <= 4; i++) //把工單號~ 客戶名稱的資訊 merge，並且加入表格底線
{
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 5]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
for (int i = 5; i <= 6; i++) //把工單號~ 客戶名稱的資訊 merge，並且加入表格底線
{
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 7]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
//實驗目的資訊欄位 merge + 底線
rng = xlsht.Range[xlsht.Cells[7, 3], xlsht.Cells[7, 10]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlsht.Cells[4, 6].Value = "混鍊試驗期限 :";
rng = xlsht.Range[xlsht.Cells[4, 8], xlsht.Cells[4, 10]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[4, 11].Value = "分析試驗期限 :";
rng = xlsht.Range[xlsht.Cells[4, 13], xlsht.Cells[4, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[5, 8].Value = "實際實驗日期";
rng = xlsht.Range[xlsht.Cells[5, 10], xlsht.Cells[5, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[6, 8].Value = "試料牌號";
rng = xlsht.Range[xlsht.Cells[6, 10], xlsht.Cells[6, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[6, 10].Value = exp.Grade;
xlsht.Cells[7, 11].Value = "試料需求";
xlsht.Cells[7, 13].Value = Convert.ToDouble(exp.BagNumber) * Convert.ToDouble(exp.BagWeight);
rng = xlsht.Range[xlsht.Cells[7, 13], xlsht.Cells[7, 13]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[7, 14].Value = "Kg";
xlsht.Cells[2, 7].Value = "保留樣品";
xlsht.Cells[2, 10].Value = "陪同試驗";
xlsht.Cells[2, 13].Value = "急件";
xlsht.Cells[3, 7].Value = "材質證明";
xlsht.Cells[3, 10].Value = "分析報告";
xlsht.Cells[3, 13].Value = "自我聲明";
rng = xlsht.get_Range("G2,G3,J2,J3,M2,M3");
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
rng = xlsht.get_Range("F2,F3,I2,I3,L2,L3");
rng.Value = "□";
rng.Font.Name = "Times New Roman";
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//int rowsOfPart2 = Math.Max(exp.CompTool.Items.Count(), exp.Formulas.Count());
//
// 配方項目、混練加工、押出機溫度、射出加工、射出溫度省略
//
int _row = 10, _col = 1;
//
// 備註欄
//
xlsht.Cells[_row, 1].Value = "實驗備註";
rng = xlsht.Range[xlsht.Cells[_row, 1], xlsht.Cells[_row, 15]];
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlsht.Cells[_row + 1, 1].Value = exp.Remark;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 5, 15]];
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//備註欄位框線
rng = xlsht.Range[xlsht.Cells[_row, 1], xlsht.Cells[_row + 5, 15]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 物性
//
_row = _row + 7;
_col = 1;
xlsht.Cells[_row, 1].Value = "物性檢測數據";
xlsht.Cells[_row, 1].Font.Color = Excel.XlRgbColor.rgbDarkRed;
xlsht.Cells[_row, 1].Font.Bold = true;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 1, 12]];
rng.Interior.Color = Excel.XlRgbColor.rgbLightGray;
rng.Value = new string[] { "檢測項目", "", "單位", "", "檢測值(平均)", "", "", "", "偏差值", "", "", "" };
List <IItem> property = exp.Property.OrderBy(x => x.ProdType).ToList();
string[] p_item_name = property.Select(x => x.Name).ToArray();
string[] p_item_unit = property.Select(x => x.Unit).ToArray();
rng = xlsht.Range[xlsht.Cells[_row + 2, 1], xlsht.Cells[_row + p_item_name.Length, 1]];
rng.Value = excel.WorksheetFunction.Transpose(p_item_name);
rng = xlsht.Range[xlsht.Cells[_row + 2, 3], xlsht.Cells[_row + p_item_name.Length, 3]];
rng.Value = excel.WorksheetFunction.Transpose(p_item_unit);
for (int i = _row + 1; i < _row + p_item_name.Length + 2; i++)
{
rng = xlsht.Range[xlsht.Cells[i, 1], xlsht.Cells[i, 2]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 4]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 5], xlsht.Cells[i, 8]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 9], xlsht.Cells[i, 12]];
rng.Merge();
}
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + p_item_name.Length + 1, 12]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 版面設定
//
((Excel._Worksheet)excel.ActiveSheet).PageSetup.Zoom = false;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.FitToPagesWide = 1;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.FitToPagesTall = false;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.TopMargin = excel.InchesToPoints(0.59);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.BottomMargin = excel.InchesToPoints(0.59);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.LeftMargin = excel.InchesToPoints(0.354);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.RightMargin = excel.InchesToPoints(0.354);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.CenterHorizontally = true;
}
public static void CreateExperimentTable(ExperimentTable exp, Excel.Application excel)
{
//每個 sheet 以 seq_id 命名
if (exp.SeqID == "")
{
return;
}
Excel._Worksheet xlsht = excel.ActiveWorkbook.Worksheets.Add();
xlsht.Name = exp.SeqID;
xlsht.Activate();
//標題
Excel.Range rng;
rng = xlsht.Range[xlsht.Cells[1, 1], xlsht.Cells[1, 15]];
rng.Style.Font.Size = 12; //整個表格的字型大小
rng.Style.Font.Name = "微軟正黑體"; //整個表格的字型
rng.ColumnWidth = 6f;
rng.Value = exp.CompTool.Name + string.Format(@"混鍊加工/物性檢測單({0})", exp.SeqID);
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.Font.Size = 14;//選取範圍的字型大小
rng.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleDouble;
xlsht.get_Range("A:A").ColumnWidth = 7.5;
//rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlDouble;
//實驗單基本資訊(欄位名)
rng = xlsht.Range[xlsht.Cells[2, 1], xlsht.Cells[7, 1]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] {
"工單號", "申請日", "申請人", "建議試驗日期", "客戶名稱", "試驗目的"
});
for (int i = 2; i <= 7; i++)
{
rng = xlsht.Range[xlsht.Cells[i, 1], xlsht.Cells[i, 2]];
rng.Merge();
}
//填入申請日~實驗目的資料
xlsht.get_Range("C2").NumberFormat = "@";
rng = xlsht.Range[xlsht.Cells[3, 3], xlsht.Cells[7, 3]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] {
exp.ApplyDate, exp.User.Value, exp.RunDate, exp.Customer.Value, exp.Purpose
});
for (int i = 2; i <= 4; i++) //把工單號~ 申請人的資訊 merge，並且加入表格底線
{
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 5]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
for (int i = 5; i <= 6; i++) //把試驗日期~ 客戶名稱的資訊 merge，並且加入表格底線
{
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 7]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
//實驗目的資訊欄位 merge + 底線
rng = xlsht.Range[xlsht.Cells[7, 3], xlsht.Cells[7, 8]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlsht.Cells[4, 6].Value = "混鍊試驗期限 :";
rng = xlsht.Range[xlsht.Cells[4, 8], xlsht.Cells[4, 10]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[4, 11].Value = "分析試驗期限 :";
rng = xlsht.Range[xlsht.Cells[4, 13], xlsht.Cells[4, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[5, 8].Value = "實際實驗日期";
rng = xlsht.Range[xlsht.Cells[5, 10], xlsht.Cells[5, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[6, 8].Value = "試料牌號";
rng = xlsht.Range[xlsht.Cells[6, 10], xlsht.Cells[6, 14]];
rng.Merge();
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[6, 10].Value = exp.Grade;
//Modify: 以試料需求顯示總重
//xlsht.Cells[7, 11].Value = "試料需求";
//xlsht.Cells[7, 13].Value = Convert.ToDouble(exp.BagNumber) * Convert.ToDouble(exp.BagWeight);
//rng = xlsht.Range[xlsht.Cells[7, 13], xlsht.Cells[7, 13]];
//rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
//xlsht.Cells[7, 14].Value = "Kg";
xlsht.Cells[7, 9].Value = "袋重";
xlsht.Cells[7, 10].Value = Convert.ToDouble(exp.BagWeight);
xlsht.Cells[7, 11].Value = "Kg";
xlsht.Cells[7, 12].Value = "數量";
xlsht.Cells[7, 13].Value = Convert.ToDouble(exp.BagNumber);
xlsht.Cells[7, 14].Value = "袋";
rng = xlsht.Range[xlsht.Cells[7, 10], xlsht.Cells[7, 10]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng = xlsht.Range[xlsht.Cells[7, 13], xlsht.Cells[7, 13]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsht.Cells[2, 7].Value = "保留樣品";
xlsht.Cells[2, 10].Value = "陪同試驗";
xlsht.Cells[2, 13].Value = "急件";
xlsht.Cells[3, 7].Value = "材質證明";
xlsht.Cells[3, 10].Value = "分析報告";
xlsht.Cells[3, 13].Value = "自我聲明";
rng = xlsht.get_Range("G2,G3,J2,J3,M2,M3");
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
rng = xlsht.get_Range("F2,F3,I2,I3,L2,L3");
rng.Value = "□";
rng.Font.Name = "Times New Roman";
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//int rowsOfPart2 = Math.Max(exp.CompTool.Items.Count(), exp.Formulas.Count());
//
// 配方項目
//
string[] r_item_name = exp.Formulas.Select(x => x.Name).ToArray();
string[] r_item_value = exp.Formulas.Select(x => x.Value).ToArray();
string[] r_item_unit = exp.Formulas.Select(x => x.Unit).ToArray();
int _row = 10, _col = 1;
xlsht.Cells[_row, 1].Value = "試驗配方";
xlsht.Cells[_row, 3].Value = "值";
xlsht.Cells[_row, 4].Value = "單位";
xlsht.Cells[_row, 5].Value = "單重";
xlsht.Cells[_row, 6].Value = "總重量";
rng = xlsht.Range[xlsht.Cells[_row, 1], xlsht.Cells[_row, 6]];
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;//表頭加入底線
//
// 填入配方項目名稱+單位+值
//
_col = 1;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + exp.Formulas.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(r_item_name);
_col = 3;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + exp.Formulas.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(r_item_value);
_col = 4;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + exp.Formulas.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(r_item_unit);
//
// 將比重轉換成實際重量
//
List <double> actualwt = new List <double>();
//
// 計算單重
//
double singlewt = Convert.ToDouble(exp.BagWeight);
foreach (IItem item in exp.Formulas)
{
double p = Convert.ToDouble(item.Value);
actualwt.Add(p * singlewt / 100);
}
_col = 5;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + exp.Formulas.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(actualwt.ToArray());
xlsht.Cells[_row + exp.Formulas.Count() + 1, _col].Value = actualwt.Sum();//計算單次實驗總重
//
// 計算總重
//
actualwt.Clear(); //清除之前單重的計算結果
double totalwt = Convert.ToDouble(exp.BagNumber) * Convert.ToDouble(exp.BagWeight);
foreach (IItem item in exp.Formulas)
{
double p = Convert.ToDouble(item.Value);
actualwt.Add(p * totalwt / 100);
}
_col = 6;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + exp.Formulas.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(actualwt.ToArray());
xlsht.Cells[_row + exp.Formulas.Count() + 1, _col].Value = actualwt.Sum();//計算所有試驗總重
//
// 結算列的格式調整
//
xlsht.Cells[_row + exp.Formulas.Count() + 1, _col - 2].Value = "total:";
rng = xlsht.Range[xlsht.Cells[_row + exp.Formulas.Count() + 1, 1], xlsht.Cells[_row + exp.Formulas.Count() + 1, 6]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;//結算加入上底線
//
// 混練條件
//
xlsht.Cells[_row, 10].Value = "混練加工條件";
xlsht.Cells[_row, 10].Font.Color = Excel.XlRgbColor.rgbDarkRed;
xlsht.Cells[_row, 10].Font.Bold = true;
rng = xlsht.Range[xlsht.Cells[_row, 10], xlsht.Cells[_row, 13]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;//結算加入上底線
List <IItem> c_items = exp.CompTool.Items.Where(x => x.Flag != "CT").OrderBy(x => x.Item_Seq).ToList();
string[] c_item_name = c_items.Select(x => x.Name).ToArray();
string[] c_item_unit = c_items.Select(x => x.Unit).ToArray();
string[] c_item_value = c_items.Select(x => x.Value).ToArray();
_col = 10;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + c_item_name.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(c_item_name);
_col = 12;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + c_item_name.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(c_item_value);
_col = 13;
rng = xlsht.Range[xlsht.Cells[_row + 1, _col], xlsht.Cells[_row + c_item_name.Count(), _col]];
rng.Value = excel.WorksheetFunction.Transpose(c_item_unit);
//
// 區段溫度
//
List <IItem> ct_items = exp.CompTool.Items.Where(x => x.Flag == "CT").OrderBy(x => x.Item_Seq).ToList();
string[] ct_item_name = ct_items.Select(x => x.Name).ToArray();
string[] ct_item_value = ct_items.Select(x => x.Value).ToArray();
double m_ct = Math.Ceiling(((double)ct_item_name.Length) / 14); //判斷區段溫度項目是否超過頁面
_row = _row + Math.Max(c_item_name.Count(), r_item_name.Count()) + 1; //區段溫度表格起始列
xlsht.Cells[_row, 2].Value = "押出機-區段溫度";
xlsht.Cells[_row, 2].Font.Bold = true;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 3 * m_ct, 1]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] { "", "設定溫度", "實際溫度" });
_col = 2;
for (int i = 0; i < m_ct; i++)
{
string[] sub_ct_item_name = ct_item_name.Where((x, j) => j >= 13 * i & j < 13 * (i + 1)).ToArray();
string[] sub_ct_item_value = ct_item_value.Where((x, j) => j >= 13 * i & j < 13 * (i + 1)).ToArray();
rng = xlsht.Range[xlsht.Cells[_row + 1 + 3 * i, _col], xlsht.Cells[_row + 1 + 3 * i, _col + sub_ct_item_name.Count() - 1]];
rng.Value = sub_ct_item_name;
rng.Interior.Color = Excel.XlRgbColor.rgbLightGray;
rng = xlsht.Range[xlsht.Cells[_row + 2 + 3 * i, _col], xlsht.Cells[_row + 2 + 3 * i, _col + sub_ct_item_value.Count() - 1]];
rng.Value = sub_ct_item_value;
}
//押出機-區段溫度表格 + 框線
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 3 * m_ct, Math.Min(13, ct_item_name.Length) + 1]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 射出加工
//
_row = _row + (int)m_ct * 3 + 2;// 射出加工表格起史位置
xlsht.Cells[_row, 1].Value = "射出加工條件";
xlsht.Cells[_row, 1].Font.Color = Excel.XlRgbColor.rgbDarkRed;
xlsht.Cells[_row, 1].Font.Bold = true;
List <IItem> i_item = exp.InjTool.Items.Where(x => x.Flag != "IT").OrderBy(x => x.Item_Seq).ToList();
string[] i_item_name = i_item.Select(x => x.Name).ToArray();
string[] i_item_value = i_item.Select(x => x.Value).ToArray();
string[] i_item_unit = i_item.Select(x => x.Unit).ToArray();
double m_i_item = Math.Ceiling(((double)i_item_name.Length) / 3);//判斷單欄需要多少列, 設計頁寬約放三欄..
_col = 1;
for (int i = 0; i < 3; i++)// by column 處理
{
string[] sub_i_item_name = i_item_name.Where((x, j) => j >= i * m_i_item & j < (i + 1) * (m_i_item)).ToArray();
string[] sub_i_item_value = i_item_value.Where((x, j) => j >= i * m_i_item & j < (i + 1) * (m_i_item)).ToArray();
string[] sub_i_item_unit = i_item_unit.Where((x, j) => j >= i * m_i_item & j < (i + 1) * (m_i_item)).ToArray();
rng = xlsht.Range[xlsht.Cells[_row + 1, _col + i * 4], xlsht.Cells[_row + sub_i_item_name.Count(), _col + 1 + i * 4]];
rng.Value = excel.WorksheetFunction.Transpose(sub_i_item_name);
foreach (Excel.Range subrng in rng.Rows)
{
subrng.Merge();
}
rng = xlsht.Range[xlsht.Cells[_row + 1, _col + i * 4 + 2], xlsht.Cells[_row + sub_i_item_name.Count(), _col + i * 4 + 2]];
rng.Value = excel.WorksheetFunction.Transpose(sub_i_item_value);
foreach (Excel.Range subrng in rng.Rows)
{
subrng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
}
rng = xlsht.Range[xlsht.Cells[_row + 1, _col + i * 4 + 3], xlsht.Cells[_row + sub_i_item_name.Count(), _col + i * 4 + 3]];
rng.Value = excel.WorksheetFunction.Transpose(sub_i_item_unit);
}
//
// 射出機溫度
//
List <IItem> it_item = exp.InjTool.Items.Where(x => x.Flag == "IT").OrderBy(x => x.Item_Seq).ToList();
string[] it_item_name = it_item.Select(x => x.Name).ToArray();
string[] it_item_value = it_item.Select(x => x.Value).ToArray();
string[] it_item_unit = it_item.Select(x => x.Unit).ToArray();
double m_it = Math.Ceiling(((double)it_item_name.Length) / 13); //判斷區段溫度項目是否超過頁面
_row = _row + (int)m_i_item + 2; //區段溫度表格起始列
xlsht.Cells[_row, 1].Value = "射出溫度";
xlsht.Cells[_row, 1].Font.Color = Excel.XlRgbColor.rgbDarkRed;
xlsht.Cells[_row, 1].Font.Bold = true;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 3 * m_it, 1]];
rng.Value = excel.WorksheetFunction.Transpose(new string[] { "", "設定溫度", "實際溫度" });
_col = 2;
for (int i = 0; i < m_it; i++)
{
string[] sub_it_item_name = it_item_name.Where((x, j) => j >= 13 * i & j < 13 * (i + 1)).ToArray();
string[] sub_it_item_value = it_item_value.Where((x, j) => j >= 13 * i & j < 13 * (i + 1)).ToArray();
rng = xlsht.Range[xlsht.Cells[_row + 1 + i * 3, _col], xlsht.Cells[_row + 1 + i * 3, _col + sub_it_item_name.Count() - 1]];
rng.Interior.Color = Excel.XlRgbColor.rgbLightGray;
rng.Value = sub_it_item_name;
rng = xlsht.Range[xlsht.Cells[_row + 2 + i * 3, _col], xlsht.Cells[_row + 2 + i * 3, _col + sub_it_item_value.Count() - 1]];
rng.Value = sub_it_item_value;
}
//押出機-區段溫度表格 + 框線
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 3 * m_it, Math.Min(13, it_item_name.Length) + 1]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 備註欄
//
_row = _row + (int)m_it * 3 + 2;
xlsht.Cells[_row, 1].Value = "實驗備註";
rng = xlsht.Range[xlsht.Cells[_row, 1], xlsht.Cells[_row, 15]];
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlsht.Cells[_row + 1, 1].Value = exp.Remark;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 5, 15]];
rng.Merge();
rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//備註欄位框線
rng = xlsht.Range[xlsht.Cells[_row, 1], xlsht.Cells[_row + 5, 15]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 物性
//
_row = _row + 7;
_col = 1;
xlsht.Cells[_row, 1].Value = "物性檢測數據";
xlsht.Cells[_row, 1].Font.Color = Excel.XlRgbColor.rgbDarkRed;
xlsht.Cells[_row, 1].Font.Bold = true;
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + 1, 12]];
rng.Interior.Color = Excel.XlRgbColor.rgbLightGray;
rng.Value = new string[] { "檢測項目", "", "單位", "", "檢測值(平均)", "", "", "", "偏差值", "", "", "" };
List <IItem> property = exp.Property.OrderBy(x => x.ProdType).ToList();
string[] p_item_name = property.Select(x => x.Name).ToArray();
string[] p_item_unit = property.Select(x => x.Unit).ToArray();
rng = xlsht.Range[xlsht.Cells[_row + 2, 1], xlsht.Cells[_row + p_item_name.Length + 1, 1]];
rng.Value = excel.WorksheetFunction.Transpose(p_item_name);
rng = xlsht.Range[xlsht.Cells[_row + 2, 3], xlsht.Cells[_row + p_item_name.Length + 1, 3]];
rng.Value = excel.WorksheetFunction.Transpose(p_item_unit);
for (int i = _row + 1; i < _row + p_item_name.Length + 2; i++)
{
rng = xlsht.Range[xlsht.Cells[i, 1], xlsht.Cells[i, 2]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 3], xlsht.Cells[i, 4]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 5], xlsht.Cells[i, 8]];
rng.Merge();
rng = xlsht.Range[xlsht.Cells[i, 9], xlsht.Cells[i, 12]];
rng.Merge();
}
rng = xlsht.Range[xlsht.Cells[_row + 1, 1], xlsht.Cells[_row + p_item_name.Length + 1, 12]];
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
//
// 版面設定
//
try
{
((Excel._Worksheet)excel.ActiveSheet).PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.TopMargin = excel.InchesToPoints(0.59);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.BottomMargin = excel.InchesToPoints(0.59);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.LeftMargin = excel.InchesToPoints(0.354);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.RightMargin = excel.InchesToPoints(0.354);
((Excel._Worksheet)excel.ActiveSheet).PageSetup.CenterHorizontally = true;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.Zoom = false;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.FitToPagesWide = 1;
((Excel._Worksheet)excel.ActiveSheet).PageSetup.FitToPagesTall = false;
}
catch (Exception ex)
{
//針對 lcy 電腦設置的防呆機制..當使用 CutePDF Writer 導致無法使用 PageSetup 屬性
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable table = (DataTable)ViewState["CloneTable"];
if (table == null)
{
MessageBox.Show(this, "数据表不存在！");
return;
}
if (table.Rows.Count < 3)
{
MessageBox.Show(this, "不能低于3条数据！");
return;
}
string customerId = this.ddlCustomer.SelectedValue;
string chartTitle = this.txtChartTitle.Text.Trim().Replace("#", "");
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string SheetName = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[SheetName];
worksheet.Activate();
//写入Excel列头
int i = 0;
int columnsCount = table.Columns.Count;
for (int j = 0; j < columnsCount; j++)
{
if (j > 1)
{
i++;
var letter = CommonUtility.GetLetters()[i];
Excel.Range range = worksheet.get_Range(letter + "1");
range.Value2 = table.Columns[j].ColumnName;
}
}
//写入Excel数据
int a = 1;
foreach (DataRow row in table.Rows)
{
a++;
for (int b = 1; b < columnsCount; b++)
{
Excel.Range range = worksheet.get_Range(CommonUtility.GetLetters()[b - 1] + a);
range.Value2 = row[b].ToString();
}
}
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xlLine, missing, missing, missing, missing);
Excel.Range sourceRange = worksheet.get_Range("A1", CommonUtility.GetLetters()[columnsCount - 2] + a);
//设置数据源
shape1.Chart.SetSourceData(sourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle;
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
Excel.Series se = shape1.Chart.SeriesCollection(1);
se.Format.Line.Visible = MsoTriState.msoTrue;
se.Format.Line.Transparency = 0.0f;
se.Format.Line.Weight = 2.25f;
se = shape1.Chart.SeriesCollection(2);
se.Format.Line.ForeColor.RGB = Color.FromArgb(0, 0, 192).ToArgb();
se.Format.Line.Visible = MsoTriState.msoTrue;
se.Format.Line.Transparency = 0.0f;
se.Format.Line.Weight = 2.25f;
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G8").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
shape1.Chart.PlotArea.Format.Fill.Visible = MsoTriState.msoTrue;
shape1.Chart.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(218, 234, 253).ToArgb();
shape1.Chart.PlotArea.Format.Fill.Transparency = 0;
shape1.Chart.PlotArea.Format.Fill.Solid();
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
using (var context = new ChartContext())
{
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "trendCompare"
};
StringBuilder sBuilder = new StringBuilder();
foreach (ListItem item in this.lstCategories.Items)
{
sBuilder.AppendFormat("{0},", item.Value);
}
string legendString = sBuilder.ToString().TrimEnd(',');
Legend legend = new Legend()
{
LegendName = legendString
};
entity.Legends.Add(legend);
StringBuilder legendDataBuilder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
for (int c = 0; c < table.Columns.Count; c++)
{
if (c > 0)
{
if (c == 1)
{
legendDataBuilder.AppendFormat("{0}+", row[c].ToString());
}
else
{
legendDataBuilder.AppendFormat("{0},", row[c].ToString());
}
}
}
legendDataBuilder.Append("|");
}
string strValue = legendDataBuilder.ToString().TrimEnd('|');
foreach (string str in strValue.Split('|'))
{
LegendData legendData = new LegendData()
{
DateString = str.TrimEnd(',').Split('+')[0],
LegendValue = str.TrimEnd(',').Split('+')[1]
};
legend.LegendDatas.Add(legendData);
}
context.Gathers.Add(entity);
context.SaveChanges();
}
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string customerId = this.ddlCustomer.SelectedValue;
string filePath = Server.MapPath("~/template_files/template_workbook.xlsx");
string chartTitle = this.txtChartTitle.Text.Replace("#", "");
using (var context = new ChartContext())
{
DataTable table = (DataTable)ViewState["table"];
DataTable table1 = (DataTable)ViewState["table1"];
if (this.ddlChartType.SelectedValue == "1")
{
var rows = table.Select("Quantity <> '0'");
if (rows.Count() == 0)
{
MessageBox.Show(this, "图表数据不能为空");
return;
}
}
if (this.ddlChartType.SelectedValue == "2")
{
var rows = table1.Rows;
if (rows.Count == 0)
{
MessageBox.Show(this, "图表数据不能为空");
return;
}
}
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string sheetname = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[sheetname];
worksheet.Activate();
List <GatherTrend> gatherTrends = new List <GatherTrend>();
List <int> listElements = new List <int>();
int i = 0;
string chartCategory = this.ddlChartType.SelectedValue;
switch (chartCategory)
{
case "1":
table = (DataTable)ViewState["table"];
string dateString = string.Empty;
foreach (DataRow row in table.Rows)
{
if (row["Quantity"].ToString() != "0")
{
if (this.rbYearMonth.Checked == true)
{
dateString = DateTime.Parse(row["Date"].ToString()).ToString("yyyy年M月");
}
else if (this.rbMonthDay.Checked == true)
{
dateString = DateTime.Parse(row["Date"].ToString()).ToString("M月d日");
}
i++;
Excel.Range rangeA = worksheet.get_Range("A" + i);
rangeA.Value = dateString;
Excel.Range rangeB = worksheet.get_Range("B" + i);
rangeB.Value = row["Quantity"].ToString();
listElements.Add(row["Quantity"].ToInt32());
GatherTrend trend = new GatherTrend
{
Name = dateString,
Number = row["Quantity"].ToInt32()
};
gatherTrends.Add(trend);
}
}
break;
case "2":
table1 = (DataTable)ViewState["table1"];
foreach (DataRow row in table1.Rows)
{
i++;
Excel.Range rangeA = worksheet.get_Range("A" + i);
rangeA.Value = row["Issue"];
Excel.Range rangeB = worksheet.get_Range("B" + i);
rangeB.Value = row["Quantity"].ToString();
listElements.Add(row["Quantity"].ToInt32());
}
break;
}
Excel.Range dataSourceRange = worksheet.get_Range("A1", "B" + i);
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xlLineMarkers, missing, missing, missing, missing);
//设置数据源
shape1.Chart.SetSourceData(dataSourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle.Replace("#", "");
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
//设置Legend
if (this.rblDataLabels.SelectedValue == "1")
{
shape1.Chart.ApplyDataLabels();
}
else if (this.rblDataLabels.SelectedValue == "2")
{
int maxIndex = listElements.IndexOf(listElements.Max()) + 1;
int minIndex = listElements.IndexOf(listElements.Min()) + 1;
shape1.Chart.SeriesCollection(1).Points(maxIndex).ApplyDataLabels();
shape1.Chart.SeriesCollection(1).Points(minIndex).ApplyDataLabels();
}
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G6").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.PlotArea.Format.Fill.Visible = MsoTriState.msoTrue;
shape1.Chart.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(218, 234, 253).ToArgb();
shape1.Chart.PlotArea.Format.Fill.Transparency = 0;
shape1.Chart.PlotArea.Format.Fill.Solid();
//shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
shape1.Chart.HasLegend = false;
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "trend"
};
gatherTrends.ForEach(item =>
{
entity.GatherTrends.Add(item);
});
context.Gathers.Add(entity);
context.SaveChanges();
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
InitRepeater3();
}
catch (Exception ex)
{
MessageBox.ShowAndRedirect(this, "生成图表是出错：" + ex.Message, Request.RawUrl);
}
}
}
/*/
* //int xtimer;
*
* private int timer
* {
* get
* {
* return Environment.TickCount - xtimer;
* }
* set
* {
* xtimer = Environment.TickCount + value;
* }
* }
* /*/
private void Report(string YEAR)
{
string saveStatus = statusLabel.Text;
int NY = 0;
int[] YearIndex = new int[100];
int[] colors = { 17, 38, 35, 39 }; // Бюджеты {15455409, 12303591, 12445144, 14731988};
//System.IO.StreamWriter LogFile;
int[] progressTArr1 = { 34, 9, 7, 20, 5, 22, 4, 30, 25 };
int[] progressTArr2 = { 4, 10, 2, 13, 6, 12 };
List <string> budgets = new List <string>();
budgets.AddRange(new string[] {
"Сумма",
"Федеральный",
"Региональный",
"Местный",
"Прочее"
});
List <string> reasons = new List <string>();
reasons.AddRange(new string[] {
"Плановая проверка",
"По заданию (постановлению, распоряжению) Правительства РТ",
"По обращению правоохранительных органов",
"По обращениям граждан, включая обращения должностных лиц и бюджетных учреждений и организаций"
});
List <string> violations = new List <string>();
violations.AddRange(new string[] {
"Недостача денежных средств",
"Недостача ТМЦ",
"Излишки денежных средств",
"Излишки ТМЦ",
"Нецелевые расходы",
"Неэффективное использование бюджетных средств",
"Неправильное списание, расходование денежных средств",
"Неправильное списание, расходование материальных запасов, в том числе ГСМ",
"Неправильная выплата (переплата) зарплаты, авансов, премий, отпускных, мат.помощи",
"Недоплата зарплаты, премий, мат.помощи",
"Недопоступление доходов",
"Завышение СМР",
"Прочие Финансовые нарушения",
"Прочие Нефинансовые нарушения",
"Нарушения Федерального закона №94-ФЗ"
});
List <string> total_info_data_names = new List <string>();
total_info_data_names.AddRange(new string[]
{
"Общий объем проверенных средств",
"Общая сумма нарушений",
"В том числе, нецелевые",
"Финансовые нарушения",
"Прочие Нефинансовые нарушения",
"Нарушения 94-ФЗ",
"Устранено нарушений",
"Неустранимые",
"Не устранено",
"Зачет нецелевых",
"Возмещено в бюджет",
"Наложено штрафов",
"Уплачено штрафов"
});
// Выбираем нужные годы из базы, составляем список и массив индексов. Для удобства NY сохраняет количество годов + 1
List <int> Years = new List <int>();
try
{
MySQL_cmd.CommandText = "SELECT _year FROM violations WHERE inspection IN " +
"(SELECT _id FROM inspections WHERE YEAR(_date)=" + YEAR + ") GROUP BY _year ORDER BY _year ASC";
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (NY = 1; MySQL_Reader.Read(); NY++)
{
int year = MySQL_Reader.GetInt32("_year");
Years.Add(year);
YearIndex[year - 1994] = NY;
}
MySQL_Reader.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Ошибка MySQL");
}
//Выбираем количество протоколов за год
int CNT = 1;
try
{
MySQL_cmd.CommandText = "SELECT COUNT(_id) FROM inspections WHERE YEAR(_date)=" + YEAR;
CNT = int.Parse(MySQL_cmd.ExecuteScalar().ToString());
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Ошибка MySQL");
}
statusLabel.Text = "Экспорт в Excel: ";
progressBar.Visible = true;
progressBar.Value = 0;
progressBar.Maximum = 0;
for (int i = 0; i < progressTArr1.Length; i++)
{
progressBar.Maximum += progressTArr1[i];
}
for (int i = 0; i < progressTArr2.Length; i++)
{
progressBar.Maximum += progressTArr2[i] * (CNT + 1);
}
progressBar.Maximum -= progressTArr2[0];
progressBar.Maximum -= progressTArr2[1];
try
{
// Открываем лог файл
/*/
* try
* {
* LogFile = new System.IO.StreamWriter("A:\\auditdc_report.log", true);
* LogFile.WriteLine("---=* " + DateTime.Now.ToString() + " *=----------------");
* }
* catch (Exception ex)
* {
* MessageBox.Show(ex.Message, "Ошибка записи лога");
* }
* /*/
// Открываем Excel
//LogFile.WriteLine("Открываем Excel"); timer = 0;
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
ExcelApp.SheetsInNewWorkbook = 3;
progressBar.Value += progressTArr1[0];
//LogFile.WriteLine(timer);
// Добавляем книгу
//LogFile.WriteLine("Добавляем книгу"); timer = 0;
ExcelApp.Workbooks.Add();
ExcelApp.Calculation = XlCalculation.xlCalculationManual;
Excel.Workbook Book = ExcelApp.Workbooks[1];
Excel._Worksheet Sheet = Book.Worksheets[1];
Excel.Range Cells = Sheet.Cells;
Excel.Range Cell = Cells.Item[1, 1];
progressBar.Value += progressTArr1[1];
//LogFile.WriteLine(timer);
// -------!!!------- Лист1 -------!!!------- //
Sheet.Name = "Протоколы" + YEAR;
Sheet.Activate();
// Ячейки по умолчанию
//LogFile.WriteLine("Форматируем ячейки по умолчанию"); timer = 0;
Cells.Font.Size = 8;
Cells.Font.Name = "Times New Roman";
Cells.VerticalAlignment = -4108;
Cells.RowHeight = 16;
Cells.WrapText = true;
Cells.ColumnWidth = 10.2;
Cells.NumberFormat = defaultFormat;
progressBar.Value += progressTArr1[2];
//LogFile.WriteLine(timer);
// --- ШАПКА --------------------------------------------------------------------------=* !!!!!!!!!!!!!!!!!!!!
// Высота шапки + 1 для удобства
int HeadHeight = 4;
// Высота второй строки 24 (по умолчанию 16)
Cells.Item[2, 1].RowHeight = 24;
// Строка с годами из нешего списка
//LogFile.WriteLine("Выводим строку с годами");
for (int i = 0; i < 19; i++)
{
Cells.Item[3, 8 + i * NY].Value = "Всего";
for (int j = 0; j < Years.Count; j++)
{
Cell = Cells.Item[3, 9 + i * NY + j];
Cell.Value = Years[j];
Cell.NumberFormat = "####";
}
}
// Объединение пустот (там где нет годов - пустоты)
Sheet.Range["A3:G3"].Merge();
Sheet.Range[ToLetter(19 * NY + 8) + "3:" + ToLetter(19 * NY + 16) + "3"].Merge();
progressBar.Value += progressTArr1[3];
//LogFile.WriteLine(timer);
// Статичные заголовки
//LogFile.WriteLine("Статичные заголовки"); timer = 0;
// Объединяем первые семь пар ячеек (по вертикали: строки 1 и 2) для заголовков полей
for (int i = 1; i < 8; i++)
{
Sheet.Range[ToLetter(i) + "1:" + ToLetter(i) + "2"].Merge();
}
Cell = Cells.Item[1, 1];
Cell.Value = "№ п/п";
Cell.ColumnWidth = 7;
Cells.Item[1, 2] = "№ дела";
Cell = Cells.Item[1, 3];
Cell.Value = "Наименование проверки";
Cell.ColumnWidth = 40;
Cell = Cells.Item[1, 4];
Cell.Value = "Основание проверки";
Cell.ColumnWidth = 20;
Cell = Cells.Item[1, 5];
Cell.Value = "Руководитель ревизионной группы";
Cell.ColumnWidth = 20;
Cell = Cells.Item[1, 6];
Cell.Value = "Проверяемый период";
Cell.ColumnWidth = 20;
Cell = Cells.Item[1, 7];
Cell.Value = "Уровни бюджетов";
Cell.ColumnWidth = 10;
progressBar.Value += progressTArr1[4];
//LogFile.WriteLine(timer);
// Выводим заголовки данных
//LogFile.WriteLine("Выводим заголовки данных");
Cells.Item[1, 8].Value = "Общий объем проверенных средств";
Cells.Item[1, 8 + NY].Value = "Общая сумма нарушений";
for (int i = 2; i < 17; i++)
{
Cell = Cells.Item[1, 8 + i * NY];
Cell.Interior.ColorIndex = 20;
Cell.Value = violations[i - 2];
}
for (int i = 0; i < 19; i++)
{
Sheet.Range[ToLetter(i * NY + 8) + "1:" + ToLetter(i * NY + 8 + NY - 1) + "2"].Merge();
}
Cell = Cells.Item[1, 17 * NY + 8];
Cell.Value = "Всего финансовых нарушений";
Cell.Interior.ColorIndex = 36;
Cell = Cells.Item[1, 18 * NY + 8];
Cell.Value = "Всего нефинансовых нарушений";
Cell.Interior.ColorIndex = 36;
Sheet.Range[ToLetter(19 * NY + 8) + "1:" + ToLetter(19 * NY + 10) + "1"].Merge();
Cells.Item[1, 19 * NY + 8].Value = "Устранено нарушений";
Cells.Item[2, 19 * NY + 8].Value = "Всего";
Cells.Item[2, 19 * NY + 9].Value = "Зачет нецелевых";
Cells.Item[2, 19 * NY + 10].Value = "Возмещено в бюджеты";
for (int j = 0; j < 6; j++)
{
Sheet.Range[ToLetter(19 * NY + 11 + j) + "1:" + ToLetter(19 * NY + 11 + j) + "2"].Merge();
}
Cell = Cells.Item[1, 19 * NY + 11];
Cell.Value = "Внесены изменения в РЦП";
Cell.ColumnWidth = 12;
Cell = Cells.Item[1, 19 * NY + 12];
Cell.Value = "Неустранимые";
Cell.ColumnWidth = 12;
Cell = Cells.Item[1, 19 * NY + 13];
Cell.Value = "Не устранено";
Cell.ColumnWidth = 12;
Cell = Cells.Item[1, 19 * NY + 14];
Cell.Value = "Наложены штрафы";
Cell.ColumnWidth = 12;
Cell = Cells.Item[1, 19 * NY + 15];
Cell.Value = "Уплачены штрафы";
Cell.ColumnWidth = 12;
Cell = Cells.Item[1, 19 * NY + 16];
Cell.Value = "Примечание";
Cell.ColumnWidth = 56;
progressBar.Value += progressTArr1[5];
//LogFile.WriteLine(timer);
// Форматирование всей шапки
//LogFile.WriteLine("Форматирование всей шапки"); timer = 0;
Cell = Sheet.Range["A1:" + ToLetter(19 * NY + 16) + "3"];
Cell.Font.Bold = true;
Cell.HorizontalAlignment = -4108;
Cell.Borders[XlBordersIndex.xlEdgeRight].Weight = 3;
Cell.Borders[XlBordersIndex.xlInsideHorizontal].Weight = 3;
Cell.Borders[XlBordersIndex.xlInsideVertical].Weight = 2;
progressBar.Value += progressTArr1[6];
//LogFile.WriteLine(timer);
//--- КОНЕЦ ШАПКИ ---------------------------------------------------------------------=* !!!!!!!!!!!!!!!!!!!!
//LogFile.WriteLine("---------------");
// Загрузим все проверки за год
MySQL_cmd.CommandText = "SELECT inspections._id as _id, inspections.protocol as proton, inspections.name as name, reasons.name as reason, " +
"CONCAT_WS(' ', inspectors.surname, inspectors.name, inspectors.patronymic) as fio, inspections.period_begin as pb, inspections.period_end as pe, inspections._comment as cmt " +
"FROM inspections, reasons, inspectors " +
"WHERE inspections.reason = reasons._id AND inspections.director = inspectors._id AND YEAR(inspections._date)=" + YEAR +
" ORDER BY _id";
MySQL_Reader = MySQL_cmd.ExecuteReader();
// ID проверок сохраним в список
List <int> inspections = new List <int>();
// Вывод общей информации по проверке
for (int xx = 0; MySQL_Reader.Read(); xx++)
{
//LogFile.WriteLine("Вывод общей информации по проверке " + xx); timer = 0;
inspections.Add(MySQL_Reader.GetInt32("_id"));
for (int i = 1; i < 7; i++)
{
Cell = Sheet.Range[ToLetter(i) + (HeadHeight + xx * 5).ToString() + ":" + ToLetter(i) + (HeadHeight + xx * 5 + 4).ToString()];
Cell.Merge();
Cell.NumberFormat = "####";
}
Cells.Item[HeadHeight + xx * 5, 1].Value = MySQL_Reader["proton"];
for (int i = 2; i < 5; i++)
{
Cells.Item[HeadHeight + xx * 5, i + 1].Value = MySQL_Reader[i];
}
Cells.Item[HeadHeight + xx * 5, 6].Value = "c " + MySQL_Reader.GetDateTime("pb").ToShortDateString() + " по " + MySQL_Reader.GetDateTime("pe").ToShortDateString();
Cell = Sheet.Range[ToLetter(19 * NY + 16) + (HeadHeight + xx * 5).ToString() + ":" + ToLetter(19 * NY + 16) + (HeadHeight + xx * 5 + 4).ToString()];
Cell.Merge();
Cell.Value = MySQL_Reader["cmt"];
progressBar.Value += progressTArr2[0];
//LogFile.WriteLine(timer);
}
MySQL_Reader.Close();
// А теперь для каждой проверки в списке загрузим данные
for (int xx = 0; xx <= inspections.Count; xx++)
{
// Данные протокола
if (xx != inspections.Count)
{
//LogFile.WriteLine("Данные протокола " + xx); timer = 0;
// Данные группа 1
MySQL_cmd.CommandText = "SELECT * FROM violations WHERE inspection=" + inspections[xx];
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (int i = 0; MySQL_Reader.Read(); i++)
{
int budget = MySQL_Reader.GetInt32("budget_lvl");
int year = MySQL_Reader.GetInt32("_year");
Cell = Cells.Item[HeadHeight + xx * 5 + budget, 8 + YearIndex[year - 1994]];
Cell.Value = MySQL_Reader.GetDouble(18);
for (int j = 2; j < 17; j++)
{
Cell = Cells.Item[HeadHeight + xx * 5 + budget, 8 + YearIndex[year - 1994] + j * NY];
Cell.Value = MySQL_Reader.GetDouble(j + 1);
}
}
MySQL_Reader.Close();
// Данные группа 2
MySQL_cmd.CommandText = "SELECT * FROM eliminations WHERE inspection=" + inspections[xx];
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (int i = 0; MySQL_Reader.Read(); i++)
{
for (int j = 2; j < 9; j++)
{
int budget = MySQL_Reader.GetInt32("budget_lvl");
Cell = Cells.Item[HeadHeight + xx * 5 + budget, 7 + NY * 19 + j];
Cell.Value = MySQL_Reader.GetDouble(j);
}
}
MySQL_Reader.Close();
progressBar.Value += progressTArr2[1];
//LogFile.WriteLine(timer);
}
// Все что ниже применяется и к ИТОГО
// цвета бюджетов
//LogFile.WriteLine("Бюджеты"); timer = 0;
for (int i = 0; i < 4; i++)
{
Cell = Sheet.Range["G" + (xx * 5 + i + 5) + ":" + ToLetter(19 * NY + 15) + (xx * 5 + i + 5)];
Cell.Interior.ColorIndex = colors[i];
}
for (int i = 0; i < 5; i++)
{
Cell = Cells.Item[HeadHeight + xx * 5 + i, 7];
Cell.Value = budgets[i];
}
progressBar.Value += progressTArr2[2];
//LogFile.WriteLine(timer);
// формулы СУММ #ИМЯ??? чеза!??!... видимо, плохо пережевывает русский UTF... SUM работает нормально
//LogFile.WriteLine("Формулы СУММ по годам и по бюджетам для нарушений"); timer = 0;
for (int i = 0; i < 19; i++)
{
for (int j = 0; j < 5; j++)
{
Cell = Cells.Item[HeadHeight + xx * 5 + j, 8 + i * NY];
Cell.Formula = "=SUM(" + ToLetter(9 + i * NY) + (HeadHeight + xx * 5 + j) + ":" + ToLetter(7 + i * NY + NY) + (HeadHeight + xx * 5 + j) + ")";
}
for (int j = 1; j < NY; j++)
{
Cell = Cells.Item[HeadHeight + xx * 5, 8 + i * NY + j];
Cell.Formula = "=SUM(" + ToLetter(8 + i * NY + j) + (xx * 5 + 5) + ":" + ToLetter(8 + i * NY + j) + (xx * 5 + 8) + ") ";
}
}
progressBar.Value += progressTArr2[3];
//LogFile.WriteLine(timer);
// Формулы для общей суммы нарушений и сумм финансовых/нефинансовых нарушений
//LogFile.WriteLine("Формулы для общей суммы нарушений и сумм финансовых/нефинансовых нарушений"); timer = 0;
for (int i = 1; i < NY; i++)
{
for (int j = 1; j < 5; j++)
{
Cell = Cells.Item[HeadHeight + xx * 5 + j, 8 + NY * 17 + i];
string tmp = "=";
for (int k = 2; k < 15; k++)
{
if (k != 2)
{
tmp += "+";
}
tmp += ToLetter(8 + NY * k + i) + (xx * 5 + 4 + j);
}
Cell.Value = tmp;
Cell = Cells.Item[HeadHeight + xx * 5 + j, 8 + 18 * NY + i];
Cell.Value = "=" + ToLetter(8 + NY * 15 + i) + (xx * 5 + 4 + j) + "+" + ToLetter(8 + NY * 16 + i) + (xx * 5 + 4 + j);
Cell = Cells.Item[HeadHeight + xx * 5 + j, 8 + NY + i];
Cell.Value = "=" + ToLetter(8 + NY * 17 + i) + (xx * 5 + 4 + j) + "+" + ToLetter(8 + NY * 18 + i) + (xx * 5 + 4 + j);
}
}
for (int i = 0; i < 8; i++)
{
Cell = Cells.Item[HeadHeight + xx * 5, 8 + 19 * NY + i];
Cell.Value = "=SUM(" + ToLetter(8 + 19 * NY + i) + (xx * 5 + 5) + ":" + ToLetter(8 + 19 * NY + i) + (xx * 5 + 8) + ")";
}
progressBar.Value += progressTArr2[4];
//LogFile.WriteLine(timer);
// Всего устранено нарушений
//LogFile.WriteLine("Формулы \"Всего устранено нарушений\"");
for (int i = 0; i < 4; i++)
{
Cell = Cells.Item[xx * 5 + 5 + i, 8 + 19 * NY];
Cell.Value = "=" + ToLetter(9 + 19 * NY) + (xx * 5 + 5 + i) + "+" + ToLetter(10 + 19 * NY) + (xx * 5 + 5 + i);
}
Cell = Sheet.Range["A" + (xx * 5 + 4) + ":" + ToLetter(19 * NY + 16) + (xx * 5 + 8)];
Cell.Borders[XlBordersIndex.xlEdgeTop].Weight = 3;
Cell.Borders[XlBordersIndex.xlEdgeRight].Weight = 3;
Cell.Borders[XlBordersIndex.xlInsideVertical].Weight = 2;
Cell.Borders[XlBordersIndex.xlInsideHorizontal].Weight = 2;
progressBar.Value += progressTArr2[5];
//LogFile.WriteLine(timer);
}
Cell.Borders[XlBordersIndex.xlEdgeBottom].Weight = 3;
// ИТОГО
//LogFile.WriteLine("---- ИТОГО ----"); timer = 0;
Cell = Sheet.Range["A" + (HeadHeight + inspections.Count * 5).ToString() + ":" + "F" + (HeadHeight + inspections.Count * 5 + 4).ToString()];
Cell.Merge();
Cell.Font.Bold = true;
Cell.HorizontalAlignment = -4108;
Cell.Value = "Итого за " + YEAR + " год";
Cell = Sheet.Range[ToLetter(NY * 19 + 16) + (HeadHeight + inspections.Count * 5) + ":" + ToLetter(NY * 19 + 16) + (HeadHeight + inspections.Count * 5 + 4)];
Cell.Merge();
MySQL_cmd.CommandText = "SELECT budget_lvl, _year, SUM(d1), SUM(d2), SUM(d3), SUM(d4), SUM(d5), SUM(d6), SUM(d7), SUM(d8), SUM(d9), SUM(d10), SUM(d11), SUM(d12), SUM(d13), SUM(d14), SUM(d15), SUM(d16) FROM violations WHERE inspection IN (SELECT _id FROM inspections WHERE YEAR(_date)=" + YEAR + ") GROUP BY budget_lvl, _year";
MySQL_Reader = MySQL_cmd.ExecuteReader();
while (MySQL_Reader.Read())
{
int budget = MySQL_Reader.GetInt32("budget_lvl");
int year = MySQL_Reader.GetInt32("_year");
Cell = Cells.Item[HeadHeight + inspections.Count * 5 + budget, 8 + YearIndex[year - 1994]];
Cell.Value = MySQL_Reader.GetDouble(17);
for (int j = 2; j < 17; j++)
{
Cell = Cells.Item[HeadHeight + inspections.Count * 5 + budget, 8 + YearIndex[year - 1994] + j * NY];
Cell.Value = MySQL_Reader.GetDouble(j);
}
}
MySQL_Reader.Close();
MySQL_cmd.CommandText = "SELECT SUM(d1), SUM(d2), SUM(d3), SUM(d4), SUM(d5), SUM(d6), SUM(d7) FROM eliminations WHERE inspection IN (SELECT _id FROM inspections WHERE YEAR(_date)=" + YEAR + ") GROUP BY budget_lvl ORDER BY budget_lvl";
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (int i = 1; MySQL_Reader.Read(); i++)
{
for (int j = 0; j < 7; j++)
{
Cell = Cells.Item[HeadHeight + inspections.Count * 5 + i, 9 + 19 * NY + j];
Cell.Value = MySQL_Reader.GetDouble(j);
}
}
MySQL_Reader.Close();
progressBar.Value += progressTArr1[7];
//Заморозка шапки
Cell = Cells.Item[4, 8];
Cell.Select();
ExcelApp.ActiveWindow.FreezePanes = true;
//LogFile.WriteLine(timer);
/* Лист 3: цвета на выбор */
/*/
* Sheet = Book.Worksheets.Item[3];
* Sheet.Name = "Цвета";
* Sheet.Activate();
* for (int i = 1; i < 57; i++)
* {
* Cell = Sheet.Range["A" + i + ":" + "A" + i];
* Cell.Interior.ColorIndex = i;
* Cell.Value = i;
* }
* /**/
// -------!!!------- Лист2 -------!!!------- //
//LogFile.WriteLine("-----------");
//LogFile.WriteLine("Лист второй"); timer = 0;
Sheet = Book.Worksheets.Item[2];
Cells = Sheet.Cells;
Sheet.Name = "Отчет" + YEAR;
Sheet.Activate();
// Формат ячеек по умолчанию
Cells.Font.Size = 8;
Cells.Font.Name = "Times New Roman";
Cells.RowHeight = 18; //16?
Cells.ColumnWidth = 10.2;
Cells.VerticalAlignment = -4108;
Cells.NumberFormat = defaultFormat;
// ------- Заголовок
Cell = Sheet.Range["A1:N1"];
Cell.Merge();
Cell.Borders[XlBordersIndex.xlEdgeBottom].Weight = 3;
Cell = Cells.Item[1, 1];
Cell.RowHeight = 30;
Cell.HorizontalAlignment = -4108;
Cell.VerticalAlignment = -4108;
Cell.Font.Size = 15;
Cell.Font.Bold = true;
Cell.Value = "ОТЧЕТ СЛУЖБЫ ПО ФИНАНСОВО-БЮДЖЕТНОМУ НАДЗОРУ РЕСПУБЛИКИ ТЫВА ЗА " + YEAR + " ГОД";
// ------- Таблица1
for (int i = 3; i < 8; i++)
{
Cell = Sheet.Range["A" + i + ":G" + i];
Cell.Merge();
}
//Данные
for (int i = 0; i < 4; i++)
{
Cell = Cells.Item[i + 3, 1];
Cell.Value = reasons[i];
}
MySQL_cmd.CommandText = "SELECT reason, count(_id) as cnt FROM inspections WHERE YEAR(_date)=" + YEAR + " GROUP BY reason";
MySQL_Reader = MySQL_cmd.ExecuteReader();
while (MySQL_Reader.Read())
{
Cell = Cells.Item[2 + MySQL_Reader.GetInt32("reason"), 8];
Cell.Value = MySQL_Reader["cnt"];
}
MySQL_Reader.Close();
Cell = Cells.Item[7, 1];
Cell.Value = "ВСЕГО в " + YEAR + "г. проведено проверок:";
Cell = Cells.Item[7, 8];
Cell.Value = "=SUM(H3:H6)";
//Рамка
Cell = Sheet.Range["A3:H7"];
for (int i = 7; i < 11; i++)
{
Cell.Borders[Border(i)].Weight = 3;
}
Cell.Borders[XlBordersIndex.xlInsideHorizontal].Weight = 2;
Cell.Borders[XlBordersIndex.xlInsideVertical].Weight = 2;
// ------- Таблица 2
//Данные
string q1 = "SELECT SUM(d1), SUM(d2), SUM(d3), SUM(d4), SUM(d5), SUM(d6), SUM(d7)";
string q2 = ", SUM(d8), SUM(d9), SUM(d10), SUM(d11), SUM(d12), SUM(d13), SUM(d14), SUM(d15), SUM(d16) FROM violations";
string q3 = " FROM eliminations";
string q4 = " WHERE inspection IN (SELECT _id FROM inspections WHERE YEAR(_date)=" + YEAR + ") GROUP BY budget_lvl";
for (int i = 1; i < 5; i++)
{
Cells.Item[9 + i, 1].Value = budgets[i];
}
MySQL_cmd.CommandText = q1 + q2 + q4;
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (int i = 0; MySQL_Reader.Read(); i++)
{
Cells.Item[10 + i, 2].Value = MySQL_Reader.GetDouble(15);
double sum = 0;
int j;
for (j = 3; j < 6; j++)
{
sum += MySQL_Reader.GetDouble(j);
}
Cells.Item[10 + i, 3].Value = sum;
Cells.Item[10 + i, 4].Value = MySQL_Reader.GetDouble(4);
for (j = 0, sum = 0; j < 13; j++)
{
sum += MySQL_Reader.GetDouble(j);
}
Cells.Item[10 + i, 5].Value = sum;
Cells.Item[10 + i, 6].Value = MySQL_Reader.GetDouble(13);
Cells.Item[10 + i, 7].Value = MySQL_Reader.GetDouble(14);
}
MySQL_Reader.Close();
MySQL_cmd.CommandText = q1 + q3 + q4;
MySQL_Reader = MySQL_cmd.ExecuteReader();
for (int i = 0; MySQL_Reader.Read(); i++)
{
Cells.Item[10 + i, 8].Value = MySQL_Reader.GetDouble(0) + MySQL_Reader.GetDouble(1);
Cells.Item[10 + i, 9].Value = MySQL_Reader.GetDouble(3);
Cells.Item[10 + i, 10].Value = MySQL_Reader.GetDouble(4);
Cells.Item[10 + i, 11].Value = MySQL_Reader.GetDouble(0);
Cells.Item[10 + i, 12].Value = MySQL_Reader.GetDouble(1);
Cells.Item[10 + i, 13].Value = MySQL_Reader.GetDouble(5);
Cells.Item[10 + i, 14].Value = MySQL_Reader.GetDouble(6);
}
MySQL_Reader.Close();
/* Пример как сделать систему более гибкой через БД, однако лучше через XML
* MySQL_cmd.CommandText = "SELECT name FROM total_info_data_names";
* MySQL_Reader = MySQL_cmd.ExecuteReader();
* for (int i = 0; MySQL_Reader.Read(); i++)
* {
* Cells.Item[9, i + 2].Value = MySQL_Reader.GetString("name");
* }
* MySQL_Reader.Close();
*/
for (int i = 0; i < total_info_data_names.Count; i++)
{
Cells.Item[9, i + 2].Value = total_info_data_names[i];
}
//Строчка формул сумм
Cells.Item[14, 1].Value = "Всего";
for (int i = 2; i < 15; i++)
{
Cells.Item[14, i].Value = "=SUM(" + ToLetter(i) + "10:" + ToLetter(i) + "13)";
}
//Размеры столбцов и строк, перенос слов
Cell = Sheet.Range["A9:N9"];
Cell.RowHeight = 40;
Cell.WrapText = true;
//Рамка
Cell = Sheet.Range["A9:N14"];
for (int i = 7; i < 11; i++)
{
Cell.Borders[Border(i)].Weight = 3;
}
Cell.Borders[XlBordersIndex.xlInsideHorizontal].Weight = 2;
Cell.Borders[XlBordersIndex.xlInsideVertical].Weight = 2;
progressBar.Value += progressTArr1[8];
//LogFile.WriteLine(timer);
//for (int i = 0; i < 8; i++) LogFile.WriteLine();
// Покажем что получилось
ExcelApp.Visible = true;
ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
finally
{
progressBar.Visible = false;
statusLabel.Text = saveStatus;
//LogFile.Close();
}
}
// A10: Number of classroom teachers by qualified and trained status, teaching level of education, type of institution and sex
void sheetA10(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
//const int FEMALE = 18; //row offset
//const int MALE = 17;
const int FEMALE_OFFSET = 1; //row offset
const int TRAINED_OFFSET = 10; //row offset
const int PUBLIC = 14;
const int PRIVATE = 17;
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A10"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED, SCHOOLTYPE, GENDER, QUALIFIED, TRAINED, sum(COUNT) as COUNT from #TeacherBaseTable
group by ISCED, SCHOOLTYPE, GENDER, QUALIFIED, TRAINED",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced = rdr.GetString(0);
string schooltype = rdr.GetString(1);
string gender = rdr.GetString(2);
string qualified = rdr.GetString(3);
string trained = rdr.GetString(4);
int count = rdr.GetInt32(5);
int row = (schooltype == "PUBLIC" ? PUBLIC : PRIVATE)
+ (gender == "F" ? FEMALE_OFFSET : 0)
+ (trained == "Y" ? TRAINED_OFFSET : 0);
List <string> columns = new List <string>();
if (isced == "ISCED 24" || isced == "ISCED 34")
{
columns.Add("ISCED 24+34");
columns.Add(isced.Substring(0, 7));
}
else if (isced == "ISCED 25" || isced == "ISCED 35")
{
columns.Add("ISCED 25+35");
columns.Add(isced.Substring(0, 7));
}
else
{
columns.Add(isced);
}
foreach (string column in columns)
{
workSheet.Cells[row, getCol(column)] = workSheet.get_Range(helpers.GetCellAddress(getCol(column), row)).Value2 + count;
}
}
}
}
public void ExcelCreate(String path, int day, Dictionary <int, String> em)
{
if (File.Exists(path))
{
excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.DisplayAlerts = false;
wBook = excelapp.Workbooks.Open(path, 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);
DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
DateTime date = DateTime.Now;
Calendar cal = dfi.Calendar;
bool found = false;
foreach (Excel.Worksheet sheet in wBook.Sheets)
{
if (sheet.Name == ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek)))
{
found = true;
}
}
if (found == true)
{
wSheet = wBook.Sheets[("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek))];
wSheet.Activate();
}
else
{
Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)wBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek));
wSheet = newWorksheet;
wSheet.Activate();
NameTagInit(excelapp);
}
Excel.Range rng = excelapp.Range[excelapp.Cells[1, day], excelapp.Cells[em.Count + 2, day]];
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Empty);
excelapp.get_Range("A1", "J99").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelapp.Cells[1, day] = DateTime.Now.ToString("ddd");
//1,2 = 週一，1,3 = 週二 ...
for (int i = 2; i < em.Count + 2; i++)
{
if (em.ElementAt(i - 2).Value.Equals("出席"))
{
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value;
}
else if (em.ElementAt(i - 2).Value.Equals("遲到"))
{
Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]];
rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = "出席";
}
else
{
Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]];
rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value;
}
}
try
{
wBook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + path);
}
catch
{
MessageBox.Show("儲存檔案出錯，檔案可能正在使用", "錯誤:檔案無法儲存", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
wBook.Close(false, Type.Missing, Type.Missing);
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp);
wBook = null;
wSheet = null;
excelapp = null;
GC.Collect();
Console.Read();
}
else
{
MessageBox.Show("檔案不存在，請確認路徑", "錯誤:路徑", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Excel.Application oXL = new Excel.Application();
#if DEBUG
oXL.Visible = true;
oXL.DisplayAlerts = false;
#else
oXL.Visible = false;
oXL.DisplayAlerts = false;
#endif
//Open the Excel File
String inputFile = @"D:\Excel\InsertChart.xlsx";
Excel.Workbook oWB = oXL.Workbooks.Open(inputFile);
String SheetName = "ExperimentSheet";
Excel._Worksheet oSheet = oWB.Sheets[SheetName];
oSheet.Activate();
//oSheet = oWB.ActiveSheet;
//oSheet = oWB.Sheets.[1];
//Insert an Empty Chart
//Excel.Chart chart1 = oSheet.Ch .AddChart(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Shape chart1 = oSheet.Shapes.AddChart(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Insert a basic 2-D bar graph
chart1.Chart.ChartType = Excel.XlChartType.xlColumnClustered;
//Set the Data Source for the chart
Excel.Range Rng;
Rng = oSheet.get_Range("A2", "C10");
chart1.Chart.SetSourceData(Rng, Type.Missing);
//Set the Chart Title
chart1.Chart.HasTitle = true;
chart1.Chart.ChartTitle.Text = "Population Growth";
//Set the y-axis
var yaxis = (Excel.Axis)chart1.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yaxis.HasTitle = true;
yaxis.AxisTitle.Text = "Growth";
yaxis.AxisTitle.Orientation = Excel.XlOrientation.xlVertical;
//Set the X-axis
var xaxis = (Excel.Axis)chart1.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xaxis.HasTitle = true;
xaxis.AxisTitle.Text = "Population";
yaxis.AxisTitle.Orientation = Excel.XlOrientation.xlHorizontal;
//Set the Legend
chart1.Chart.SeriesCollection(1).Name = (String)oSheet.get_Range("B1").Value2;
chart1.Chart.SeriesCollection(2).Name = (String)oSheet.get_Range("C1").Value2;
//Set the Size of the Chart
chart1.Width = 350;
chart1.Height = 350;
chart1.Left = (float)oSheet.get_Range("D1").Left;
chart1.Top = (float)oSheet.get_Range("D3").Top;
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
oSheet = null;
oWB = null;
oXL = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
catch (Exception ex)
{
String errorMessage = "Error : " + ex.Message;
MessageBox.Show(errorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
float theta = 35 + 90; //角度值 4f->64
private void check_uwb(byte Response1, byte Response2, int Check) //02,10,F3,02,4F,4B,E5,
{
RX_Counter = 0;
theta = Convert.ToInt32(textBox2.Text) + 90;
label32.Text = Convert.ToString(theta);
double radian = theta * Math.PI / 180;//轉換弧度值
//Thread.Sleep(1300);
//label10.Text = Response[0].ToString("X2") + " " + Response[1].ToString("X2") + " ";
if (Response[0] == Response1 && Response[1] == Response2)
{
label9.Text = "UWB OK";
/*
* label10.Text = Response[0].ToString("X2") + " " + Response[24].ToString("X2") + " " + Response[25].ToString("X2") + " " + Response[26].ToString("X2") + " ";
* float Tag_dis1 = ((Response[26] << 16) | (Response[25] << 8) | (Response[24] << 0)) / 256000.0f;
* label12.Text = Convert.ToString(Tag_dis1);*/
Anchor_x = ((Response[6] << 24) | (Response[5] << 16) | (Response[4] << 8) | (0x00 << 0)) / 256000.0f;
label26.Text = Convert.ToString(Anchor_x);
Anchor_y = ((Response[9] << 24) | (Response[8] << 16) | (Response[7] << 8) | (0x00 << 0)) / 256000.0f;
label27.Text = Convert.ToString(Anchor_y);
Anchor_z = ((Response[12] << 24) | (Response[11] << 16) | (Response[10] << 8) | (0x00 << 0)) / 256000.0f;
//Anchor_z = Anchor_z + 0.3;
label28.Text = Convert.ToString(Anchor_z);
if (Anchor_z < 1)
{
Anchor_z = Anchor_z + 0.2; //4f-> 0.3
}
//processFrameData();
if (send_flag)
{
long cur_ms = stopwatch.ElapsedMilliseconds;
MAVLink.mavlink_att_pos_mocap_t att_pos = new MAVLink.mavlink_att_pos_mocap_t();
//att_pos.time_usec = (ulong)(((DateTime.UtcNow - unix_epoch).TotalMilliseconds - 10) * 1000);
att_pos.time_usec = (ulong)(cur_ms * 1000);
//att_pos.x = Anchor_y; //north Anchor_y
//att_pos.y = Anchor_x; //east Anchor_x
att_pos.x = (float)(Anchor_x * Math.Cos(radian) + Anchor_y * Math.Sin(radian)); //north
att_pos.y = (float)-((-Anchor_x * Math.Sin(radian)) + Anchor_y * Math.Cos(radian)); //east
att_pos.z = (float)-Anchor_z; //down
Anchor_x = att_pos.x;
Anchor_y = att_pos.y;
Anchor_z = att_pos.z;
label7.Text = Convert.ToString(att_pos.x);
label13.Text = Convert.ToString(att_pos.y);
label22.Text = Convert.ToString(att_pos.z);
//att_pos.q = new float[4] { rbData.qw, rbData.qx, rbData.qz, -rbData.qy };
DroneData drone = drones["bebop2"];
drone.lost_count = 0;
byte[] pkt;
pkt = mavlinkParse.GenerateMAVLinkPacket20(MAVLink.MAVLINK_MSG_ID.ATT_POS_MOCAP, att_pos);
mavSock.SendTo(pkt, drone.ep);
}
// for (int i = 0; i < 23; i++ )
// richTextBox1.Text += Response[i].ToString("X2") + " ";
if (log_flag)
{
Log_Counter = Log_Counter + 1;
try
{
wSheet = (Excel._Worksheet)wBook.Worksheets[1]; // 引用第一個工作表
wSheet.Name = "UWB Sensor Value Log"; // 命名工作表的名稱
wSheet.Activate(); // 設定工作表焦點
//excelApp.Cells[1, 1] = "Excel測試";
excelApp.Cells[Log_Counter, 1] = Anchor_x; //att_pos.x
excelApp.Cells[Log_Counter, 2] = Anchor_y; //att_pos.y
excelApp.Cells[Log_Counter, 3] = Anchor_z; //att_pos.z
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
if (log_flag_set)
{
try
{
//另存活頁簿
wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
wBook.Close(false, Type.Missing, Type.Missing); //關閉活頁簿
excelApp.Quit(); //關閉Excel
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); //釋放Excel資源
wBook = null;
wSheet = null;
excelApp = null;
GC.Collect();
Console.Read();
log_flag = false;
log_flag_set = false;
Log_Counter = 0;
}
}
}
}
private void ExportToExcel(DataTable DataTable, string ExcelFilePath = null)
{ //https://stackoverflow.com/a/21079709
try
{
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
ExcelInterop.Application Excel = new ExcelInterop.Application();
Excel.Workbooks.Add();
// single worksheet
ExcelInterop._Worksheet Worksheet = Excel.ActiveSheet;
object[] Header = new object[ColumnsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
{
Header[i] = DataTable.Columns[i].ColumnName;
}
ExcelInterop.Range HeaderRange = Worksheet.get_Range((ExcelInterop.Range)(Worksheet.Cells[1, 1]), (ExcelInterop.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = ColorTranslator.ToOle(Color.Indigo);
HeaderRange.Font.Color = ColorTranslator.ToOle(Color.White);
HeaderRange.Font.Bold = true;
//https://stackoverflow.com/a/5060552
//Freeze header to lock on top when scrolling
Worksheet.Activate();
Worksheet.Application.ActiveWindow.SplitRow = 1;
Worksheet.Application.ActiveWindow.FreezePanes = true;
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];
for (int j = 0; j < RowsCount; j++)
{
for (int i = 0; i < ColumnsCount; i++)
{
Cells[j, i] = DataTable.Rows[j][i];
}
}
Worksheet.get_Range((ExcelInterop.Range)(Worksheet.Cells[2, 1]), (ExcelInterop.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
Worksheet.Cells.Style.HorizontalAlignment = ExcelInterop.XlHAlign.xlHAlignLeft;
Worksheet.Cells.Style.VerticalAlignment = ExcelInterop.XlVAlign.xlVAlignTop;
Worksheet.Columns.AutoFit();
for (int i = 1; i <= ColumnsCount; i++)
{
if (Worksheet.Columns[i].ColumnWidth > 84)
{
Worksheet.Columns[i].ColumnWidth = 84;
}
}
Worksheet.Cells.WrapText = true;
Excel.Visible = true; //delete if auto-save logic is uncommented and applied
/*
* Code will go through the sheet, locate keywords searched for, turn them red, and set to bold.
* Works well except it slows down the export severely.
*/
//https://msdn.microsoft.com/en-us/library/e4x1k99a.aspx & https://stackoverflow.com/a/10401233
/* Excel.Range currentFind = null;
* Excel.Range firstFind = null;
* string text = "biopsy";
* // Find the first occurrence of the passed-in text
*
* currentFind = Worksheet.Cells.Find(text, Missing.Value, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
* Microsoft.Office.Interop.Excel.XlLookAt.xlPart, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
* Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,
* false, Missing.Value, Missing.Value);
*
* while (currentFind != null)
* {
* // Keep track of the first range we find
*
* if (firstFind == null)
* {
* firstFind = currentFind;
* }
* else if (currentFind.get_Address(Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1,
* Missing.Value, Missing.Value) ==
* firstFind.get_Address(Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1,
* Missing.Value, Missing.Value))
* {
* // We didn't move to a new range so we're done
*
* break;
* }
*
* // We know our text is in first cell of this range, so we need to narrow down its position
*
* string searchResult = currentFind.get_Range("A1").Value.ToString().ToLower();
* //int startPos = searchResult.IndexOf(text);
* List<int> positionList = Program.AllIndexesOf(searchResult, text);
* // Set the text in the cell to bold
* foreach (var position in positionList)
* {
* currentFind.get_Range("A1").Characters[position + 1, text.Length].Font.Bold = true;
* currentFind.get_Range("A1").Characters[position + 1, text.Length].Font.Color = ColorTranslator.ToOle(Color.Red);
* }
*
*
* // Move to the next find
*
* currentFind = Worksheet.Cells.FindNext(currentFind);
* } */
// check fielpath logic in case auto-save-to destination is provided
// if (ExcelFilePath != null && ExcelFilePath != "")
// {
// try
// {
// Worksheet.SaveAs(ExcelFilePath);
// Excel.Quit();
// MessageBox.Show("Excel file saved!");
// }
// catch (Exception ex)
// {
// throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
// + ex.Message);
// }
// }
// else // no filepath is given
// {
// Excel.Visible = true;
// }
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public void generatExcelNoSubmit(string f)
{
string file_Name = f;
m_oExcelApp = new Excel.Application();
m_oExcelApp.Visible = false;
m_oExcelApp.UserControl = false;
m_oSheet = null;
excelRange = null;
try
{
m_oBook = m_oExcelApp.Workbooks.Add(missing);
Excel.Sheets sheets = m_oBook.Worksheets;
//Add new 4 Sheet
//sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
//Product Sheet [Sheet1]
m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
m_oSheet.Activate();
m_oSheet.Name = "รายการเสนอซื้อ";
SetData_to_SheetNoSubmit();
string template = Application.StartupPath;
string strRunReport = file_Name;
//string strPass = "******"; password
m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
m_oExcelApp.Visible = true;
}
catch (interop.COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
if (m_oExcelApp == null)
{
m_oExcelApp.Quit();
m_oExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
public static void CreateExperimentUploadTable(ExperimentTable exp, Excel.Application excel)
{
//每個 sheet 以 seq_id 命名
if (exp.SeqID == "")
{
return;
}
Excel._Worksheet xlsht = excel.ActiveWorkbook.Worksheets.Add();
xlsht.Name = exp.SeqID + "_Upload";
xlsht.Activate();
//
// 取得所有 item
//
List <IItem> _totalItems = new List <IItem>();
if (exp.CompTool.Items != null)
{
_totalItems = _totalItems.Union(exp.CompTool.Items).ToList();
}
if (exp.InjTool.Items != null)
{
_totalItems = _totalItems.Union(exp.InjTool.Items).ToList();
}
if (exp.Formulas != null)
{
_totalItems = _totalItems.Union(exp.Formulas).ToList();
}
if (exp.Property != null)
{
_totalItems = _totalItems.Union(exp.Property).ToList();
}
string[] item_flag = new string[] { "C1", "CT", "I1", "IT", "P" };
IItem[] uploadItems = _totalItems.Where(x => item_flag.Contains(x.Flag)).ToArray();
DataTable dt = new DataTable();
string[] col_dt = new string[] { "seq_id", "lot_id", "item", "item_flag", "unit", "item_value" };
foreach (string col in col_dt)
{
dt.Columns.Add(col);
dt.Columns[col].DataType = typeof(string);
}
foreach (IItem item in uploadItems)
{
DataRow dr = dt.NewRow();
dr["seq_id"] = exp.SeqID;
dr["lot_id"] = "";
dr["item_value"] = "";
dr["item_flag"] = item.Flag;
dr["item"] = item.Name;
dr["unit"] = item.Unit;
dt.Rows.Add(dr);
}
Excel.Range rng;
int rows = dt.Rows.Count;
xlsht.get_Range("A1").Value = "Remark:";
xlsht.get_Range("B1").Value = exp.Remark;
rng = xlsht.Range[xlsht.Cells[2, 1], xlsht.Cells[2, 6]];
rng.Value = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();
for (int i = 0; i < dt.Columns.Count; i++)
{
rng = xlsht.Range[xlsht.Cells[3, i + 1], xlsht.Cells[2 + rows, i + 1]];
rng.NumberFormat = "@";
string[] values = dt.Rows.Cast <DataRow>().Select(x => x[i].ToString()).ToArray();
rng.Value = excel.WorksheetFunction.Transpose(values);
}
}
/// <summary>
/// 方法名称： ReWrite
/// 内容描述： 重写数据
/// 作 者： 林付国
/// 日 期： 2006-5-25 11:22:30
/// </summary>
/// <param name="iRow">重写开始行数</param>
/// <returns></returns>
public bool ReWrite(int iRow)
{
System.Data.DataTable dt = null;
Excel.Range m_objRange;
bool bolRet = false;
string strCell = "A1";
dt = this.DataSource.Tables[0];
Object[,] AData;
int iSheetCount = wb.Sheets.Count;
if (!CellCheck(iRow, 1))
{
bolRet = false;
throw new Exception("写入单元格超出范围，请检查！");
}
// 写入检查
this.WriteException(dt);
string strSheetName;
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
strSheetName = ws.Name.ToString();
strCell = "A" + Convert.ToString(iRow);
try
{
// 插入的测试
// if(this.WriteType == EnumType.WriteType.Insert)
// {
// AData = new object[dt.Rows.Count,dt.Columns.Count];
// ConvertArrayDataTable(AData,dt,0,dt.Rows.Count - 1,0);
//
// m_objRange = ws.get_Range(strCell, Type.Missing);
// //m_objRange = m_objRange.get_Resize(dt.Rows.Count,dt.Columns.Count);
// //m_objRange.EntireRow.Select();
// for(int i = 0 ; i <= dt.Rows.Count ; i++)
// {
// m_objRange.EntireRow.Insert();
// }
// //m_objRange.Value2 = AData;
// }
// else
// {
// 重写部分
if ((dt.Rows.Count + iRow) <= iMaxRow)
{
AData = new object[dt.Rows.Count, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
}
else
{
int iFristInsertRow, iSeceondInRow;
iFristInsertRow = iMaxRow - iRow;
AData = new object[iFristInsertRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iFristInsertRow, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(iFristInsertRow, dt.Columns.Count);
m_objRange.Value2 = AData;
Excel._Worksheet xSheet = null;
xSheet = (Excel._Worksheet)(wb.Sheets.Add(objOpt, objOpt, objOpt, objOpt));
xSheet.Name = strSheetName + "(2)";
iSeceondInRow = dt.Rows.Count - iFristInsertRow;
AData = new object[iSeceondInRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iSeceondInRow, 0);
m_objRange = xSheet.get_Range("A1", Type.Missing);
m_objRange = m_objRange.get_Resize(iSeceondInRow, dt.Columns.Count);
m_objRange.Value2 = AData;
xSheet.Activate();
xSheet.Move(objOpt, wb.Sheets[ws.Index]);
}
//}
this.Save();
bolRet = true;
}
catch
{
throw new Exception("写入Excel文件出错，请检查！");
}
return(bolRet);
}
// A3: Number of students by level of education, age and sex
void sheetA3(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 29; //row offset
const int UNDER_TWO = 14; //row
const int TWENTYFIVE_TWENTYNINE = 38; //row
const int OVER_TWENTYNINE = 39; //row
const int AGE_UNKNOWN = 40; //row
const int ZERO = 13; //row
const int MISSING_AGE = -1;
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A3"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED, AGE, gender, sum(count) as COUNT from #StudentsTable group by ISCED, AGE, gender",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced;
int age;
string gender;
int count;
isced = rdr.GetString(0);
try
{
age = rdr.GetInt32(1);
} catch (System.Data.SqlTypes.SqlNullValueException)
{
age = MISSING_AGE;
}
gender = rdr.GetString(2);
count = rdr.GetInt32(3);
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row;
if (age >= 2 && age <= 24)
{
row = ZERO + age + rowOffset;
}
else if (age == MISSING_AGE)
{
row = AGE_UNKNOWN + rowOffset;
}
else if (age < 2)
{
row = UNDER_TWO + rowOffset;
}
else if (age >= 25 && age <= 29)
{
row = TWENTYFIVE_TWENTYNINE + rowOffset;
}
else if (age > 29)
{
row = OVER_TWENTYNINE + rowOffset;
}
else
{
Console.WriteLine("Invalid Age: " + age);
continue;
}
int column = getCol(isced);
workSheet.Cells[row, column] = workSheet.get_Range(helpers.GetCellAddress(column, row)).Value2 + count;
}
}
}
/// <summary>
/// 方法名称： Write
/// 内容描述： 写入数据，包括（列名，数据集）
/// 实现流程：
/// 作 者： 林付国
/// 日 期： 2006-5-19 10:26:31
/// </summary>
/// <returns></returns>
public bool Write()
{
System.Data.DataTable dt = null;
Excel.Range m_objRange;
bool bolRet = false;
string strCell = "A1";
dt = this.DataSource.Tables[0];
Object[,] AData;
int irowcount = 0;
int iSheetCount = wb.Sheets.Count;
app.ActiveCell.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Select();
irowcount = app.ActiveCell.Row;
// 写入检查
this.WriteException(dt);
try
{
switch (this.WriteType)
{
case EnumType.WriteType.None:
strCell = "A1";
AData = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
AData[0, i] = dt.Columns[i].ColumnName;
}
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 1);
//ws = (excel.Worksheet)(wb.Sheets.Add(objOpt,objOpt,objOpt,objOpt)) ;
// 改变Sheet表名称
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
#region // 删除默认Sheet表
//ws.Activate();
//ws.Move(wb.Sheets[1],objOpt);
// while(iSheetCount > 0)
// {
// Excel._Worksheet tempXSheet = (Excel._Worksheet) (wb.Worksheets[iSheetCount]) ;
// tempXSheet.Delete() ;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tempXSheet) ;
// tempXSheet=null ;
// iSheetCount--;
// }
#endregion
break;
case EnumType.WriteType.Insert:
{
// strCell = "A1";
// AData = new object[dt.Rows.Count,dt.Columns.Count];
// for(int i = 0;i <=dt.Columns.Count - 1;i++)
// {
// AData[0,i] = dt.Columns[i].ColumnName;
// }
// ConvertArrayDataTable(AData,dt,0,dt.Rows.Count - 1,1);
// // 改变Sheet表名称
// if(this.SheetName != "")
// {
// ws.Name = this.SheetName;;
// }
// m_objRange = ws.get_Range(strCell, Type.Missing);
// //m_objRange.EntireRow.Insert();
// m_objRange = m_objRange.get_Resize(dt.Rows.Count,dt.Columns.Count).EntireRow;
// m_objRange.Insert();
// //m_objRange.Value2 = AData;
break;
}
case EnumType.WriteType.Append:
string strSheetName;
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
strSheetName = ws.Name.ToString();
strCell = "A" + Convert.ToString(irowcount + 1);
if ((dt.Rows.Count + irowcount) <= iMaxRow)
{
AData = new object[dt.Rows.Count, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
//m_objRange.EntireRow.Insert();
}
else
{
int iFristInsertRow, iSeceondInRow;
iFristInsertRow = iMaxRow - irowcount;
AData = new object[iFristInsertRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iFristInsertRow, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(iFristInsertRow, dt.Columns.Count);
m_objRange.Value2 = AData;
Excel._Worksheet xSheet = null;
xSheet = (Excel._Worksheet)(wb.Sheets.Add(objOpt, objOpt, objOpt, objOpt));
xSheet.Name = strSheetName + "(2)";
iSeceondInRow = dt.Rows.Count - iFristInsertRow;
AData = new object[iSeceondInRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iSeceondInRow, 0);
m_objRange = xSheet.get_Range("A1", Type.Missing);
m_objRange = m_objRange.get_Resize(iSeceondInRow, dt.Columns.Count);
m_objRange.Value2 = AData;
xSheet.Activate();
xSheet.Move(objOpt, wb.Sheets[ws.Index]);
}
break;
default:
strCell = "A1";
break;
}
this.Save();
bolRet = true;
}
catch
{
throw new Exception("写入Excel文件出错，请检查！");
}
return(bolRet);
}
/// <summary>
/// Sets Excel to activate the tab specified by name.
/// </summary>
/// <param name="tabName">Name of the tab.</param>
public void SetTab(string tabName)
{
_xlWorksheet = getWorksheetByName(tabName);
_xlWorksheet.Activate();
}
private void ecxelPoster(int regnskabsår)
{
var api = UCInitializer.GetBaseAPI;
CompanyFinanceYear CompanyFinanceYear = null;
var task1 = api.Query <CompanyFinanceYear>();
task1.Wait();
var cols1 = task1.Result;
foreach (var col in cols1)
{
if (col.PeriodeStart(0).Year == regnskabsår)
{
CompanyFinanceYear = col;
}
}
var task2a = api.Query <Debtor>();
task2a.Wait();
var karDebtor = task2a.Result;
var task2b = api.Query <Creditor>();
task2b.Wait();
var karCreditor = task2b.Result;
KarDebCred karDebCred = new KarDebCred();
foreach (var d in karDebtor)
{
RecDebCred recDebCred = new RecDebCred()
{
_Account = d._Account,
_Name = d._Name
};
karDebCred.Add(recDebCred);
}
foreach (var k in karCreditor)
{
RecDebCred recDebCred = new RecDebCred()
{
_Account = k._Account,
_Name = k._Name
};
karDebCred.Add(recDebCred);
}
var task3 = api.Query <GLAccount>();
task3.Wait();
var karGLAccount = task3.Result;
var crit = new List <PropValuePair>();
string dateinterval = string.Format("{0}..{1}", CompanyFinanceYear._FromDate.ToShortDateString(), CompanyFinanceYear._ToDate.ToShortDateString());
var pair = PropValuePair.GenereteWhereElements("Date", typeof(DateTime), dateinterval);
crit.Add(pair);
var task4 = api.Query <GLTrans>(crit);
task4.Wait();
var karGLTrans = task4.Result;
DateTime pReadDate = DateTime.Now;
string pSheetName = "Poster";
char[] dash = { '-' };
_Excel.Application oXL = null;;
_Excel._Workbook oWB;
_Excel._Worksheet oSheetPoster;
_Excel._Worksheet oSheetRegnskab;
_Excel._Worksheet oSheetRegnskab_puls3060 = null;
_Excel.Window oWindow;
_Excel.Range oRng;
string rec_Regnskaber = @"Z:\Regnskaber";
rec_Regnskaber = Environment.ExpandEnvironmentVariables(rec_Regnskaber);
string SaveAs = rec_Regnskaber + @"\" + pSheetName + pReadDate.ToString("_yyyyMMdd_HHmmss") + ".xlsx";
var JournalPoster = from h in karGLTrans
join d1 in karGLAccount on h._Account equals d1._Account into details1
from x1 in details1.DefaultIfEmpty()
join d2 in karDebCred on h._DCAccount equals d2._Account into details2
from x2 in details2.DefaultIfEmpty(new RecDebCred()
{
_Account = null, _Name = null
})
orderby h._JournalPostedId, h._Voucher, h._VoucherLine
select new clsJournalposter
{
ds = DS(x1.AccountTypeEnum),
k = IUAP(x1.AccountTypeEnum),
Konto = h._Account + "-" + x1._Name,
DebKrd = x2._Name,
//DebKrd = h._DCAccount,
Udvalg = h._Dimension1,
Aktivitet = h._Dimension2,
Dato = h._Date,
Klade = h._JournalPostedId,
Serie = h._NumberSerie,
Bilag = h._Voucher,
Linie = h._VoucherLine,
Tekst = h._Text,
Beløb = h._Amount,
};
var count = JournalPoster.Count();
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new _Excel.Application();
oXL.Visible = true;
//oXL.Visible = true; //For debug
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in karGLTrans select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
////////////////////////////////////////////////////////////////////////
oSheetPoster.Name = "Poster";
int row = 1;
foreach (clsJournalposter m in JournalPoster)
{
this.MainformProgressBar.PerformStep();
row++;
//if (row > 500) break; //<----------------------------------------------
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheetPoster.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "Medlem3060uc.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheetPoster.Cells[1, col] = heading;
}
}
}
}
}
oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = _Excel.Constants.xlCenter;
oRng.VerticalAlignment = _Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "G" + row.ToString(); //<------------------HUSK
oRng = oSheetPoster.get_Range("G2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheetPoster.ListObjects.AddEx(_Excel.XlListObjectSourceType.xlSrcRange, oSheetPoster.UsedRange, System.Type.Missing, _Excel.XlYesNoGuess.xlYes).Name = "PosterList";
oSheetPoster.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.FreezePanes = true;
oSheetPoster.get_Range("A1", Missing.Value).Select();
oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(oSheetPoster, System.Type.Missing, System.Type.Missing, System.Type.Missing);
//oXL.Visible = true; //For debug
_Excel.Range x1 = oSheetPoster.Cells[1, 1];
_Excel.Range x2 = oSheetPoster.Cells[row, 13]; //<--------------------HUSK
_Excel.Range xx = oSheetPoster.get_Range(x1, x2);
_Excel.PivotField _pvtField = null;
_Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard(
_Excel.XlPivotTableSourceType.xlDatabase, //SourceType
xx, //SourceData
oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination
"PivotTable1", //TableName
System.Type.Missing, //RowGrand
System.Type.Missing, //CollumnGrand
System.Type.Missing, //SaveData
System.Type.Missing, //HasAutoformat
System.Type.Missing, //AutoPage
System.Type.Missing, //Reserved
System.Type.Missing, //BackgroundQuery
System.Type.Missing, //OptimizeCache
System.Type.Missing, //PageFieldOrder
System.Type.Missing, //PageFieldWrapCount
System.Type.Missing, //ReadData
System.Type.Missing); //Connection
_pvtField = (_Excel.PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField;
_pvtField.Function = _Excel.XlConsolidationFunction.xlSum;
_pvtField.NumberFormat = "#,##0";
oSheetRegnskab.Name = "Regnskab";
oSheetRegnskab.Cells[2, 3] = "Regnskab Puls 3060";
oRng = oSheetRegnskab.get_Range("D3", Missing.Value);
oRng.Select();
bool[] Periods = { false, false, false, false, true, false, false };
oRng.Group(true, true, Missing.Value, Periods);
oRng = oSheetRegnskab.get_Range("D4", "P4");
oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight;
oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Puls 3060";
oSheetRegnskab.PageSetup.CenterHeader = "";
oSheetRegnskab.PageSetup.RightHeader = "&P af &N";
oSheetRegnskab.PageSetup.LeftFooter = "&Z&F";
oSheetRegnskab.PageSetup.CenterFooter = "";
oSheetRegnskab.PageSetup.RightFooter = "&D&T";
oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.PrintHeadings = false;
oSheetRegnskab.PageSetup.PrintGridlines = true;
oSheetRegnskab.PageSetup.CenterHorizontally = false;
oSheetRegnskab.PageSetup.CenterVertically = false;
oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed;
oWB.ShowPivotTableFieldList = false;
/*
* for (var i = oWB.Worksheets.Count; i > 0; i--)
* {
* _Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i);
* if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster"))
* {
* oSheetWrk.Delete();
* }
* }
*/
oSheetRegnskab_puls3060 = oSheetRegnskab;
oSheetRegnskab.get_Range("A1", Missing.Value).Select();
//////////////////////////////////////////////////////////////////////////////////////////////
oSheetRegnskab_puls3060.Activate();
oSheetRegnskab_puls3060.get_Range("A1", Missing.Value).Select();
oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
oXL.Quit();
oXL = null;
this.imapSaveExcelFile(SaveAs, "Puls3060 Regnskab");
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
// A7: Number of new entrants to Grade 1 in initial education and prior enrolment by age and sex
void sheetA7(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 21; //row offset
const int UNDER_FOUR = 14; //row
const int OVER_EIGHTEEN = 30; //row
const int ZERO = 11; //row offset
const int AGE_UNKNOWN = 31; //row
const int PRIMARY_COL = 17; //col
const int ECE = 33; //row
const int LOWER_SECONDARY_COL = 20; //col
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A7"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED_TOP as ISCED, AGE, gender, ECE, sum(count) as COUNT from #StudentsTable
where class in (1.0, 7.0)
and REPEATER = ''
--where AGE = 4 and gender = 'F'
group by ISCED_TOP, gender, AGE, ECE",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced;
int age;
string gender;
string ece;
int count;
try
{
isced = (string)rdr["ISCED"];
age = (int)rdr["AGE"];
gender = (string)rdr["gender"];
ece = (string)rdr["ECE"];
count = (int)rdr["count"];
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}, {4}", isced, gender, age, ece, count));
}
catch
{
continue; // Data needs to be clean
}
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row;
if (age >= 2 && age <= 24)
{
row = ZERO + age + rowOffset;
}
else if (age < 4)
{
row = UNDER_FOUR + rowOffset;
}
else if (age > 18)
{
row = OVER_EIGHTEEN + rowOffset;
}
else
{
row = AGE_UNKNOWN + rowOffset;
}
if (isced == "ISCED 1")
{
Console.WriteLine(age + " " + isced + " " + gender);
Console.WriteLine(row.ToString() + " : " + PRIMARY_COL.ToString());
workSheet.Cells[row, PRIMARY_COL] = workSheet.get_Range(helpers.GetCellAddress(PRIMARY_COL, row)).Value2 + count;
if (ece == "ECE")
{
row = ECE + rowOffset;
Console.WriteLine(age + " " + isced + " " + gender);
Console.WriteLine(row.ToString() + " : " + PRIMARY_COL.ToString());
workSheet.Cells[row, PRIMARY_COL] = workSheet.get_Range(helpers.GetCellAddress(PRIMARY_COL, row)).Value2 + count;
}
}
else if (isced == "ISCED 2")
{
Console.WriteLine(age + " " + isced + " " + gender);
Console.WriteLine(row.ToString() + " : " + LOWER_SECONDARY_COL.ToString());
workSheet.Cells[row, LOWER_SECONDARY_COL] = workSheet.get_Range(helpers.GetCellAddress(LOWER_SECONDARY_COL, row)).Value2 + count;
}
}
}
}
private void ExportData()
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//Get a new workbook.
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oWB = oXL.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
oXL.Visible = true;
//oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//oSheet.Activate();
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add();
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Activate();
oSheet.Name = DateTime.Now.ToString("yyyyMMdd-HHmm");
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
//AutoFit columns A:D.
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
//Save file
//oWB.Save();
//Save as
oWB.SaveAs("C:\\file.xls", XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
oWB.Close();
}
// A6: Number of students in initial lower secondary general education by age, grade and sex
void sheetA6(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 21; //row offset
const int AGE_UNKNOWN = 31; //row
const int UNDER_TEN = 17; //row
const int OVER_TWENTYFOUR = 30; //row
const int ZERO = 10; //row offset
const int REPEATERS = 33; //row
//const int UNSPECIFIED_GRADE = 35; // column AI
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A6"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
SqlCommand cmd = new SqlCommand(
@"select CLASS - 6 as CLASS, AGE, gender, REPEATER, sum(count) as COUNT from #StudentsTable
where ISCED = 'ISCED 24'
group by CLASS, AGE, gender, REPEATER",
sqlConn);
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
decimal _class = (decimal)rdr["class"];
string strAge = rdr["AGE"].ToString();
string gender = (string)rdr["gender"];
string repeaters = (string)rdr["REPEATER"];
int count = (int)rdr["count"];
int column = getCol("Grade " + ((int)_class).ToString());
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row;
if (strAge == "N/A")
{
row = AGE_UNKNOWN + rowOffset;
}
else
{
int age = Convert.ToInt16(strAge);
if (age < 10)
{
row = UNDER_TEN + rowOffset;
}
else if (age > 24)
{
row = OVER_TWENTYFOUR + rowOffset;
}
else //if (age >= 10 && age <= 24)
{
row = ZERO + age + rowOffset;
}
}
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", _class, strAge, gender, count));
Console.WriteLine(String.Format("{0}, {1}", row, column));
workSheet.Cells[row, column] = workSheet.get_Range(helpers.GetCellAddress(column, row)).Value2 + count;
if (repeaters == "Repeaters")
{
row = REPEATERS + rowOffset;
workSheet.Cells[row, column] = workSheet.get_Range(helpers.GetCellAddress(column, row)).Value2 + count;
}
}
}
}
protected void btnGatherSubmit_Click(object sender, EventArgs e)
{
string customerId = this.ddlCustomer.SelectedValue;
string chartTitle = this.txtChartTitle.Text.Trim().Replace("#", "");
Dictionary <string, string> tables = (Dictionary <string, string>)ViewState["tables"];
using (var context = new ChartContext())
{
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string sheetname = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[sheetname];
worksheet.Activate();
Excel.Range Range_B1 = worksheet.get_Range("B1");
Range_B1.Value2 = "敏感信息量";
Excel.Range Range_C1 = worksheet.get_Range("C1");
Range_C1.Value2 = "信息总量";
int i = 1;
foreach (KeyValuePair <string, string> item in tables)
{
i++;
Excel.Range Range_Ai = worksheet.get_Range("A" + i);
Range_Ai.Value2 = item.Key;
Excel.Range Range_Bi = worksheet.get_Range("B" + i);
Range_Bi.Value2 = item.Value.Split(',')[0];
Excel.Range Range_Ci = worksheet.get_Range("C" + i);
Range_Ci.Value2 = item.Value.Split(',')[1];
}
Excel.Range dataSourceRange = worksheet.get_Range("A1", "C" + i);
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, missing, missing, missing, missing);
//设置数据源
shape1.Chart.SetSourceData(dataSourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle;
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
//设置Legend
shape1.Chart.SeriesCollection(1).Name = (string)worksheet.get_Range("B1").Value2;
shape1.Chart.SeriesCollection(2).Name = (string)worksheet.get_Range("C1").Value2;
shape1.Chart.ApplyDataLabels();
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G6").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "gather"
};
foreach (KeyValuePair <string, string> item in tables)
{
var gatherQuantity = new GatherQuantity
{
ChannelName = item.Key,
SensitiveNumber = int.Parse(item.Value.Split(',')[0]),
TotalNumber = int.Parse(item.Value.Split(',')[1]),
};
entity.GatherQuantities.Add(gatherQuantity);
}
context.Gathers.Add(entity);
context.SaveChanges();
InitRepeater2();
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
}
catch (Exception ex)
{
MessageBox.ShowAndRedirect(this, "生成图表是出错：" + ex.Message, Request.RawUrl);
}
}
}
private void saveTOcsv(object sender, EventArgs e)
{
button1_Click(sender, e);
// 開啟一個新的應用程式
Excel.Application app = new Excel.Application();
// 讓Excel文件可見
app.Visible = true;
// 停用警告訊息
app.DisplayAlerts = false;
// 加入新的活頁簿
app.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wbk = app.Workbooks[1];
// 設定活頁簿焦點
wbk.Activate();
try
{
// 引用第一個工作表
wst = (Excel._Worksheet)wbk.Worksheets[1];
// 命名工作表的名稱
wst.Name = db.Station_0001.mday;
// 設定工作表焦點
wst.Activate();
app.Cells[1, 1] = "ubike retrieve data";
// 設定metainfo
app.Cells[1, 1] = "站點";
app.Cells[2, 1] = "資料更新時間";
app.Cells[3, 1] = "目前車輛數";
app.Cells[4, 1] = "空位數量";
app.Cells[5, 1] = "總停車格";
// 設定第1列顏色
wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
wrange.Select();
wrange.Font.Color = ColorTranslator.ToOle(Color.Blue); //word color
wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
// 設定第2列資料
for (int c = 2; c < db.Stations.Count; c++)
{
app.Cells[1, c] = db.Stations[c - 2].sno; //站點
app.Cells[2, c] = db.Stations[c - 2].mday; //資料更新時間
app.Cells[3, c] = db.Stations[c - 2].sbi; //目前車數
app.Cells[4, c] = db.Stations[c - 2].bemp; //空格數
app.Cells[5, c] = db.Stations[c - 2].tot; //總停車數
}
try
{
//另存活頁簿
wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbk.SaveCopyAs(db.Station_0001.mday);
Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
}
catch (Exception ex)
{
Console.WriteLine("產生報表時出錯！" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wbk.Close(false, Type.Missing, Type.Missing);
//關閉Excel
app.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wbk = null;
wst = null;
wrange = null;
app = null;
GC.Collect();
}
