private static int CopyDataToClipBoard(ExcelInfo sourceExcelInfo, Excel._Worksheet firstWorksheet)
{
if (sourceExcelInfo.ColumnCount == 0 || sourceExcelInfo.RowsCount == 0)
{
firstWorksheet.UsedRange.Copy(ObjOpt);
return firstWorksheet.UsedRange.Rows.Count;
}
var startCell = SYSTEM.WindowsTools.ConvertIntToChar(sourceExcelInfo.ColumnStart) +
sourceExcelInfo.RowStart.ToString(CultureInfo.InvariantCulture);
var endCell = SYSTEM.WindowsTools.ConvertIntToChar(sourceExcelInfo.ColumnStart + sourceExcelInfo.ColumnCount - 1) +
(sourceExcelInfo.RowStart + sourceExcelInfo.RowsCount - 1).ToString(CultureInfo.InvariantCulture);
var sourceRange = firstWorksheet.Range[startCell, endCell];
sourceRange.Copy(ObjOpt);
Console.WriteLine("Copy [{0}][{1}]", startCell, endCell);
return 0;
}
/// <summary>
/// 将超过255列Sql查询结果插入到指定Excel文件中
/// </summary>
/// <param name="sql">sql查询语句(注：SQL中需要包含数据库名)</param>
/// <param name="excelInfo">导出EXCEL信息</param>
/// <param name="dataBaseEngineType">数据库引擎类型</param>
/// <returns>操作结果</returns>
public static string ExportLargeSqlToExcel(string sql, ExcelInfo excelInfo, DataBaseEngineType dataBaseEngineType = DataBaseEngineType.MsSqlServer)
{
MSSQL.Tools.DataBaseType = dataBaseEngineType;
var result = MSSQL.Tools.ExportCSV(sql, excelInfo.FilePath.Replace(".xlsx", ".csv"));
return result != "" ? result : CopyExcelData(new ExcelInfo { FilePath = excelInfo.FilePath.Replace(".xlsx", ".csv") }, excelInfo);
}
private static void SaveDataToExcelImpl(DataTable dataTable, ExcelInfo excelInfo, ExcelInfo dataTableInfo=null)
{
var rowCountUsing = dataTable.Rows.Count;
if (dataTable.Rows.Count > excelInfo.RowsCount && excelInfo.RowsCount != 0)
{
rowCountUsing = excelInfo.RowsCount;
}
var colCountUsing = dataTable.Columns.Count;
if (dataTable.Columns.Count > excelInfo.ColumnCount && excelInfo.ColumnCount != 0)
{
colCountUsing = excelInfo.ColumnCount;
}
if (excelInfo.IsInsert == "1")
{
InsertRow(excelInfo.RowStart, rowCountUsing);
}
if (excelInfo.HasTitle == "1")
{
SetTitle(excelInfo.RowStart, excelInfo.ColumnStart, colCountUsing, dataTable);
excelInfo.RowStart++;
}
if (excelInfo.SheetName != "" && _objSheet.Name == "sheet1")
{
_objSheet.Name = excelInfo.SheetName;
}
Console.WriteLine("Insert data into excel file");
if (dataTable.Rows.Count > 0)
{
var i = 0;
var j = 0;
if (dataTableInfo != null)
{
i = dataTableInfo.RowStart;
j = dataTableInfo.ColumnStart;
}
while (i<rowCountUsing)
{
_tmpNumber = 0;
while (j < colCountUsing)
{
SetCellValue(i + excelInfo.RowStart, j + excelInfo.ColumnStart, dataTable.Rows[i][j].ToString(),i);
Console.WriteLine("row:{0},col:{1}", i + 1, j + 1);
j++;
}
if (rowCountUsing % 20 == 0)
{
Console.WriteLine("【{0}】/【{1}】 rows of data inserted", i, rowCountUsing);
}
i++;
}
}
Console.WriteLine("Insert finished\n");
}
private static void CopyDataFromClipBoard(ExcelInfo destinationExcelInfo, int index = 1)
{
var startRow = (index - 1) * destinationExcelInfo.RowsCount + destinationExcelInfo.RowStart;
var startCell = SYSTEM.WindowsTools.ConvertIntToChar(destinationExcelInfo.ColumnStart) +
startRow.ToString(CultureInfo.InvariantCulture);
var destinationRange = _objSheet.Range[startCell];
_objSheet.Paste(destinationRange, false);
Console.WriteLine("Paste [{0}]", startCell);
}
private static Excel._Worksheet OpenSheet(ExcelInfo excelInfo, Excel._Workbook workbook = null)
{
var objSheets = _objBook.Worksheets;
if (workbook != null)
{
objSheets = workbook.Worksheets;
}
if (string.IsNullOrEmpty(excelInfo.SheetName))
{
return (Excel._Worksheet)(objSheets.Item[1]);
}
try
{
Console.WriteLine("Open Sheet:【{0}】", excelInfo.SheetName);
return (Excel._Worksheet)(objSheets.Item[excelInfo.SheetName]);
}
catch (Exception)
{
CloseExcel();
return null;
}
}
private static string SaveDataToSingleExcel(List<DataTable> dataTables, List<ExcelInfo> excelInfos, ExcelInfo dataTableInfo = null)
{
try
{
OpenExcel(excelInfos[0], false);
for (int n = 0; n < dataTables.Count; n++)
{
_objSheet = OpenSheet(excelInfos[n]);
if (_objSheet == null)
{
return "没有指定页签";
}
SaveDataToExcelImpl(dataTables[n], excelInfos[n], dataTableInfo);
}
SaveAs(excelInfos[0].FilePath);
}
catch (Exception err)
{
CloseExcel();
Console.WriteLine(err.Message);
return err.Message;
}
return "";
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
/// <summary>
/// 获取Excel中页签名称列表
/// </summary>
/// <param name="excelInfo">Excel信息</param>
/// <returns>页签名称列表</returns>
public static List<string> GetSheetName(ExcelInfo excelInfo)
{
var sheetNameList = new List<string>();
using (var conn = new OleDbConnection(
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{excelInfo.FilePath}\";Extended Properties='{GetExcelVersionString()};HDR=YES;IMEX=0'"))
{
conn.Open();
var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt == null)
{
return sheetNameList;
}
foreach (DataRow row in dt.Rows)
{
var tmp = row[2].ToString();
if (tmp.ToLower().Contains("print_area"))
{
continue;
}
sheetNameList.Add(tmp.Replace("$", "").Replace("'", ""));
}
}
return sheetNameList;
}
/// <summary>
/// 隐藏页签
/// </summary>
/// <param name="excelInfo">EXCEL信息</param>
/// <param name="sheetNames">待隐藏页签列表</param>
/// <returns>操作结果</returns>
public static string HideSheets(ExcelInfo excelInfo, List<string> sheetNames)
{
try
{
OpenExcel(excelInfo);
foreach (Excel.Worksheet worksheet in _objBook.Worksheets)
{
foreach (var worksheetName in sheetNames)
{
if (worksheet.Name != worksheetName) continue;
worksheet.Name = worksheet.Name + "(无)";
worksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
}
Save();
}
catch (Exception ex)
{
CloseExcel();
return ex.Message;
}
return "";
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="excelInfo">需要设置单元格边框的excel信息</param>
/// <returns>操作结果</returns>
public static string SetCellBorder(ExcelInfo excelInfo)
{
try
{
OpenExcel(excelInfo);
if (excelInfo.RowsCount == 0 || excelInfo.ColumnCount == 0)
{
_objSheet.UsedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
var startCell = SYSTEM.WindowsTools.ConvertIntToChar(excelInfo.ColumnStart) + excelInfo.RowStart;
var endCell = SYSTEM.WindowsTools.ConvertIntToChar(excelInfo.ColumnStart + excelInfo.ColumnCount - 1)
+ (excelInfo.RowStart + excelInfo.RowsCount - 1);
var range = _objSheet.Range[startCell, endCell];
range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
Save();
}
catch (Exception ex)
{
CloseExcel();
return ex.Message;
}
return "";
}
/// <summary>
/// EXCEL间数据拷贝
/// </summary>
/// <param name="sourceExcelInfo">源数据EXCEL文件信息</param>
/// <param name="destinationExcelInfo">拷贝目标EXCEL文件信息</param>
/// <param name="copyTime">拷贝次数</param>
/// <returns>拷贝结果</returns>
public static string CopyExcelData(ExcelInfo sourceExcelInfo, ExcelInfo destinationExcelInfo, int copyTime = 1)
{
try
{
if (destinationExcelInfo.RowStart < 1 || destinationExcelInfo.ColumnStart < 1)
{
return @"目标excel初始行列应>0";
}
var result = OpenExcel(destinationExcelInfo);
if (result != "")
{
return result;
}
var sourceBook = _objBook;
var sourceSheet = _objSheet;
if (sourceExcelInfo.FilePath != destinationExcelInfo.FilePath)
{
sourceBook = _objBooks.Open(sourceExcelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
sourceSheet = OpenSheet(sourceExcelInfo, sourceBook);
if (sourceSheet == null)
{
return "没有指定页签";
}
}
var rowCount = CopyDataToClipBoard(sourceExcelInfo, sourceSheet);
if (destinationExcelInfo.RowsCount == 0)
{
destinationExcelInfo.RowsCount = rowCount;
}
for (var n = 1; n <= copyTime; n++)
{
CopyDataFromClipBoard(destinationExcelInfo, n);
}
//sourceBook.Close(false, ObjOpt, ObjOpt);
ReleaseObj(sourceSheet);
ReleaseObj(sourceBook);
Clipboard.Clear();
Save();
}
catch (Exception err)
{
CloseExcel();
Console.WriteLine(err.Message);
return err.Message;
}
return "";
}