/// <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 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>
/// 将二维数组数据写入Excel文件
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(object[,] arr, int top, int left)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.FormulaArray = arr;
}
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 ExportExcel(object obj)
{
System.Windows.Forms.SaveFileDialog opf = new System.Windows.Forms.SaveFileDialog();
opf.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
opf.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
if (opf.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filepath = opf.FileName;
new Thread(() =>
{
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//创建一个Application对象并使其不可见
app.Visible = false;
//创建一个WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Add(
Type.Missing,
workBook.ActiveSheet,
Type.Missing,
Type.Missing);
Type type = typeof(SHEBEIGUANLIModel);
int rowCount = tableList.Count + 1; //DataTable行数+GirdHead
int colCount = type.GetProperties().Count(); //DataTable列数
FieldInfo[] members = type.GetFields();
PropertyInfo[] info = type.GetProperties();
//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
int col = 0;
for (int k = 0; k < info.Count(); ++k)
{
System.Reflection.PropertyInfo item = (System.Reflection.PropertyInfo)info.ElementAt(k);
if (j == 0)
{
if (item.Name != "operateinfomodel")
{
arr[j, col] = DataGridAttach._columnNameMap[item.Name];
}
}
else if (item.PropertyType.Name.StartsWith("Int32"))
{
arr[j, k] += item.GetValue(tableList[j - 1], null);
}
else if (item.PropertyType.Name.StartsWith("Int64"))
{
arr[j, k] += item.GetValue(tableList[j - 1], null);
}
else if (item.PropertyType.Name.StartsWith("String"))
{
arr[j, k] += item.GetValue(tableList[j - 1], null);
}
else if (item.PropertyType.Name.StartsWith("Boolean"))
{
arr[j, k] += item.GetValue(tableList[j - 1], null);
}
else
{
;
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
workBook.SaveAs(filepath, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();//强制代码垃圾回收
}).Start();
}
}
/// <summary>
/// Using the Interop.Excel to get DS Data
/// </summary>
/// <param name="password"></param>
/// <param name="excelFilePath"></param>
/// <param name="orderingClumnNames"></param>
/// <param name="OrderDTClumnNameIndex"></param>
/// <param name="sheetInde"></param>
/// <returns></returns>
public DataSet ExcelToDS(string password, string excelFilePath, string orderingClumnNames, int[] OrderDTClumnNameIndex, int sheetInde = 1)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
try
{
excel.Visible = false;//设置调用引用的 Excel文件是否可见
excel.DisplayAlerts = false;
if (String.IsNullOrEmpty(password))
{
wb = excel.Workbooks.Open(excelFilePath);
}
else
{
wb = excel.Workbooks.Open(excelFilePath, missing, false, missing, password, password, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);
}
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[sheetInde];
int rowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.Columns.Count;
int maxClumnNameIndex = 0;
for (int i = 0; i < OrderDTClumnNameIndex.Length; i++)
{
if (maxClumnNameIndex < OrderDTClumnNameIndex[i])
{
maxClumnNameIndex = OrderDTClumnNameIndex[i];
}
}
range = (Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1];
range = range.get_Resize(rowCount, maxClumnNameIndex + 2);
object[,] objArray = range.Value2;
DataTable orderDataTable = new DataTable();
orderDataTable.TableName = "table1";
var columnNameList = orderingClumnNames.Split(',');
for (int i = 0; i < columnNameList.Length; i++)
{
orderDataTable.Columns.Add(columnNameList[i], typeof(String));
}
for (int i = 0; i < rowCount - 1; i++)
{
orderDataTable.Rows.Add();
for (int j = 0; j < columnNameList.Length; j++)
{
if (OrderDTClumnNameIndex[j] != -1)
{
var objVal = objArray[i + 1, OrderDTClumnNameIndex[j] + 1];
if (objVal != null)
{
var text = objVal.ToString();
orderDataTable.Rows[i][j] = String.IsNullOrEmpty(text) ? "" : text;
}
}
else
{
orderDataTable.Rows[i][j] = "N/A";
}
}
}
DataSet retDataSet = new DataSet();
retDataSet.Tables.Add(orderDataTable);
return(retDataSet);
}
catch (Exception ex)
{
var ErrorMessage = string.Format("方法：{0}, 错误提示：{1},堆栈信息：{2}", "GetOrderDT", ex.Message, ex.StackTrace);
Log.Write(ErrorMessage);
throw ex;
}
finally
{
if (wb != null)
{
wb.Close(true, Type.Missing, Type.Missing);
}
if (excel != null)
{
excel.Workbooks.Close();
excel.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (ws != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
}
if (wb != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
CommonMethod.KillAllExcelProcess();
GC.Collect();
}
}
/// <summary>
/// using the Interop.Excel to insert data into the Excel.
/// </summary>
/// <param name="password"></param>
/// <param name="excelFilePath"></param>
/// <param name="insertDataTable"></param>
/// <param name="cloumNameIndexs"></param>
public void InsertDataIntoExcel(string password, string excelFilePath, DataTable insertDataTable, int[] cloumNameIndexs)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
try
{
if (insertDataTable.Rows.Count > 0)
{
excel.Visible = false;//设置调用引用的 Excel文件是否可见
excel.DisplayAlerts = false;
if (String.IsNullOrEmpty(password))
{
wb = excel.Workbooks.Open(excelFilePath);
}
else
{
wb = excel.Workbooks.Open(excelFilePath, missing, false, missing, password, password, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);
}
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
int topRowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.Columns.Count;
if (insertDataTable.Rows.Count > 0)
{
var rowCount = insertDataTable.Rows.Count;
var columnCount = insertDataTable.Rows[0].ItemArray.Count();
var startIndex = topRowCount + 1;
#region old updating method
//Log.Write("------------------------insert single data into Excel ---------------------");
//for (int i = 0; i < rowCount; i++)
//{
// var rowItem = insertDataTable.Rows[i];
// for (int j = 0; j < columnCount; j++)
// {
// ws.Cells[startIndex + i, cloumNameIndexs[j] + 1].Value = rowItem[j];
// }
//}
//Log.Write("------------------------insert single data into Excel ---------------------");
#endregion
#region insert multiple data into Excel
Log.Write("------------------------insert multiple data into Excel ---------------------");
int maxClumnNameIndex = 0;
for (int i = 1; i < cloumNameIndexs.Length; i++)
{
if (maxClumnNameIndex < cloumNameIndexs[i])
{
maxClumnNameIndex = cloumNameIndexs[i];
}
}
object[,] objValue = new object[rowCount, maxClumnNameIndex + 2];
//for (int i = 0; i < rowCount; i++)
//{
// var rowItem = insertDataTable.Rows[i];
// for (int j = 0; j < maxClumnNameIndex + 2; j++)
// {
// objValue[i, j] = String.Empty;
// }
//}
for (int i = 0; i < rowCount; i++)
{
var rowItem = insertDataTable.Rows[i];
for (int j = 0; j < columnCount; j++)
{
if (rowItem[j] != null)
{
var val = Convert.ToString(rowItem[j]);
objValue[i, cloumNameIndexs[j]] = String.IsNullOrEmpty(val) ? "" : val;
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)ws.Cells[startIndex, 1];
range = range.get_Resize(rowCount, maxClumnNameIndex + 2);
range.FormulaArray = objValue;
Log.Write("------------------------insert multiple data into Excel ---------------------");
#endregion
}
excel.ActiveWorkbook.Save();
}
}
catch (Exception ex)
{
var ErrorMessage = string.Format("方法：{0}, 错误提示：{1},堆栈信息：{2}", "InsertDataIntoExcel", ex.Message, ex.StackTrace);
Log.Write(ErrorMessage);
throw ex;
}
finally
{
if (wb != null)
{
wb.Close(true, Type.Missing, Type.Missing);
}
if (excel != null)
{
excel.Workbooks.Close();
excel.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (ws != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
}
if (wb != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
CommonMethod.KillAllExcelProcess();
GC.Collect();
}
}
private void ExportExcel(object obj)
{
System.Windows.Forms.SaveFileDialog opf = new System.Windows.Forms.SaveFileDialog();
opf.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
opf.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
if (opf.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filepath = opf.FileName;
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//创建一个Application对象并使其不可见
app.Visible = false;
//创建一个WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Add(
Type.Missing,
workBook.ActiveSheet,
Type.Missing,
Type.Missing);
int rowCount = 1; //DataTable行数+GirdHead
int colCount = 1; //DataTable列数
//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
if (j == 0)
{
arr[j, k] = "测试速度:";
}
else
{
arr[j, k] = statusText;
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
workBook.SaveAs(filepath, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();//强制代码垃圾回收
}
}
/// <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
}
/// <summary>
/// Method to make columns auto fit according to data
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
/// <summary>
/// 写入Excel的Sheet表
/// </summary>
/// <param name="wb"></param>
/// <param name="tables"></param>
private void WriteExcelSheet(Microsoft.Office.Interop.Excel.Workbook wb)
{
List <string> SheetNames = GetSheetsName(wb); //Sheet表名字集合,防止相同名称,出现错误!
for (int i = 0; i < _tables.Count; i++)
{
System.Data.DataTable table = _tables[i];
int rows = table.Rows.Count;
int cols = table.Columns.Count;
//获取Sheet表
Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i + 1]; //wb.Worksheets 所以从1开始
//选中表
wsheet.Select();
//设置表名字
wsheet.Name = GetCorrectSheetName(SheetNames, table.TableName, (i + 1)); //Name要特别注意
//保存DataTable和Sheet表的绑定
SheetTable.Add(wsheet.Name, table);
//设置总标题
DrawTitle(wsheet, cols);
//设置HeaderText
if (IsDrawHeader)
{
DrawText(wsheet, _headerdtext, _headerfont, TextAlign.xlHAlignLeft, cols);// Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, cols);
}
//数据列表标题
//Microsoft.Office.Interop.Excel.Range r = ret.get_Range(ret.Cells[1, 1], ret.Cells[1, table.Columns.Count]);
if (_isbodylistheader)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, cols]];
object[] header = new object[cols];
for (int j = 0; j < cols; j++)
{
header[j] = table.Columns[j].ToString();
}
range.Value2 = header;
SetFont(range, _bodyheaderfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //边框线
range.Borders.Weight = (int)_borderweight;
}
}
if (rows > 0)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.get_Range("A" + _rowindex, Missing.Value);
object[,] objData = new Object[rows, cols];
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
string converttxt = String.Empty;
if (_isautoconverttext)
{
converttxt = "'";
}
objData[row, col] = converttxt + table.Rows[row][col].ToString(); //随后数据后面加单引号
}
}
range = range.get_Resize(rows, cols);
range.Value2 = objData;
SetFont(range, _bodyfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //加线框
range.Borders.Weight = (int)_borderweight;
}
else
{
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.LineStyle = 1;
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
//指定的列显示边框
foreach (var vk in _columnsborder)
{
Microsoft.Office.Interop.Excel.Borders borders = ((Microsoft.Office.Interop.Excel.Range)range.Columns[vk.Key, Type.Missing]).Borders;
borders.LineStyle = 1;
borders.Weight = (int)vk.Value;
}
}
if (IsAutoFit)
{
range.EntireColumn.AutoFit(); //自动适应列
}
//更新行索引
_rowindex += (rows - 1);
}
//设置FooterText
if (IsDrawFooter)
{
if (rows == 0)
{
_rowindex += 2; //与Body隔一行
}
DrawText(wsheet, _footertext, _footerfont, DrawFooterTextAlign, cols);
}
//所有列自动换行
wsheet.Columns.WrapText = _iswraptext;
//设置列宽
if (!IsAutoFit)
{
foreach (var vk in _columnswidth)
{
((Microsoft.Office.Interop.Excel.Range)wsheet.Columns[vk.Key, System.Type.Missing]).ColumnWidth = vk.Value;
}
}
//保存名字
SheetNames.Add(wsheet.Name);
//还原
_rowindex = _saveindex;
}
//选中第一个表
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]).Select();
}
private void dataGridViewExportAsExcel(DataGridView dgv, string FileFullPath)
{
string strExamPath = FileFullPath.Substring(0, FileFullPath.LastIndexOf('\\'));
if (!System.IO.Directory.Exists(strExamPath))
{
MessageBox.Show(FileFullPath, "目录错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.EnableEvents = false;
excelApplication.Application.DisplayAlerts = false;
excelApplication.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
excelApplication.Visible = false;
int nRowIndex = 0;
int nColumnIndex = 0;
object[,] strArr = new object[gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount];
foreach (DataGridViewColumn dgvc in gvDataRecord.Columns)
{
strArr[nRowIndex, nColumnIndex] = dgvc.HeaderText;
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
long lb = System.DateTime.Now.Ticks;
foreach (DataGridViewRow dgvr in gvDataRecord.Rows)
{
foreach (DataGridViewCell dgvcell in dgvr.Cells)
{
strArr[nRowIndex, nColumnIndex] = dgvcell.Value.ToString();
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
}
long le = System.DateTime.Now.Ticks;
System.Diagnostics.Trace.WriteLine(le - lb);
string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(gvDataRecord.ColumnCount, gvDataRecord.RowCount + 1);
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex);
myRange.get_Resize(gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount);
try
{
myRange.Value2 = strArr;
lb = System.DateTime.Now.Ticks;
myRange.Columns.AutoFit();
le = System.DateTime.Now.Ticks;
System.Diagnostics.Trace.WriteLine(le - lb);
myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("文件成功保存到了" + FileFullPath, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
excelApplication.Quit();
killexcel(excelApplication);
GC.Collect();
}
public static bool DataTableExportAsExcel(DataTable dt, string FileFullPath)
{
if (!System.IO.Directory.Exists(Path.GetDirectoryName(FileFullPath)))
{
CreatePath(FileFullPath);
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.EnableEvents = false;
excelApplication.Application.DisplayAlerts = false;
excelApplication.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
excelApplication.Visible = false;
int nRowIndex = 0;
int nColumnIndex = 0;
int ColumnCount = dt.Columns.Count;
int RowCount = dt.Rows.Count;
object[,] strArr = new object[RowCount + 1, ColumnCount];
foreach (DataColumn col in dt.Columns)
{
strArr[nRowIndex, nColumnIndex] = col.ColumnName;
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < ColumnCount; i++)
{
strArr[nRowIndex, nColumnIndex] = row[i].ToString();
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
}
string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(ColumnCount, RowCount + 1);
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex);
myRange.get_Resize(RowCount + 1, ColumnCount);
try
{
myRange.Value2 = strArr;
myRange.Columns.AutoFit();
myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
return(false);
}
excelApplication.Quit();
killexcel(excelApplication);
GC.Collect();
return(true);
}
private void dataToExelMainMenuItem_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;
object obj = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(obj);
Microsoft.Office.Interop.Excel.Sheets ss = wb.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)ss.get_Item(1);
//object[] headers = { "First", "Second", "Third1111111111" };
//Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", "C1");
//range.Value2 = headers;
//range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatReport1, obj, obj, obj, obj, obj, obj);
//range.Font.Bold = true;
//range = ws.get_Range("A2", obj);
//range = range.get_Resize(3, 3);
//range.Borders.Value = true;
////range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj);
//object[,] data = { { "1", "2", "3" }, { "4", "5", "6" }, { "7", "8", "9" } };
//range.Value2 = data;
//excel.Visible = true;
DataGridView grid = tabControl1.SelectedTab.Controls[0] as DataGridView;
Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", obj);
range = range.get_Resize(grid.Rows.Count, 6);
object[,] data = new object[grid.Rows.Count, 6];
data[0, 0] = "Дата";
data[0, 1] = "Время";
data[0, 2] = "Широта";
data[0, 3] = "Долгота";
data[0, 4] = "От предыдущей, м";
data[0, 5] = "Всего, м";
for (int i = 0; i < grid.Rows.Count - 1; i++)
{
for (int j = 0; j < 6; j++)
{
data[i + 1, j] = grid[j, i].Value;
}
}
range.Value2 = data;
range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj);
excel.Visible = true;
}
#pragma warning disable 168
catch (NullReferenceException except)
{
MessageBox.Show("Нет данных для отображения", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception except)
{
MessageBox.Show("При передаче данных в Excel произошла ошибка", "Ошибка-", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#pragma warning restore 168
}
public static void DataGridViewtoExcel(DataGridView tmpDataTable, String strFileName)
{
if (tmpDataTable == null)
{
return;
}
string lvssss = Application.StartupPath + "\\Microsoft.Office.Interop.Excel.dll";
Assembly ass;
object obj;
//获取并加载DLL类库中的程序集
ass = Assembly.LoadFile(lvssss);
//获取类的类型：必须使用名称空间+类名称
obj = ass.CreateInstance("Microsoft.Office.Interop.Excel.ApplicationClass");
Microsoft.Office.Interop.Excel.Application xlApp = obj as Microsoft.Office.Interop.Excel.Application;
try
{
xlApp.Visible = true;
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel._Worksheet xlSheet = xlBook.ActiveSheet as Microsoft.Office.Interop.Excel._Worksheet;
object m_objOpt = System.Reflection.Missing.Value;
int lv总行数 = tmpDataTable.Rows.Count;
int lv总列数 = tmpDataTable.Columns.Count;
object[,] objData = new Object[1, lv总列数];
int lv列_流水 = 0;
for (int i = 0; i < tmpDataTable.Columns.Count; i++)
{
DataGridViewColumn dc = tmpDataTable.Columns[i];
if (dc.Visible == true && dc.GetType().Name != "DataGridViewButtonColumn")
{
objData[0, lv列_流水] = dc.HeaderText;
lv列_流水++;
}
}
xlSheet.Range["A1"].Select();
Microsoft.Office.Interop.Excel.Range m_objRange = null;
m_objRange = xlSheet.get_Range("A1", m_objOpt);
m_objRange = m_objRange.get_Resize(1, lv总列数);
m_objRange.Value = objData;
string lvTmpvalue;
decimal lviTmpValue;
int lv行数_每批导出最大行 = 2000;
int lv复制总次数 = lv总行数 / lv行数_每批导出最大行 + 1;
int lv行序号_相对总行数 = 0; //相对总行数
lv列_流水 = 0;
int lv本次复制开始行 = 0;
int lv本次可复制行数 = 0;
while (lv行序号_相对总行数 < lv总行数)
{
lv本次复制开始行 = lv行序号_相对总行数;
//如果剩余行数 大于 固定导出行数 则本次 导出 固定行数
lv本次可复制行数 = lv总行数 - lv行序号_相对总行数;
if (lv本次可复制行数 > lv行数_每批导出最大行)
{
lv本次可复制行数 = lv行数_每批导出最大行;
}
int lv行_本次复制流水 = 0;
objData = new Object[lv本次可复制行数, lv总列数];
while (lv行_本次复制流水 < lv本次可复制行数)
{
lv本次复制开始行 = lv行序号_相对总行数;
lv列_流水 = 0;
for (int j = 0; j < lv总列数; j++)
{
if (tmpDataTable.Columns[j].Visible == true && tmpDataTable.Columns[j].GetType().Name != "DataGridViewButtonColumn")
{
if (tmpDataTable.Rows[lv本次复制开始行 + lv行_本次复制流水].Cells[j].Value == null)
{
objData[lv行_本次复制流水, lv列_流水] = "";
}
else
{
lvTmpvalue = tmpDataTable.Rows[lv本次复制开始行 + lv行_本次复制流水].Cells[j].Value.ToString().Trim();
if (decimal.TryParse(lvTmpvalue, out lviTmpValue))
{
if (lviTmpValue == 0)
{
lvTmpvalue = "";
}
}
objData[lv行_本次复制流水, lv列_流水] = lvTmpvalue;
}
lv列_流水++;
}
}
lv行_本次复制流水++;
}
lv行序号_相对总行数 = lv行序号_相对总行数 + lv本次可复制行数;
m_objRange = xlSheet.get_Range(xlSheet.Cells[lv本次复制开始行 + 1, 1], xlSheet.Cells[lv本次复制开始行 + lv本次可复制行数, lv总列数]);
//m_objRange = m_objRange.get_Resize(lv包含标题总行数, lv总列数);
m_objRange.Value = objData;
}
xlBook.SaveCopyAs(System.Web.HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
xlBook.Saved = true;
xlApp.Quit();
}
catch// (Exception ex)
{ xlApp.Quit(); throw; }
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
//Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(strFileName); //引用Excel工作簿
//excel.Visible = true;
//xlBook.SaveAs(strFileName,System.Text.Encoding.UTF8);
}
public static void DataGridViewtoExcel(List <DataGridView> Grids4Excel, List <string> GridText, string strDefaultFileName)
{
if (Grids4Excel == null)
{
return;
}
if (Grids4Excel.Count == 0)
{
return;
}
SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
saveFileDialog1.FileName = strDefaultFileName;
saveFileDialog1.DefaultExt = "*.xls";
saveFileDialog1.Filter = "Excel文件|*.xls";
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
int rowNum;
int columnNum;
int columnIndex = 1;
int maxLineCount;
string lvssss = Application.StartupPath + "\\Microsoft.Office.Interop.Excel.dll";
Assembly ass;
object obj;
ass = Assembly.LoadFile(lvssss);
obj = ass.CreateInstance("Microsoft.Office.Interop.Excel.ApplicationClass");
Microsoft.Office.Interop.Excel.Application xlApp = obj as Microsoft.Office.Interop.Excel.Application;
xlApp.Visible = true;
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = Grids4Excel.Count;
object m_objOpt = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
DataGridView lvGrid;
for (int lvSheetNo = 0; lvSheetNo < Grids4Excel.Count; lvSheetNo++)
{
lvGrid = Grids4Excel[lvSheetNo];
rowNum = lvGrid.Rows.Count;
columnNum = lvGrid.Columns.Count;
columnIndex = 1;
maxLineCount = rowNum + 1;
object[,] objData = new Object[maxLineCount, columnNum];
columnIndex = 0;
for (int i = 0; i < lvGrid.Columns.Count; i++)
{
DataGridViewColumn dc = lvGrid.Columns[i];
if (dc.Visible == true && dc.GetType().Name != "DataGridViewButtonColumn")
{
objData[0, columnIndex] = dc.HeaderText;
columnIndex++;
}
}
string lvTmpvalue;
decimal lviTmpValue;
for (int r = 0; r < rowNum; r++)
{
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
if (lvGrid.Columns[j].Visible == true && lvGrid.Columns[j].GetType().Name != "DataGridViewButtonColumn")
{
if (lvGrid.Rows[r].Cells[j].Value == null)
{
objData[r + 1, columnIndex] = "";
}
else
{
lvTmpvalue = lvGrid.Rows[r].Cells[j].Value.ToString().Trim();
if (decimal.TryParse(lvTmpvalue, out lviTmpValue))
{
if (lviTmpValue == 0)
{
lvTmpvalue = "";
}
}
objData[r + 1, columnIndex] = lvTmpvalue;
}
columnIndex++;
}
}
}
//写入excel
if (xlBook.Sheets.Count < lvSheetNo + 1)
{
xlBook.Sheets.Add(m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
//xlBook.Sheets.get_Item(lvSheetNo + 1);
Microsoft.Office.Interop.Excel._Worksheet xlSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xlBook.Sheets.get_Item(1));
//xlSheet.Range["A1"].Select();
xlSheet.Activate();
xlSheet.Name = GridText[lvSheetNo];
Microsoft.Office.Interop.Excel.Range m_objRange = null;
m_objRange = xlSheet.get_Range("A1", m_objOpt);
m_objRange = m_objRange.get_Resize(maxLineCount, columnNum);
m_objRange.Value = objData;
}
try
{
xlBook.SaveCopyAs(System.Web.HttpUtility.UrlDecode(saveFileDialog1.FileName, System.Text.Encoding.UTF8));
xlBook.Saved = true;
xlApp.Quit();
HelpTXD.ShowInfo("导出完成。" + saveFileDialog1.FileName);
}
catch //(Exception ex)
{
xlBook.Saved = true;
xlApp.Quit();
throw;
}
}
