/// <summary>
/// 保存数据
/// </summary>
public void Save()
{
if (_mWorkbook != null)
{
_mWorkbook.Save();
}
}
public bool WriteExcelFile(string sReportName, string sSheetName, DateTime time1, DateTime time2, DataTable dt)
{
string[] date = new string[2];
date[0] = dealdate(time1);
date[1] = dealdate(time2);
StringBuilder sAddrModel = new StringBuilder(40);
StringBuilder sAddrReport = new StringBuilder(40);
string sFileModel = "";
string sFileDer = "";
string colA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
char[] colABC = new char[26] {
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
};
GetPrivateProfileString("Addr", "AddrModel", "", sAddrModel, sAddrModel.Capacity, FILE_NAME);
GetPrivateProfileString("Addr", "AddrReport", "", sAddrReport, sAddrReport.Capacity, FILE_NAME);
sFileModel = sAddrModel.ToString() + sReportName.Trim() + ".xls";
if (date[0].CompareTo(date[1]) == 0)
{
sReportName = sReportName.Trim() + "(" + date[0] + ")";
}
else
{
sReportName = sReportName.Trim() + "(" + date[0] + "-" + date[1] + ")";
}
sFileDer = sAddrReport.ToString() + sReportName.Trim() + ".xls";
if (System.IO.File.Exists(sFileModel) == false)
{
MyLog.Log("3", "模板文件不存在");
return(false);
}
if (System.IO.File.Exists(sFileDer) == false)
{
System.IO.File.Copy(sFileModel, sFileDer, true);
}
else
{
System.IO.File.Delete(sFileDer);
System.IO.File.Copy(sFileModel, sFileDer, true);
}
//从表中把数据导入SHEET中
#region
string FileName;
Excel.Workbooks eworkbooks = null;
Excel._Workbook eworkbook = null;
Excel.Sheets esheets = null;
Excel._Worksheet esheet = null;
Excel.Range range = null;
excelApp.Visible = false;
excelApp.UserControl = true;
//用Excel应用程序创建Workbooks
eworkbooks = excelApp.Workbooks;
FileName = sFileDer;
eworkbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//用Workbooks创建Workbook
eworkbook = eworkbooks.get_Item(1);
//用Workbook创建sheets
esheets = eworkbook.Worksheets;
int iSheetNumber = esheets.Count;
int index = 0;
for (int i = 1; i <= iSheetNumber; i++)
{
esheet = (Excel._Worksheet)esheets.get_Item(i);
if (esheet.Name.Trim() == sSheetName.Trim())
{
index++;
break;
}
}
if (index == 0)
{
esheet = null;//没有找到相应的SHEET页
}
else
{
int _rows = dt.Rows.Count;
int _cols = dt.Columns.Count;
int j = 4;
object[] date_write = new object[4];
date_write[0] = "日期:";
date_write[1] = date[0];
date_write[2] = "至";
date_write[3] = date[1];
range = esheet.get_Range("A" + 2, "D" + 2);
range.set_Value(Missing.Value, date);
date = null;
for (int i = 0; i < _rows; i++)
{
j = i + 5;//指定从哪一行开始写入数据
object[] currentRow = new object[_cols];
currentRow = dt.Rows[i].ItemArray;
range = esheet.get_Range("A" + j, colA.Substring(_cols - 1, 1) + j);
range.set_Value(Missing.Value, currentRow);
currentRow = null;
}
}
eworkbook.Save();
range = null;
eworkbook = null;
esheet = null;
esheets = null;
eworkbooks.Close();
#endregion//excel写数据处理
return(true);
}
public void MergeExcel(string FileName, C1.Win.C1FlexGrid.C1FlexGrid fg)
{
try
{
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.UsedRange.Cells.Borders.Value = 1;
// Thực hiện Merge
C1.Win.C1FlexGrid.CellRange cr;
object obj;
for (int i = 0; i < fg.Rows.Count; i++)
{
if (fg.Rows[i].Visible)
{
for (int j = 0; j < fg.Cols.Count; j++)
{
if (fg.Cols[j].Visible)
{
cr = fg.GetMergedRange(i, j);
if ((!cr.IsSingleCell && (cr.r1 == i)) && (cr.c1 == j))
{
oRng = oSheet.get_Range(TinhToaDo(cr.r1 + 1, cr.c1 + 1), TinhToaDo(cr.r2 + 1, cr.c2 + 1));
obj = fg[i, j];
oRng.Value2 = string.Empty;
oRng.Merge(Missing.Value);
oRng.Value2 = ReturnValue(obj);
}
}
}
}
}
// Xóa các cột, hàng đã bị ẩn
for (int i = oSheet.UsedRange.Rows.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range("A" + i.ToString(), Missing.Value);
if ((bool)oRng.EntireRow.Hidden == true)
{
oRng.EntireRow.Delete(Excel.XlDirection.xlToRight);
}
}
for (int i = oSheet.UsedRange.Columns.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range(TinhToaDo(1, i), Missing.Value);
if ((bool)oRng.EntireColumn.Hidden == true)
{
oRng.EntireColumn.Delete(Excel.XlDirection.xlDown);
}
}
oWB.Save();
oXL.Visible = true;
}
catch (Exception ex)
{
//oXL.Quit();
throw ex;
}
}
