/// <summary>
/// Fill the excel sheet with data along with the position specified
/// </summary>
/// <param name="columnrow"></param>
/// <param name="data"></param>
private void FillExcelWithData(string startColumn)
{
_excelRange = _excelSheet.get_Range(startColumn, _value);
_excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount);
_excelRange.set_Value(Missing.Value, ExcelData);
_excelRange.EntireColumn.AutoFit();
}
/// <summary>
/// Method to add an excel rows
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows(string startRange, int rowCount,
int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
/// <summary>
/// fill the header columns for the range specified and make it bold if specified
/// </summary>
/// <param name="headers"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
protected void FillHeaderColumn(object[] headers, string startColumn, string endColumn)
{
_excelRange = _excelSheet.get_Range(startColumn, endColumn);
_excelRange.set_Value(_value, headers);
if (BoldHeaders == true)
{
this.BoldRow(startColumn, endColumn);
}
_excelRange.EntireColumn.AutoFit();
}
private void ExportData(List <string[]> rows, Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet)
{
object[,] rowrows = new object[rows.Count, 6];
// string[,] rowrows = new string[rows.Count,6];
int i = 0;
foreach (string[] sa in rows)
{
int j = 0;
foreach (string s in sa)
{
if (j == 4)
{
rowrows[i, j] = float.Parse(s);
}
else
{
rowrows[i, j] = s;
}
j++;
}
i++;
}
int rowCount = rowrows.GetLength(0);
int columnCount = rowrows.GetLength(1);
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[2, 1];
range = range.get_Resize(rowCount, 6);
range.set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault, rowrows);
//int rowCount = 2;
//foreach (string[] s in rows)
//{
// xlWorkSheet.Cells[rowCount, 1] = s[0];
// xlWorkSheet.Cells[rowCount, 2] = s[1];
// xlWorkSheet.Cells[rowCount, 3] = s[2];
// xlWorkSheet.Cells[rowCount, 4] = s[3];
// xlWorkSheet.Cells[rowCount, 5] = s[4];
// xlWorkSheet.Cells[rowCount, 6] = s[5];
// rowCount++;
//}
}
private void AddDocumentNoInLastRow(string filename, string docno)
{
try
{
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1];
workSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
string strRange = string.Format("B{0}", lastRow + 2);
Microsoft.Office.Interop.Excel.Range docIsoRange = workSheet.get_Range(strRange, Type.Missing);
//docIsoRange.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
docIsoRange.set_Value(System.Type.Missing, docno);
//lastCell.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
//range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(docIsoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
book.Save();
book.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}
private void tsbtn_Build_Click(object sender, EventArgs e)
{
CloseProcess("EXCEL"); //关闭所有Excel进程
object missing = System.Reflection.Missing.Value; //定义object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(tstxt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[tscbox_Sheet.Text]); //获取选择的工作表
Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range("A1", "E1"); //定义标题范围
object[] P_obj_Items = { "编程词典", "VC编程词典", "JAVA编程词典", "ASP.NET编程词典", "C#编程词典" };
searchRange.set_Value(missing, P_obj_Items); //绘制标题
searchRange.Font.Bold = true; //设置字体加粗
searchRange.Font.Name = "宋体"; //设置字体样式
searchRange.Font.Size = 10; //设置字体大小
searchRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置标题对齐方式
//获得要生成图表的数据
for (int i = 0; i < 13; i++)
{
worksheet.Cells[2 + i, 1] = i;
worksheet.Cells[2 + i, 2] = i + 1;
worksheet.Cells[2 + i, 3] = i + 2;
worksheet.Cells[2 + i, 4] = i + 3;
worksheet.Cells[2 + i, 5] = i + 4;
}
//实例化Excel绘图对象
Microsoft.Office.Interop.Excel.Chart chart = (Microsoft.Office.Interop.Excel.Chart)workbook.Charts.Add(missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Range chartRange = worksheet.get_Range("A1:A14", "B1:E14");//定义绘制图表范围
//在指定范围绘制图表
chart.ChartWizard(chartRange, Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, missing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, 1, 1, true, "编程词典销量分析", "月份", "销量", missing);
excel.DisplayAlerts = false; //设置保存Excel时不显示对话框
workbook.Save(); //保存工作簿
workbook.Close(false, missing, missing); //关闭工作簿
CloseProcess("EXCEL"); //关闭所有Excel进程
WBrowser_Excel.Navigate(tstxt_Excel.Text); //在窗体中重新显示Excel文件内容
}
private void ExportExcelWithInsertTemplate(string filename, string ext)
{
string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext);
try
{
//declare for using Ms.Excel Object
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
ObjExcel.DisplayAlerts = false;
//for template excel
Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks;
//for current excel
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName];
workSheet.Select(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//insert last row
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName];
tmpWorkSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
copyRange.EntireRow.Copy(Type.Missing);
Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-----------------------------------------------------------------------------------------------------------------------------
//Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing);
//copyLastRange.EntireRow.Copy(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-------------------------------------------------------------------------------------------------------------------------------------
book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy;
//book.CheckCompatibility = false;
tmpBook.Close(false, Type.Missing, Type.Missing);
if (this._ShippingOrder_Hdr != null)
{
//Set Shipping Order Information.
//SO_NO
Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing);
sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO);
//SO_DATE
Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing);
if (this._ShippingOrder_Hdr.SO_DATE.HasValue)
{
sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value));
}
else
{
sodateRange.set_Value(System.Type.Missing, string.Empty);
}
//Customer
Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing);
custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME));
//PO_REF_NO
Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing);
porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO);
//PO_REF_DATE
Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing);
if (this._ShippingOrder_Hdr.REF_DATE.HasValue)
{
podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value));
}
else
{
podateRange.set_Value(System.Type.Missing, string.Empty);
}
//ETD
Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing);
if (this._ShippingOrder_Hdr.ETA.HasValue)
{
etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value));
}
else
{
etdRange.set_Value(System.Type.Missing, string.Empty);
}
//REMARK
Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing);
remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK);
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange);
}
else
{
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
}
//-------------------------------------------------------------------------------------
//System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}
/// <summary>
/// Extension method to write list data to Microsoft.Office.Interop.Excel File.
/// </summary>
/// <typeparam name="T">Ganeric list</typeparam>
/// <param name="list"></param>
/// <param name="PathToSave">Path to save file.</param>
public static void ToExcel <T>(this List <T> list, string PathToSave)
{
#region Declarations
if (string.IsNullOrEmpty(PathToSave))
{
throw new Exception("Invalid file path.");
}
else if (PathToSave.ToLower().Contains("") == false)
{
throw new Exception("Invalid file path.");
}
if (list == null)
{
throw new Exception("No data to export.");
}
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbooks books = null;
Microsoft.Office.Interop.Excel._Workbook book = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel._Worksheet sheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
// Optional argument variable
object optionalValue = Missing.Value;
string strHeaderStart = "A2";
string strDataStart = "A3";
#endregion
#region Processing
try
{
#region Init Microsoft.Office.Interop.Excel app.
excelApp = new Microsoft.Office.Interop.Excel.Application();
books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks;
book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue));
sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets;
sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1));
#endregion
#region Creating Header
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ?
property.Name : attribute.DisplayName);
}
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
range.Interior.Color = Color.LightGray.ToArgb();
#endregion
#region Writing data to cell
int count = list.Count;
object[,] objData = new object[count, objHeaders.Count];
for (int j = 0; j < count; j++)
{
var item = list[j];
int i = 0;
foreach (KeyValuePair <string, string> entry in objHeaders)
{
var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i++] = (y == null) ? "" : y.ToString();
}
}
range = sheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
#endregion
#region Saving data and Opening Microsoft.Office.Interop.Excel file.
if (string.IsNullOrEmpty(PathToSave) == false)
{
book.SaveCopyAs(PathToSave);
}
excelApp.Visible = true;
#endregion
#region Release objects
try
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
sheet = null;
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
sheets = null;
if (book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
book = null;
if (books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
}
books = null;
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
}
catch (Exception ex)
{
sheet = null;
sheets = null;
book = null;
books = null;
excelApp = null;
}
finally
{
GC.Collect();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
