//
// Summary:
// copy the style of old range to new range
//
// Parameters:
// to:
// the new range where style will applied to
// from:
// the old range where style will be copied
public static ExcelRange CopyStyleFrom(this ExcelRange to, ExcelRange from, bool withValue = false)
{
var fromIndex = from.GetRangeIndex();
var toIndex = to.GetRangeIndex();
var offset = fromIndex[3] - fromIndex[1] + 1;
var range = toIndex[3] - toIndex[1] + 1;
var rest = range % offset;
var loop = (range - rest) / offset;
var index = 0;
for (; index < loop; index++)
{
var tmpRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + (index + 1) * offset, fromIndex[2], fromIndex[3] + (index + 1) * offset });
from.Copy(tmpRange);
if (!withValue)
{
tmpRange.ForEach(t => t.Value = "");
}
}
if (rest > 0)
{
var restRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + index * offset, fromIndex[2], fromIndex[1] + index * offset + rest - 1 });
var x = from.Start.Address.AddressToNumber();
var y = from.End.Address.AddressToNumber();
var restFrom = from[x[0], x[1], y[0], x[1] + rest - 1];
restFrom.Copy(restRange);
if (!withValue)
{
restRange.ForEach(t => t.Value = "");
}
}
return(to);
}
/// <summary>
/// Insert new row, copy format row i to new row, set value
/// </summary>
/// <param name="ws"></param>
/// <param name="def"></param>
/// <param name="row"></param>
private void ApplyLoopDataRow(ExcelWorksheet ws, CExcelTemplateDefinition def, DataRow row)
{
string templateRange = "";
templateRange = "A{0}:" + def.definedColumnField[def.definedColumnField.Count - 1].address + "{0}";
templateRange = string.Format(templateRange, def.loopDataRowIndex);
ExcelRange rowTemplate = ws.Cells[templateRange];
double rowHeight = ws.Row(def.loopDataRowIndex).Height;
int pasteRow = row.Table.Rows.IndexOf(row) + def.loopDataRowIndex + 1;
string pasteAddress = "A" + pasteRow;
ws.InsertRow(pasteRow, 1);
ws.Row(pasteRow).Height = rowHeight;
rowTemplate.Copy(ws.Cells[pasteAddress]);
for (int i = 0; i < def.definedColumnField.Count; i++)
{
if (row.Table.Columns.Contains(def.definedColumnField[i].value))
{
ws.Cells[def.definedColumnField[i].address + pasteRow].Value = row[def.definedColumnField[i].value];
}
}
}
/// <summary>
/// copy the style of old range to new range
/// </summary>
/// <param name="to">the new range where style will applied to</param>
/// <param name="offset">the old range where style will be copied</param>
/// <param name="skip">the number of rows or columns to skip</param>
/// <returns>The <paramref name="ExcelRange"/> after new style applied</returns>
public static ExcelRange CopyStyleFrom(this ExcelRange to, ExcelRange from, InsertMode mode = InsertMode.ColumnRight, bool withValue = false, int skip = 0)
{
var fromIndex = from.GetRangeIndex();
var toIndex = to.GetRangeIndex();
var offset = (mode.In(InsertMode.RowAfter, InsertMode.RowBefore) ? fromIndex[2] - fromIndex[0] : fromIndex[3] - fromIndex[1]) + 1;
var range = (mode.In(InsertMode.RowAfter, InsertMode.RowBefore) ? toIndex[2] - toIndex[0] : toIndex[3] - toIndex[1]) + 1;
var rest = range % offset;
var loop = (range - rest) / offset;
var index = 0;
for (; index < loop; index++)
{
ExcelRange tmpRange = null;
if (mode.In(InsertMode.RowAfter, InsertMode.RowBefore))
{
tmpRange = to.GetRange(new[] { fromIndex[0] + (index + 1 + skip) * offset, fromIndex[1], fromIndex[2] + (index + 1 + skip) * offset, fromIndex[3] });
}
else
{
tmpRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + (index + 1 + skip) * offset, fromIndex[2], fromIndex[3] + (index + 1 + skip) * offset });
}
from.Copy(tmpRange);
if (!withValue)
{
tmpRange.ForEach(t => t.Value = "");
}
}
if (rest > 0)
{
var restRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + index * offset, fromIndex[2], fromIndex[1] + index * offset + rest - 1 });
var x = from.Start.Address.AddressToNumber();
var y = from.End.Address.AddressToNumber();
var restFrom = from[x[0], x[1], y[0], x[1] + rest - 1];
restFrom.Copy(restRange);
if (!withValue)
{
restRange.ForEach(t => t.Value = "");
}
}
return(to);
}
/// <summary>
/// 填充报表数据
/// </summary>
/// <param name="rData">列数据</param>
/// <param name="hmcData">行名称数据</param>
private void FillReportData(DataTable rData, DataSet hmcData)
{
//Range range1 = null;
//Range range2 = null;
ExcelRange range1 = null;
ExcelRange range2 = null;
try
{
// 2.行遍历,填写行名称
int linenum = BaseRow;
//下面的初始化主要为防止出现未初始化错误，并不一定需要设置为linenum
range1 = worksheet.Cells[String.Format("{0}:{1}", linenum, linenum)];
range2 = worksheet.Cells[String.Format("{0}:{1}", linenum, linenum)];
string oldLineType = "";
DataTable rownameDt = hmcData.Tables["ROW_DEFINE"];
try
{
foreach (DataRow dr in rownameDt.Rows)
{
if (!dr.IsNull("HBSFZ"))
{
string fl = dr["HBSFZ"].ToString();
int thishx = int.Parse(dr["HX"].ToString()) + BaseRow;
if (thishx > linenum + 1)
{
linenum = thishx - 1;
}
if (oldLineType == fl)
{
//复制一行
//range1 = (Range)worksheet.Rows[linenum - 1, m_oMissing];
//range1.Insert(XlDirection.xlDown, m_oMissing);//range2 = (Range)worksheet.Rows[linenum - 1, m_oMissing];
//range1.Copy(range2);
//edit by Ly 2015.06.17
try
{
worksheet.InsertRow(linenum - 1, 1);
range1.Address = String.Format("{0}:{1}", linenum - 1, linenum - 1);
range2.Address = String.Format("{0}:{1}", linenum, linenum);
range2.Copy(range1);
}
catch (Exception ex)
{
throw new Exception(String.Format("复制一行时出错！{0}", ex.Message));
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject (range1);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
}
else
{
oldLineType = fl;
}
try
{
PutValueToCell(linenum, BaseCol, dr["HMC"]);
}
catch (Exception ex)
{
throw new Exception(
String.Format("PutValueToCell时出错！{0},hx={1},lx={2},BaseRow={3},BaseCol={4}",
ex.Message, linenum, BaseCol - 1, BaseRow, BaseCol));
}
}
linenum++;
}
}
catch (Exception ex)
{
LogWriter.WriteSystemLog(string.Format("填入行名称时出错！{0}", ex.Message), "ERROR");
}
// 2.行遍历，填写数据
linenum = BaseRow;
DataTable colnameDt = hmcData.Tables["COL_DEFINE"];
int colCount = colnameDt.Rows.Count;
int i = 0;
double lastHx = 0;
foreach (DataRow rowdr in rData.Rows)
{
for (int colnum = 0; colnum < colCount; colnum++)
{
if (Double.Parse(rowdr["HX"].ToString()) > lastHx)
{
i++;
}
lastHx = Double.Parse(rowdr["HX"].ToString());
int thishx = BaseRow + i;
string lx = string.Format("L{0}", colnum + 1);
try
{
PutValueToCell(thishx - 1, colnum + BaseCol + 1, rowdr[lx]);//rowdr[lx]
}
catch (Exception ex)
{
throw new Exception(string.Format("PutValueToCell出错！{0},hx={1},lx={2},BaseRow={3},BaseCol={4}", ex.Message,
thishx - 1, colnum + BaseCol, BaseRow, BaseCol));
}
}
linenum++;
}
}
catch (Exception ex)
{
LogWriter.WriteSystemLog(string.Format("填入数据时出错！{0}", ex.Message), "ERROR");
}
finally
{
if (range1 != null)
{
range1 = null;
}
if (range2 != null)
{
range2 = null;
}
GC.Collect();
// GC.WaitForPendingFinalizers();
// GC.Collect();
// GC.WaitForPendingFinalizers();
}
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public string Export4SSTemplate(System.Data.DataTable dt)
{
string templateFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SSTemplate.xlsx";
string newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx";
//Copy template to file
if (File.Exists(templateFile))
{
File.Copy(templateFile, newFile);
}
else
{
return("01-Template not found");
}
CMixExcel mixExcel = new CMixExcel(newFile, true);
int colIndex = 3;
ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore;
var worksheet = pck.Workbook.Worksheets[1];
ExcelRange columnTemplate = worksheet.Cells["C1:C7"];
ExcelRange pastedColumn = worksheet.Cells["D1"];//1, colIndex, 7, colIndex];
//columnTemplate.Copy(pastedColumn);
for (int i = 3; i < dt.Columns.Count; i++)
{
if (colIndex > 3)
{
pastedColumn = worksheet.Cells[1, colIndex];//1, colIndex, 7, colIndex];
columnTemplate.Copy(pastedColumn);
}
pastedColumn[1, colIndex].Value = dt.Columns[i].ColumnName;
pastedColumn[2, colIndex].Value = dt.Rows[0][i];
pastedColumn[3, colIndex].Value = dt.Rows[1][i];
pastedColumn[4, colIndex].Value = dt.Rows[2][i];
pastedColumn[5, colIndex].Value = dt.Rows[3][i];
pastedColumn[6, colIndex].Value = dt.Rows[4][i];
pastedColumn[7, colIndex].Value = dt.Rows[5][i];
colIndex++;
}
//Xoa cot template
//if (!string.IsNullOrEmpty(mixExcel.PathFile))
newFile = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\NIPPON_4SS" + Guid.NewGuid().ToString() + ".xlsx";
//pck.Save();
pck.SaveAs(new FileInfo(newFile));
mixExcel.CloseStream();
//pck.Stream.Flush();
//pck.Stream.Close();
FileStream fs = new FileStream(newFile, FileMode.OpenOrCreate);
if (fs != null)
{
byte[] binaryData = new byte[fs.Length];
long bytesRead = fs.Read(binaryData, 0, (int)fs.Length);
fs.Close();
string base64Data = Convert.ToBase64String(binaryData);
string result = String.Format("00-{0}", base64Data);
return(result);
}
return("01-Unknow Error");
}
/// <summary>
/// Copy a range of cells from source worksheet to destination worksheet
/// </summary>
/// <param name="srcWs"></param>
/// <param name="destWs"></param>
/// <param name="srcRange"></param>
/// <param name="destRange"></param>
private void CopyFrom(ExcelRange srcRange, ExcelRange destRange)
{
//bug:out of range
srcRange.Copy(destRange);
}