/// <summary>
/// 将数据写入Microsoft.Office.Interop.Excel
/// </summary>
/// <param name="data">要写入的字符串</param>
/// <param name="starRow">写入的行</param>
/// <param name="startColumn">写入的列</param>
public void WriteData(string data, string fileName, string sheetName, int row, int column)
{
try
{
Microsoft.Office.Interop.Excel.Application myExcel;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
myExcel = new Microsoft.Office.Interop.Excel.Application();
//myWorkBook = myExcel.Application.Workbooks.Add(true);
myWorkBook = myExcel.Workbooks.Add(fileName);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = sheetName;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
myExcel.Cells[row, column] = data;
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
//Microsoft.Office.Interop.Excel.Range rng3 = xSheet.get_Range("C6", System.Reflection.Missing.Value);
//rng3.Cells.FormulaR1C1 = txtCellText.Text;
// rng3.Interior.ColorIndex = 6; //设置Range的背景色
}
public bool ConvertSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, string exportDir)
{
if (sheet == null)
{
return(false);
}
sheet.Activate();
if (sheet.Cells == null || string.IsNullOrEmpty(exportDir) || sheet.UsedRange == null || sheet.UsedRange.Rows.Count < 3 || sheet.UsedRange.Columns.Count <= 0)
{
return(false);
}
// 转换
if (!ConvertSheetToCs(sheet, exportDir))
{
return(false);
}
if (!ConvertSheetToJson(sheet, exportDir))
{
return(false);
}
return(true);
}
public static void exportToExcel(DataGridView dgv)
{
Application.UseWaitCursor = true;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.HeaderText;
}
int rowIndex = 0;
foreach (DataGridViewRow row in dgv.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
Application.UseWaitCursor = false;
}
//_______________________________________添加自定义函数_________________________________________________________________
/// <summary>
/// 将数据写入Microsoft.Office.Interop.Excel
/// </summary>
/// <param name="data">要写入的二维数组数据</param>
/// <param name="startRow">Microsoft.Office.Interop.Excel中的起始行</param>
/// <param name="startColumn">Microsoft.Office.Interop.Excel中的起始列</param>
public void WriteData(string[,] data, string fileName, string sheetName, int startRow, int startColumn)
{
try
{
Microsoft.Office.Interop.Excel.Application myExcel;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
myExcel = new Microsoft.Office.Interop.Excel.Application();
//myWorkBook = myExcel.Application.Workbooks.Add(true);
myWorkBook = myExcel.Workbooks.Add(fileName);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = sheetName;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
int rowNumber = data.GetLength(0);
int columnNumber = data.GetLength(1);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
//在Microsoft.Office.Interop.Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];
}
}
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
cmdOpenWS_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string strPath = BaseObjs.docFullName;
string strJN = BaseObjs.docName.Substring(1, 4);
string strFN = strJN + "EW" + ".xlsx";
string strFullPath = strPath + "\\" + strFN;
Microsoft.Office.Interop.Excel.Application objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook objWB = objExcelApp.Workbooks.Open(strFullPath);
Microsoft.Office.Interop.Excel.Worksheet objWS = objExcelApp.Worksheets["SUMMARY"];
objWS.Activate();
}
/// <summary>
/// 读取指定单元格数据
/// </summary>
/// <param name="row">行序号</param>
/// <param name="column">列序号</param>
/// <returns>该格的数据</returns>
public string ReadData(string fileName, string sheetName, int row, int column)
{
Microsoft.Office.Interop.Excel.Application myExcel;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
myExcel = new Microsoft.Office.Interop.Excel.Application();
//myWorkBook = myExcel.Application.Workbooks.Add(true);
myWorkBook = myExcel.Workbooks.Add(fileName);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = sheetName;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
Microsoft.Office.Interop.Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
string str = range.Text.ToString();
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
return(str);
}
private bool WriteHeaderOfResExcel(Microsoft.Office.Interop.Excel.Application excelTo, Microsoft.Office.Interop.Excel.Application excelFrom, int iStartRowID)
{
if (excelTo == null)
{
return(false);
}
if (excelFrom == null)
{
return(false);
}
try
{
Microsoft.Office.Interop.Excel.Worksheet fromSheet = excelFrom.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Worksheet toSheet = excelTo.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
object MissingValue = Type.Missing;
int ColCnt = fromSheet.UsedRange.Columns.Count;
// 选择
fromSheet.Select(MissingValue);
// 复制.
fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[iStartRowID - 1, ColCnt]).Copy(MissingValue);
toSheet.Activate();
toSheet.Select(MissingValue);
// 粘贴格式.
toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue);
// 粘贴数据.
toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue);
fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[1, 1]).Copy(MissingValue);
}
catch
{
return(false);
}
return(true);
}
/// <summary>
/// Click event to handle the export to excel
/// </summary>
/// <param name="sender">Sender object</param>
/// <param name="e">event data</param>
private void Export_Click(object sender, EventArgs e)
{
try
{
var table = (DataTable)dataGridViewMembers.DataSource;
Microsoft.Office.Interop.Excel.Application excel
= new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
excel.Cells[1, columnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6)
{
if (columnIndex == 4)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(Occupation), row[col.ColumnName]);
}
if (columnIndex == 5)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(MaritalStatus), row[col.ColumnName]);
}
if (columnIndex == 6)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(HealthStatus), row[col.ColumnName]);
}
}
else
{
excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString();
}
}
}
excel.Visible = true;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception ex)
{
this.ShowErrorMessage(ex);
}
}
private static void CreateChart(List <SignalGenerator.Day> data, string symbol, string templatePath)
{
Console.WriteLine("Generating...");
var app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks books = null;
Microsoft.Office.Interop.Excel.Workbook book = null;
try
{
books = app.Workbooks;
book = books.Open(templatePath);
app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
app.ScreenUpdating = false;
Microsoft.Office.Interop.Excel.Chart sheetG = book.Charts[1];
Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[1];
sheet.Activate();
var mindate = data.Min(d => d.Date);
var maxdate = data.Max(d => d.Date);
var lowest = data.Min(d => d.LowestValue());
var highest = data.Max(d => d.HighestValue());
var range = highest - lowest;
lowest -= (range * 0.05);
highest += (range * 0.05);
if (lowest < 0)
{
lowest = 0;
}
var title = $"{symbol.ToUpper()}: {mindate:yyyy-MM-dd} - {maxdate:yyyy-Mm-dd}";
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 10]).Value = title;
var row = 1;
var isin = data[0].In;
var p = 0;
foreach (var day in data)
{
var np = row * 100 / data.Count;
if (np != p)
{
Console.WriteLine($"{np}%");
p = np;
}
row++;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 1]).Value = day.Date;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 2]).Value = day.Open;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 3]).Value = day.High;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 4]).Value = day.Low;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 5]).Value = day.Close;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 6]).Value = day.BuyLine;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 7]).Value = day.SellLine;
if (day.StopLoss > 0)
{
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 8]).Value = day.StopLoss;
}
if (day.In != isin)
{
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 9]).Value = day.In ? 1 : 0;
isin = day.In;
}
}
sheetG.Activate();
Console.WriteLine("Done");
app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
app.ScreenUpdating = true;
var axis = sheetG.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Microsoft.Office.Interop.Excel.Axis;
lowest -= lowest % axis.MajorUnit;
highest += axis.MajorUnit;
highest -= highest % axis.MajorUnit;
axis.MinimumScale = lowest;
axis.MaximumScale = highest;
app.Visible = true;
Console.WriteLine("Press any key when ready");
Console.ReadKey();
}
catch (Exception ex)
{
Console.WriteLine($"Exception: {ex.Message}");
}
try
{
if (book != null)
{
book.Close(false);
}
if (books != null)
{
books.Close();
}
app.Quit();
for (var i = 0; i < 10; i++)
{
GC.Collect();
}
}
catch { }
}
public static void ExportFile(Dictionary <AttributeTypeCode, List <entityParam> > entityParam, EntityInfo entityInfo)
{
SaveFileDialog sfd = null;
DataColumns[] fromatedList = FormatDataForExport(entityParam);
String[] columnsHeaderName = new string[] {
"Display Name", "Schema Name", "Type", "Target", "Managed/Unmanaged", "IsAuditable", "IsSearchable", "Required Level", "Introduced Version", "CreatedOn", "Percentage Of Use"
};
int headerIndex = 9;
int lineIndex = headerIndex + 1;
if (fromatedList.Length > 0)
{
sfd = new SaveFileDialog();
sfd.Filter = "Excel (.xlsx)| *.xlsx;*.xls;";
sfd.FileName = entityInfo.entityName + "_EntityKPIsExport_" + DateTime.Now.ToShortDateString().Replace('/', '-') + ".xlsx";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
}
}
if (!fileError)
{
try
{
Microsoft.Office.Interop.Excel._Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
#region DataGrid
worksheet = workbook.ActiveSheet;
if (entityInfo.entityName.Length > 21)
{
worksheet.Name = entityInfo.entityName.Substring(0, 21) + "_MetaData";
}
else
{
worksheet.Name = entityInfo.entityName + "_MetaData";
}
if (entityInfo != null)
{
for (int i = 1; i <= 6; i++)
{
worksheet.Cells[i, 1].Font.Bold = true;
worksheet.Cells[i, 1].Interior.Color = Color.Wheat;
worksheet.Cells[i, 1].Font.Size = 12;
}
worksheet.Cells[1, 1] = "Entity Display Name";
worksheet.Cells[1, 2] = entityInfo.entityName;
worksheet.Cells[2, 1] = "Entity Technical Name";
worksheet.Cells[2, 2] = entityInfo.entityTechnicalName;
worksheet.Cells[3, 1] = "CreatedOn";
worksheet.Cells[3, 2] = entityInfo.entityDateOfCreation != DateTime.MinValue ? entityInfo.entityDateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[4, 1] = "Number Of Fields";
worksheet.Cells[4, 2] = entityInfo.entityFieldsCount;
worksheet.Cells[5, 1] = "Number Of Records";
worksheet.Cells[5, 2] = entityInfo.entityRecordsCount;
worksheet.Cells[6, 1] = "Entity Fields Volume Usage";
worksheet.Cells[6, 2] = ((entityInfo.entityTotalUseOfColumns * 100) / entityInfo.entityDefaultColumnSize).ToString("0.##\\%");
}
for (int i = 1; i < columnsHeaderName.Length + 1; i++)
{
worksheet.Cells[headerIndex, i] = columnsHeaderName[i - 1];
worksheet.Cells[headerIndex, i].Font.NAME = "Calibri";
worksheet.Cells[headerIndex, i].Font.Bold = true;
worksheet.Cells[headerIndex, i].Interior.Color = Color.Wheat;
worksheet.Cells[headerIndex, i].Font.Size = 12;
}
for (int i = 0; i < fromatedList.Length; i++)
{
worksheet.Cells[i + lineIndex, 1] = fromatedList[i].displayName;
worksheet.Cells[i + lineIndex, 2] = fromatedList[i].fieldName;
worksheet.Cells[i + lineIndex, 3] = fromatedList[i].fieldType;
worksheet.Cells[i + lineIndex, 4] = fromatedList[i].target;
worksheet.Cells[i + lineIndex, 5] = fromatedList[i].isManaged;
worksheet.Cells[i + lineIndex, 6] = fromatedList[i].isAuditable;
worksheet.Cells[i + lineIndex, 7] = fromatedList[i].isSearchable;;
worksheet.Cells[i + lineIndex, 8] = fromatedList[i].requiredLevel;;
worksheet.Cells[i + lineIndex, 9] = fromatedList[i].introducedVersion;
worksheet.Cells[i + lineIndex, 10] = fromatedList[i].dateOfCreation != DateTime.MinValue ? fromatedList[i].dateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[i + lineIndex, 11] = fromatedList[i].percentageOfUse.Replace(",", ".");
if (fromatedList[i].target == String.Empty)
{
worksheet.Cells[i + lineIndex, 4].Interior.Color = Color.Gainsboro;
}
}
worksheet.Columns.AutoFit();
#endregion
var xlSheets = workbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[1], Type.Missing, Type.Missing);
xlNewSheet.Name = "Charts";
#region chartManagedUnmanaged
//add data
xlNewSheet.Cells[2, 2] = "Managed";
xlNewSheet.Cells[2, 2].Font.Color = Color.White;
xlNewSheet.Cells[2, 3] = entityInfo.managedFieldsCount;
xlNewSheet.Cells[2, 3].Font.Color = Color.White;
xlNewSheet.Cells[3, 2] = "Unmanaged";
xlNewSheet.Cells[3, 2].Font.Color = Color.White;
xlNewSheet.Cells[3, 3] = entityInfo.unmanagedFieldsCount;
xlNewSheet.Cells[3, 3].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartPage.HasTitle = true;
chartPage.ChartTitle.Text = @"Managed\Unmanaged Fields";
chartRange = xlNewSheet.get_Range("B2", "C3");
chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region EntityFieldsCreated
//add data
xlNewSheet.Cells[2, 10] = "Available Fields To Create";
xlNewSheet.Cells[2, 10].Font.Color = Color.White;
xlNewSheet.Cells[2, 11] = entityInfo.entityDefaultColumnSize - entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[2, 11].Font.Color = Color.White;
xlNewSheet.Cells[3, 10] = "Created Fields";
xlNewSheet.Cells[3, 10].Font.Color = Color.White;
xlNewSheet.Cells[3, 11] = entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[3, 11].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeTotaluse;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsTotalUse = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject totalUseChartChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsTotalUse.Add(510, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageTotalUse = totalUseChartChart.Chart;
chartPageTotalUse.HasTitle = true;
chartPageTotalUse.ChartTitle.Text = @"Entity Fields Created";
chartRangeTotaluse = xlNewSheet.get_Range("J2", "K3");
chartPageTotalUse.SetSourceData(chartRangeTotaluse, System.Reflection.Missing.Value);
chartPageTotalUse.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region CustomStandar
//add data
xlNewSheet.Cells[2, 20] = "Standard Fields";
xlNewSheet.Cells[2, 20].Font.Color = Color.White;
xlNewSheet.Cells[2, 21] = entityInfo.entityStandardFieldsCount;
xlNewSheet.Cells[2, 21].Font.Color = Color.White;
xlNewSheet.Cells[3, 20] = "Custom Fields";
xlNewSheet.Cells[3, 20].Font.Color = Color.White;
xlNewSheet.Cells[3, 21] = entityInfo.entityCustomFieldsCount;
xlNewSheet.Cells[3, 21].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeCustomStandard;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsCustomStandard = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject customStandardChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsCustomStandard.Add(1010, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageCustomStandard = customStandardChart.Chart;
chartPageCustomStandard.HasTitle = true;
chartPageCustomStandard.ChartTitle.Text = @"Custom\Standard Fields";
chartRangeCustomStandard = xlNewSheet.get_Range("T2", "U3");
chartPageCustomStandard.SetSourceData(chartRangeCustomStandard, System.Reflection.Missing.Value);
chartPageCustomStandard.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region FieldsType
//add data
int indicator = 0;
foreach (var item in entityParam)
{
xlNewSheet.Cells[indicator + 21, 10] = item.Key.ToString();
xlNewSheet.Cells[indicator + 21, 10].Font.Color = Color.White;
xlNewSheet.Cells[indicator + 21, 11] = item.Value.Count;
xlNewSheet.Cells[indicator + 21, 11].Font.Color = Color.White;
indicator++;
}
Microsoft.Office.Interop.Excel.Range chartRangeFieldType;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsFieldTypes = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject fieldTypes = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(485, 270, 350, 300);
Microsoft.Office.Interop.Excel.Chart chartPageFieldTypes = fieldTypes.Chart;
chartPageFieldTypes.HasTitle = true;
chartPageFieldTypes.ChartTitle.Text = @"Entity Fields Types";
chartRangeFieldType = xlNewSheet.get_Range("J21", ("K" + (21 + (indicator - 1))).ToString());
chartPageFieldTypes.SetSourceData(chartRangeFieldType, System.Reflection.Missing.Value);
chartPageFieldTypes.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets[1];
sheet.Activate();
workbook.SaveAs(sfd.FileName);
XcelApp.Quit();
ReleaseObject(worksheet);
ReleaseObject(xlNewSheet);
ReleaseObject(workbook);
ReleaseObject(XcelApp);
if (MessageBox.Show("Would you like to open it?", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
Process.Start(sfd.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message);
}
}
}
}
}
public void ConvertToExcel(DataSet ds, string strFilenamewithpath)
{
FnWriteLogFile_Log("", "ConvertToExcel1");
//Instance reference for Excel Application
Microsoft.Office.Interop.Excel.Application objXL = null;
//Workbook refrence
Microsoft.Office.Interop.Excel.Workbook objWB = null;
objXL = new Microsoft.Office.Interop.Excel.Application();
// Find the Excel Process Id (ath the end, you kill him
int id;
GetWindowThreadProcessId(objXL.Hwnd, out id);
Process excelProcess = Process.GetProcessById(id);
FnWriteLogFile_Log("", "ConvertToExcel3");
objWB = objXL.Workbooks.Open(Server.MapPath("~/Uploads/") + ds.Tables[0].Rows[0][0].ToString());// Server.MapPath("\Log\Feet on Street Report.xlsx");
try
{
//Instancing Excel using COM services
//Adding WorkBook
//FnWriteLogFile_Log("", "objXL.Workbooks.Add(1)");
//objWB = objXL.Workbooks.Add(1);//ds.Tables.Count // Server.MapPath("~/ExcelFile/Log/myexcel.xlsx") + ""
//Variable to keep sheet count
int sheetcount = 1;
//Do I need to explain this ??? If yes please close my website and learn abc of .net .
//oSheet = oWorkBook.Sheets.Item(1)
FnWriteLogFile_Log("", "add sheet");
Microsoft.Office.Interop.Excel.Worksheet objSHT = null;
//foreach (System.Data.DataTable dt in ds.Tables)
objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[4];
DataTable dt = ds.Tables[2];
//Adding sheet to workbook for each datatable
//Naming sheet as SheetData1.SheetData2 etc....
FnWriteLogFile_Log("", "Naming sheet");
objSHT.Name = ds.Tables[1].Rows[0][0].ToString();
for (int i = 0; i < dt.Columns.Count; i++)
{
objSHT.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
objSHT.Range[objSHT.Cells[1, i + 1], objSHT.Cells[1, i + 1]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); //# d9d9d9
//objSHT.Cells[2, i + 1].Interior.Color = System.Drawing.ColorTranslator.FromHtml("#d9d9d9"); //# d9d9d9
//objSHT.Cells[1, i + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
}
Int32 rows;
Int32 columns;
rows = dt.Rows.Count;
columns = dt.Columns.Count;
var Data = new object[rows - 1 + 1, columns - 1 + 1];
for (var i = 0; i <= dt.Rows.Count - 1; i += 1)
{
for (var j = 0; j <= dt.Columns.Count - 1; j += 1)
{
Data[i, j] = dt.Rows[i][j];
}
}
var startCell = objSHT.Cells[2, 1];
var endCell = objSHT.Cells[1 + dt.Rows.Count, dt.Columns.Count];
var writeRange = objSHT.Range[startCell, endCell];
writeRange.Value = Data;
//Incrementing sheet count
//Saving the work book
objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[1];
objSHT.Activate();
//objSHT.Select(Type.Missing);
objWB.Saved = true;
objWB.SaveCopyAs(Server.MapPath("~/Uploads/New/") + ds.Tables[0].Rows[0][0].ToString());
//Closing work book
FnWriteLogFile_Log("", "objWB.Close()");
objWB.Close();
//Closing excel application
objXL.Quit();
//Marshal.ReleaseComObject(objSHT);
FnWriteLogFile_Log("", "set null");
objSHT = null;
objWB = null;
objXL = null;
GC.Collect();
GC.WaitForPendingFinalizers();
if (objSHT != null)
{
Marshal.ReleaseComObject(objSHT);
}
if (objWB != null)
{
Marshal.ReleaseComObject(objWB);
}
if (objXL != null)
{
Marshal.ReleaseComObject(objXL);
}
FnWriteLogFile_Log("", "complete");
//HttpContext.Current.Response.End();
}
catch (Exception ex)
{
FnWriteLogFile_Log("", "Error:" + ex.StackTrace.ToString());
if (objWB != null)
{
objWB.Close();
//Closing excel application
objXL.Quit();
//Marshal.ReleaseComObject(objSHT);
// objSHT = null;
objWB = null;
objXL = null;
GC.Collect();
GC.WaitForPendingFinalizers();
//if (objSHT != null)
//{
// Marshal.ReleaseComObject(objSHT);
//}
if (objWB != null)
{
Marshal.ReleaseComObject(objWB);
}
if (objXL != null)
{
Marshal.ReleaseComObject(objXL);
}
//Response.Write("Illegal permission");
}
}
finally
{
FnWriteLogFile_Log("", "Check HasExited");
if (!excelProcess.HasExited)
{
FnWriteLogFile_Log("", "kill process");
excelProcess.Kill();
FnWriteLogFile_Log("", "end kill process");
}
}
}
/// <summary>
/// 激活一个工作表
/// </summary>
/// <param name="strName"></param>
public void ActiveSheet(string strName)
{
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)m_pWb.Worksheets[strName];
ws.Activate();
}
private void bomoToexcel()
{
DataTable _px = new DataTable();
var query = new BmobQuery();
query.WhereGreaterThan("indexID", 2651); //分数大于60岁
query.OrderBy("indexID");
query.Limit(600);
var childfuture = Bmob.FindTaskAsync <computerTiKu>("computerTiKu", query);
if (childfuture.Result.results.Count != 0)
{
_px = ToDataTable <computerTiKu>(childfuture.Result.results);
// MessageBox.Show("总行数" + _px.Rows.Count);
}
//_dspx = AdDispatchManage.GetValue(sqlstr);
int row;
row = 2;
int b;
b = 0;
string PathStr;
string SourceFileName;
string DestinationFileName;
PathStr = Application.StartupPath.Trim();
// MakeBigWord.MakeBigWord.KillExcelProcess();
DestinationFileName = PathStr + @"\plantmp.xls";
SourceFileName = PathStr + @"\yhygzpx.xls";
try
{
File.Delete(DestinationFileName);
File.Copy(SourceFileName, DestinationFileName);
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(DestinationFileName);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Worksheets[1];
xlSheet.Activate();
xlSheet.Application.Visible = true;
try
{
for (int i = 0; i < _px.Rows.Count; i++)
{
if (_px.Rows[i]["titleSubject"].ToString() != null)
{
xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString();
}
else
{
MessageBox.Show(i + "titleSubject");
}
if (_px.Rows[i]["titleSubject"].ToString() != null)
{
xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString();
}
else
{
MessageBox.Show(i + "titleSubject");
}
if (_px.Rows[i]["optionA"].ToString() != null && _px.Rows[i]["optionB"].ToString() != null && _px.Rows[i]["optionC"].ToString() != null && _px.Rows[i]["optionD"].ToString() != null)
{
Choice[] student = new Choice[4];
student[0] = new Choice();
student[0].key = "A";
student[0].content = _px.Rows[i]["optionA"].ToString();
student[1] = new Choice();
student[1].key = "B";
student[1].content = _px.Rows[i]["optionB"].ToString();
student[2] = new Choice();
student[2].key = "C";
student[2].content = _px.Rows[i]["optionC"].ToString();
student[3] = new Choice();
student[3].key = "D";
student[3].content = _px.Rows[i]["optionD"].ToString();
string aa = Newtonsoft.Json.JsonConvert.SerializeObject(student);
xlSheet.Cells[i + row, 2] = aa;
//设置一个Person类
//Choice p = new Choice();
//p.key = _px.Rows[i]["optionA"].ToString();
//p.content = _px.Rows[i]["optionB"].ToString();
//p.C = _px.Rows[i]["optionC"].ToString();
//p.D = _px.Rows[i]["optionD"].ToString();
//string json1 = JsonConvert.SerializeObject(p);
//Console.WriteLine(json1 + "\n");
////缩进输出
//string json2 = JsonConvert.SerializeObject(p, Formatting.Indented);
//Console.WriteLine(json2 + "\n");
// {"A":"+价格发现功能+","B":"+套利功能+","C":"+投机功能+","D":"+套期保值功能"}
}
else
{
MessageBox.Show(i + "A=" + _px.Rows[i]["optionA"].ToString() + "B=" + _px.Rows[i]["optionB"].ToString() + "C=" + _px.Rows[i]["optionC"].ToString() + "D=" + _px.Rows[i]["optionD"].ToString());
}
xlSheet.Cells[i + row, 3] = "3RcsJ77J";
if (_px.Rows[i]["answer"].ToString() != null)
{
String title = _px.Rows[i]["answer"].ToString().Substring(4, _px.Rows[i]["answer"].ToString().Length - 4); ///去掉两个字符串
xlSheet.Cells[i + row, 4] = "[\"" + title + "\"]";
}
else
{
MessageBox.Show(i + "answer");
}
if (_px.Rows[i]["explain"].ToString() != null)
{
xlSheet.Cells[i + row, 5] = _px.Rows[i]["explain"].ToString();
}
else
{
MessageBox.Show(i + "explain");
}
xlSheet.Cells[i + row, 6] = 500 + i;
xlSheet.Cells[i + row, 7] = "P0Vm666E";
b = i;
//xlSheet.Cells[i + row, 2] = _px.Rows[i]["indexID"].ToString();
//xlSheet.Cells[i + row, 3] = _px.Rows[i]["titleSubject"].ToString();
//xlSheet.Cells[i + row, 4] = _px.Rows[i]["answer"].ToString();
//xlSheet.Cells[i + row, 5] = _px.Rows[i]["optionA"].ToString();
//xlSheet.Cells[i + row, 6] = _px.Rows[i]["chapterflag"].ToString();
//xlSheet.Cells[i + row, 7] = _px.Rows[i]["core"].ToString();
//xlSheet.Cells[i + row, 8] = _px.Rows[i]["answerCode"].ToString();
//xlSheet.Cells[i + row, 9] = "'" + _px.Rows[i]["optionB"].ToString() + "/" + _px.Rows[i]["optionC"].ToString();
//xlSheet.Cells[i + row, 10] = _px.Rows[i]["optionD"].ToString();
//xlSheet.Cells[i + row, 11] = _px.Rows[i]["questiontype"].ToString();
//xlSheet.Cells[i + row, 12] = _px.Rows[i]["subjectType"].ToString() + "/" + _px.Rows[i]["explain"].ToString();
}
}
catch
{ }
try
{
Microsoft.Office.Interop.Excel.Range r1 = xlSheet.Range[xlSheet.Cells[3, 1], xlSheet.Cells[b + row + 4, 6]];
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = 7;
}
catch
{ }
}
