protected static void SetMergeAndStyle(Aspose.Cells.Style style, Cells cls, int startRow, int startCol, int rowNumber, int colNumber)
{
cls.Merge(startRow, startCol, rowNumber, colNumber);
for (int i = 0; i < rowNumber; i++)
for (int j = 0; j < colNumber; j++)
cls[startRow + i, startCol + j].Style = style;
}
/// <summary>Add array of cells to document. (Scintilla feature 2002)</summary>
public unsafe void AddStyledText(int length, Cells c)
{
fixed (char* cPtr = c.Value)
{
IntPtr res = Win32.SendMessage(scintilla, SciMsg.SCI_ADDSTYLEDTEXT, length, (IntPtr) cPtr);
}
}
private void CreateTextBoxes(Matrix matrix, WrapPanel wp, ref TextBox[,] TextBoxDim, ref Cells[,] cellsDim)
{
const double MARGIN = 1d;
const double WIDTH = 40d;
const double HEIGHT = 40d;
int i = 0;
wp.Width = matrix.columns * WIDTH + 2*MARGIN*matrix.columns;
wp.Height = matrix.rows * HEIGHT + 2 * MARGIN * matrix.rows;
foreach (Vector vector in matrix)
{
for (int j = 0; j < vector.length; j++)
{
TextBoxDim[i,j] = new TextBox();
TextBoxDim[i,j].Width = WIDTH;
TextBoxDim[i,j].Height = HEIGHT;
TextBoxDim[i,j].Margin = new Thickness(MARGIN);
TextBoxDim[i,j].Text = vector[j].ToString();
TextBoxDim[i,j].SetValue(Grid.ColumnProperty, j);
TextBoxDim[i,j].SetValue(Grid.RowProperty, i);
wp.Children.Add(TextBoxDim[i, j]);
cellsDim[i, j] = new Cells();
cellsDim[i, j].Value = matrix[i, j];
Binding myBinding = new Binding("Value");
myBinding.Source = cellsDim[i,j];
myBinding.Mode = BindingMode.TwoWay;
TextBoxDim[i, j].SetBinding(TextBox.TextProperty, myBinding);
}
i++;
}
}
/// <summary>
///
/// </summary>
/// <param name="pIncludeType">Type of cells to apply this style, a cell can also derived by this type. If null all type are included.</param>
/// <param name="pExcludeType">Type of cells to exclude, a cell can also derived by this type. If null all the cells are used.</param>
/// <param name="pIncludeEditorValueType">Applied if the cell has the specified Type as a Editor.ValueType.</param>
/// <param name="pView"></param>
public StyleCell(Type pIncludeType, Type pExcludeType, Type pIncludeEditorValueType, Cells.Views.IView pView)
{
m_View = pView;
m_IncludeType = pIncludeType;
m_ExcludeType = pExcludeType;
mIncludeEditorValueType = pIncludeEditorValueType;
}
public void ApplyStyle(Cells.ICellVirtual cell)
{
for (int i = 0; i < m_StyleCells.Count; i++)
{
m_StyleCells[i].ApplyToCell(cell);
}
}
/// <summary>
/// Adds the elements of an array to the end of this CellBaseCollection.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the end of this CellBaseCollection.
/// </param>
public virtual void AddRange(Cells.ICellVirtual[] items)
{
foreach (Cells.ICellVirtual item in items)
{
this.List.Add(item);
}
}
private static void Fill_In_Out_Yield_CT(DateTime mtdDate, DateTime wtdDate, DateTime dtdDate, DateTime endDate, string operationName, int startRow, int startCol, Cells cls, Styles styles)
{
int dtdStartRow = startRow + 1;
int wtdStartRow = startRow + 2;
int mtdStartRow = startRow + 3;
cmdIn_Out_Yield_CT.Parameters["END_DATE"].Value = endDate;
cmdIn_Out_Yield_CT.Parameters["OPERATION_NAME"].Value = operationName;
cmdIn_Out_Yield_CT.Parameters["START_DATE"].Value = dtdDate;
using (OracleDataReader rd = cmdIn_Out_Yield_CT.ExecuteReader())
{
while (rd.Read())
{
CellValueStyle(cls[dtdStartRow, 0], rd["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[dtdStartRow, 1], "DTD", styles["ColumnHeader"]);
CellValueStyle(cls[dtdStartRow, startCol + 0], rd["InQty"], styles["Data"]);
CellValueStyle(cls[dtdStartRow, startCol + 1], rd["OutQty"], styles["Data"]);
cls[dtdStartRow, startCol + 3].R1C1Formula = "=1-" + rd["RejectQty"] + "/R[0]C[-3]";
cls[dtdStartRow, startCol + 3].Style = styles["Percent"];
CellValueStyle(cls[dtdStartRow, startCol + 4], rd["CycleTime"], styles["Data"]);
dtdStartRow += 3;
}
}
cmdIn_Out_Yield_CT.Parameters["START_DATE"].Value = wtdDate;
using (OracleDataReader rd = cmdIn_Out_Yield_CT.ExecuteReader())
{
while (rd.Read())
{
CellValueStyle(cls[wtdStartRow, 0], rd["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[wtdStartRow, 1], "WTD", styles["ColumnHeader"]);
CellValueStyle(cls[wtdStartRow, startCol + 0], rd["InQty"], styles["Data"]);
CellValueStyle(cls[wtdStartRow, startCol + 1], rd["OutQty"], styles["Data"]);
cls[wtdStartRow, startCol + 3].R1C1Formula = "=1-" + rd["RejectQty"] + "/R[0]C[-3]";
cls[wtdStartRow, startCol + 3].Style = styles["Percent"];
CellValueStyle(cls[wtdStartRow, startCol + 4], rd["CycleTime"], styles["Data"]);
wtdStartRow += 3;
}
}
cmdIn_Out_Yield_CT.Parameters["START_DATE"].Value = mtdDate;
using (OracleDataReader rd = cmdIn_Out_Yield_CT.ExecuteReader())
{
while (rd.Read())
{
CellValueStyle(cls[mtdStartRow, 0], rd["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[mtdStartRow, 1], "MTD", styles["ColumnHeader"]);
CellValueStyle(cls[mtdStartRow, startCol + 0], rd["InQty"], styles["Data"]);
CellValueStyle(cls[mtdStartRow, startCol + 1], rd["OutQty"], styles["Data"]);
cls[mtdStartRow, startCol + 3].R1C1Formula = "=1-" + rd["RejectQty"] + "/R[0]C[-3]";
cls[mtdStartRow, startCol + 3].Style = styles["Percent"];
CellValueStyle(cls[mtdStartRow, startCol + 4], rd["CycleTime"], styles["Data"]);
mtdStartRow += 3;
}
}
}
public void ApplyToCell(Cells.ICellVirtual cell)
{
if (IncludeType == null || IncludeType.IsAssignableFrom(cell.GetType()))
{
if (ExcludeType == null || ExcludeType.IsAssignableFrom(cell.GetType()) == false)
{
if (IncludeEditorValueType == null || (cell.Editor != null && cell.Editor.ValueType == IncludeEditorValueType) )
cell.View = View;
}
}
}
public ClassBusyDateSource(Cells Cells, int Row)
{
SchoolYear = ("" + Cells[Row, colSchoolYear].Value).Trim();
Semester = ("" + Cells[Row, colSemester].Value).Trim();
ClassName = ("" + Cells[Row, colClassName].Value).Trim();
Weekday = ("" + Cells[Row, colWeekday].Value).Trim();
Period = ("" + Cells[Row, colPeriod].Value).Trim();
StartTime = ("" + Cells[Row, colStartTime].Value).Trim();
EndTime = ("" + Cells[Row, colEndTime].Value).Trim();
Desc = ("" + Cells[Row, colDesc].Value).Trim();
}
void AttackFriendly()
{
UnitLocation = gameObject.GetComponentInParent<Cells>();
for (int i = range; i > 0; i--)
{
//Debug.Log(gameObject.name + ": Attacking " + i + " tiles ahead");
if (transform.parent.GetComponent<Cells>().isEnd() == false | transform.parent.GetComponent<Owner_Script>().myOwner == UnitOwner.myOwner)
{
if (UnitLocation.ForwardExists(i) == true)
{
Cells TilesInRange = UnitLocation.GetForward(i).GetComponent<Cells>();
if (TilesInRange.isOccupied == true)
{
GameObject enemyObject = TilesInRange.transform.GetChild(0).gameObject;
InRangeOwner = enemyObject.GetComponent<Owner_Script>();
if (InRangeOwner.myOwner != UnitOwner.myOwner)
{
//Debug.Log("hi ian: " + InRangeOwner.myOwner + ", " + UnitOwner.myOwner);
//Debug.Log(gameObject + " - has delt damage to - " + enemyObject);
EnemyHealth = enemyObject.GetComponent<Health_Script>();
EnemyHealth.ApplyDamage(UnitAttack.damage);
}
}
}
}
else
{
InRangeOwner = transform.parent.GetComponent<Owner_Script>();
if (InRangeOwner && InRangeOwner.myOwner != UnitOwner.myOwner)
{
InRangeOwner = transform.parent.GetComponent<Owner_Script>();
if (InRangeOwner.myOwner != UnitOwner.myOwner)
{
//Debug.Log(gameObject.name + " has reached the end!");
EnemyHealth = InRangeOwner.myOwner.GetComponent<Health_Script>();
EnemyHealth.ApplyDamage(1);
UnitHealth.ApplyDamage(9001);
i = 0;
}
}
}
}
}
public CalendarSource(Cells Cells,int Row)
{
SchoolYear = ("" + Cells[Row, colSchoolYear].Value).Trim();
Semester = ("" + Cells[Row, colSemester].Value).Trim();
ClassYear = ("" + Cells[Row, colClassYear].Value).Trim();
Subject = ("" + Cells[Row, colSubject].Value).Trim();
SubjectLevel = ("" + Cells[Row, colSubjectLevel].Value).Trim();
SubjectAlias = ("" + Cells[Row, colSubjectAlias].Value).Trim();
TeacherName = ("" + Cells[Row, colTeacherName1].Value).Trim();
TeacherCode = ("" + Cells[Row, colTeacherCode1].Value).Trim();
ClassName = ("" + Cells[Row, colClass].Value).Trim();
ClassroomName = ("" + Cells[Row, colClassroom].Value).Trim();
Weekday = ("" + Cells[Row, colWeekday].Value).Trim();
Period = ("" + Cells[Row, colPeriod].Value).Trim();
StartTime = ("" + Cells[Row, colStartTime].Value).Trim();
EndTime = ("" + Cells[Row, colEndTime].Value).Trim();
Lock = ("" + Cells[Row, colLock].Value).Trim();
Comment = ("" + Cells[Row, colComment].Value).Trim();
}
public override void PaintBitmap(Bitmap bmp, Cells cells, Point ptOffset, List<Color> colors)
{
BitmapData data = bmp.LockBits(new Rectangle(0, 0, bmp.Width, bmp.Height), ImageLockMode.ReadWrite, PixelFormat.Format24bppRgb);
unsafe
{
byte* ptr = (byte*)data.Scan0;
for (int row = 0; row < cells.Rows; row++)
for (int col = 0; col < cells.Columns; col++)
{
var pt = cells.GetXyCoordinates(col, row);
if (pt.X + 2 < cells.Columns * 2)
PaintCell(ptr, ptOffset.X + pt.X, ptOffset.Y + pt.Y, data.Stride, colors[cells[col, row]]);
else
{
PaintHalfCell(ptr, ptOffset.X + pt.X, ptOffset.Y + pt.Y, data.Stride, colors[cells[col, row]]);
PaintHalfCell(ptr, ptOffset.X, ptOffset.Y + pt.Y, data.Stride, colors[cells[col, row]]);
}
}
}
bmp.UnlockBits(data);
}
public ExcelHelper(string fileName)
{
_FileName = fileName;
_Report = new Workbook();
_Report.Open(new MemoryStream(Properties.Resources.中投區積分比序_Template));
_OutSheet = _Report.Worksheets[Global.Title];
// 先儲存Style
GetExcelStyle();
_OutCells = _OutSheet.Cells;
_DetailCells = null;
if (Global.IsDebug == true)
{
_DetailSheet = _Report.Worksheets[_Report.Worksheets.Add()];
_DetailSheet.Name = "Detail Sheet";
_DetailCells = _DetailSheet.Cells;
_DetailRowIndex = 0;
}
}
public void BuildMdmCodeSet(string excelPath, string sheetName, string dbConnName)
{
Workbook workbook = null;
IList <code_set_excel_entity> codesystemCels = new List <code_set_excel_entity>();
IList <code_set_excel_entity> codesetexcels1 = new List <code_set_excel_entity>();
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
//新版MDMExcel 的说明
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "代码系统";
}
Worksheet sheet = workbook.Worksheets[sheetName];
var cells = sheet.Cells;
#region 加载代码系统，包含代码集的代码系统
for (int i = 1; i <= cells.MaxDataRow; i++)
{
var row = cells.CheckRow(i);
if (row == null || row.FirstCell == null)
{
continue;
}
else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")) == null)
{
continue;
}
else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue.Contains("代码"))
{
continue;
}
else
{
var hasCodeSet = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P")).StringValue;
if (string.IsNullOrWhiteSpace(hasCodeSet))
{
continue;
}
var aaa = new code_set_excel_entity();
aaa.code_sys_code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("G")).StringValue;
aaa.code_sys_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("H")).StringValue;
var ser = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P"));
aaa.memo = sheet.Hyperlinks.FirstOrDefault(p => (p.Area.StartRow == ser.Row && p.Area.StartColumn == ser.Column)).Address;
if (!string.IsNullOrWhiteSpace(aaa.memo))
{
aaa.show_name = aaa.memo.Split('!')[0];//存放对应的sheet的名称
}
codesystemCels.Add(aaa);
}
}
#endregion
#region 循环处理所有的代码系统，并提取代码集
foreach (var item in codesystemCels) //所有的代码系统
{
string sheetNameCodeSet = item.code_sys_code;
Cells codeSetCells = workbook.Worksheets[item.show_name]?.Cells;
if (codeSetCells != null)
{ //读取worksheet codeset
for (int k = 3; k <= codeSetCells.MaxDataRow; k++) //单个codeset 从第二行开始
{
var row = codeSetCells.CheckRow(k);
if (row == null || row.FirstCell == null)
{
continue;
}
else if (row.GetCellOrNull(0).StringValue.StartsWith("代码", StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
var aaa = new code_set_excel_entity();
aaa.code_sys_code = item.code_sys_code;
aaa.code_sys_name = item.code_sys_name;
aaa.code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue; //第一列
aaa.name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("B")).StringValue; //第二列
aaa.show_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("C")).StringValue; //第三列
codesetexcels1.Add(aaa);
}
}
}
}//循环所有的代码系统
#endregion
#region 插入数据
var connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ConnectionString;
using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
{
foreach (var item in codesystemCels)
{
var code_sys_id_list = conn.Query <int>($"select code_sys_id from mdm.code_system where code_sys_code = '{item.code_sys_code}' ").ToList();
int code_sys_id = -1;
if (code_sys_id_list == null || code_sys_id_list.Count == 0)
{
Serilog.Log.Warning($"{item.code_sys_code}{'\t'}{item.code_sys_name}");
}
else
{
code_sys_id = code_sys_id_list.FirstOrDefault();
}
var codesets = codesetexcels1.Where(p => p.code_sys_code == item.code_sys_code);
int sortno = 0;
foreach (var codeset in codesets)
{
var codeistm = new code_set_entity
{
//code_id = "879 ",
code = codeset.code,
name = codeset.name,
show_name = codeset.show_name,
spell_code = "",
wb_code = "",
code_sys_id = code_sys_id,
is_std = true,
std_class_id = 0,
unit_id = 0,
unit_name = null,
value_type = 0,
range = null,
range_low = null,
range_high = null,
note = item.show_name,
quote_code_id = 0,
state = 1,
sort_no = ++sortno,
oper_id = 3,
oper_time = DateTime.Now,
etl_time = DateTime.Now,
tenant_id = 0,
is_valid = true
};
conn.Insert <code_set_entity>(codeistm);
}
}
#region 拼音码、五笔码
//生成拼音、五笔码
conn.Execute("update mdm.code_set set spell_code = substring(get_pym_pg(name) from 0 for 20) ,wb_code = substring(get_wbm(name) from 0 for 20) ", commandTimeout: 300);
conn.Execute("update mdm.code_domain set spell_code = substring(get_pym_pg(domain_name) from 0 for 20) ,wb_code = substring(get_wbm(domain_name) from 0 for 20)", commandTimeout: 300);
conn.Execute("update mdm.code_system set spell_code = substring(get_pym_pg(code_sys_name) from 0 for 20),wb_code = substring(get_wbm(code_sys_name) from 0 for 20) ", commandTimeout: 300);
#endregion
}
#endregion
}
public List <BaseCellObject> AllCells()
{
return(Cells.SelectMany(x => x).ToList());
}
public bool IsComplete()
{
return(Cells.All((cell) => cell.Value > 0));
}
private int CalculateCurrentCumulativeWidth()
{
return(Cells.Sum(c => c.Width));
}
/// <summary>
/// Inserts an element into the CellBaseCollection at the specified index
/// </summary>
/// <param name="index">
/// The index at which the Cells.ICellVirtual is to be inserted.
/// </param>
/// <param name="value">
/// The Cells.ICellVirtual to insert.
/// </param>
public virtual void Insert(int index, Cells.ICellVirtual value)
{
this.List.Insert(index, value);
}
/// <summary>
/// 导出数据到Excel中
/// </summary>
/// <param name="filePath">文件路径名称。</param>
/// <param name="dt">数据表对象。</param>
public static void ExportToExcel(string filePath, DataTable dt)
{
if (string.IsNullOrEmpty(filePath))
{
return;
}
if (dt == null)
{
return;
}
//创建Excel文档。
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add(dt.TableName);
sheet.SheetType = WorksheetTypes.Worksheet;
Cells cells = sheet.Cells;
#region 设置Excel数据列标题的格式
XF xfDataHead = xls.NewXF();
xfDataHead.HorizontalAlignment = HorizontalAlignments.Left;
xfDataHead.Font.FontName = "Tahoma";
xfDataHead.Font.Bold = true;
xfDataHead.UseBorder = true;
xfDataHead.BottomLineStyle = 1;
xfDataHead.TopLineStyle = 1;
xfDataHead.LeftLineStyle = 1;
xfDataHead.RightLineStyle = 1;
xfDataHead.CellLocked = false;
xfDataHead.UseProtection = false;
xfDataHead.UseNumber = true;
#endregion
//循环放入列名
for (int i = 1; i <= dt.Columns.Count; i++)
{
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = (ushort)(i - 1);
colInfo.ColumnIndexEnd = (ushort)i;
colInfo.Width = 15 * 256;
sheet.AddColumnInfo(colInfo);
cells.Add(1, i, dt.Columns[i - 1].Caption, xfDataHead);
}
#region 设置各数据列的格式
XF xfData = xls.NewXF();
xfData.UseBorder = true;
xfData.CellLocked = false;
xfData.BottomLineStyle = 1;
xfData.TopLineStyle = 1;
xfData.LeftLineStyle = 1;
xfData.RightLineStyle = 1;
xfData.UseProtection = false;
xfData.UseMisc = true;
xfData.UseNumber = true;
xfData.HorizontalAlignment = HorizontalAlignments.Left;
#endregion
//循环放入所有数据
int iRow = 2;
foreach (DataRow dr in dt.Rows)
{
for (int iCol = 1; iCol <= dt.Columns.Count; iCol++)
{
DataColumn dc = dt.Columns[iCol - 1];
if (dr[iCol - 1] != DBNull.Value)
{
if (dc.DataType == typeof(DateTime))
{
DateTime dtValue = Convert.ToDateTime(dr[iCol - 1]);
//向单元格中插入数据
cells.Add(iRow, iCol, dtValue.ToString("yyyy-MM-dd HH:mm:ss"), xfData);
}
else
{
//向单元格中插入数据
cells.Add(iRow, iCol, dr[iCol - 1], xfData);
}
}
else
{
cells.Add(iRow, iCol, string.Empty, xfData);
}
}
iRow++;
}
xls.FileName = System.IO.Path.GetFileName(filePath);
string fileWay = System.IO.Path.GetDirectoryName(filePath);
xls.Save(fileWay, true);
}
protected PermissionCheckGridRow()
{
Cells.Add(iconCell);
Cells.Add(descriptionCell);
}
/// <summary>
///
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="p_cell">except this cell</param>
private void EnsureDestinationSpannedAreaIsEmptyExceptOriginalCell(int row, int col, Cells.ICell p_cell)
{
if (AllowOverlappingCells)
return;
EnsureNoSpannedCellsExist(row, col, p_cell);
EnsureNoOtherCellsExist(row, col, p_cell);
/*
for (int y = row; y < row + p_cell.RowSpan; y++)
{
for (int x = col; x < col + p_cell.ColumnSpan; x++)
{
if (x == col && y == row)
continue;
Cells.ICell existingSpannedCell = this[y, x];
if (existingSpannedCell != null)
if (existingSpannedCell != p_cell)
throw new OverlappingCellException(string.Format(
"Given cell at position ({0}, {1}), " +
"intersects with another cell " +
"at position ({2}, {3}) '{4}'",
row, col,
existingSpannedCell.Row.Index,
existingSpannedCell.Column.Index,
existingSpannedCell.DisplayText));
}
}*/
}
public int CandidateCountBySquare(int square, List <int> locations)
{
return(Cells.Count(c => c.Square == square && locations.Contains(c.SquareIndex)));
}
public List <GridCell> SquareCells(int square)
{
return(Cells.Where(c => c.Square == square).ToList());
}
public GridCell CellBySquareIndex(int square, int squareIndex)
{
var cell = Cells.Where(c => c.Square == square && c.SquareIndex == squareIndex).FirstOrDefault();
return(cell);
}
public CapabilityRow(Capability capability)
{
Capability = capability;
Cells.AddRange(cellCheck, cellItem, cellImage);
Update();
}
/// <summary>
/// 批量导入excel数据
/// </summary>
public ActionResult Upexcel(FormCollection from)
{
HttpPostedFileBase file = Request.Files["upload"];
string result = string.Empty;
if (file == null || file.ContentLength <= 0)
{
}
else
{
try
{
Workbook workbook = new Workbook(file.InputStream);
Cells cells = workbook.Worksheets[0].Cells;
DataTable tab = cells.ExportDataTable(0, 0, cells.Rows.Count, cells.MaxDisplayRange.ColumnCount);
int rowsnum = tab.Rows.Count;
if (rowsnum == 0)
{
result = "Excel表为空!请重新导入！"; //当Excel表为空时，对用户进行提示
}
//数据表一共多少行！
DataRow[] dr = tab.Select();
//按行进行数据存储操作！
for (int i = 1; i < dr.Length; i++)
{
T_WageCost WageCostRecord = new T_WageCost();
WageCostRecord.W_GUID = Guid.NewGuid().ToString();
WageCostRecord.C_GUID = Session["CurrentCompany"].ToString();
WageCostRecord.Date = Convert.ToDateTime(dr[i][1].ToString());
WageCostRecord.Employee = dr[i][0].ToString();;
WageCostRecord.Cash = Convert.ToDecimal(dr[i][2].ToString());
WageCostRecord.PersonalTaxes = Convert.ToDecimal(dr[i][3].ToString());
WageCostRecord.SocialSecurity = Convert.ToDecimal(dr[i][4].ToString());
WageCostRecord.Total = Convert.ToDecimal(dr[i][2].ToString()) + Convert.ToDecimal(dr[i][3].ToString()) + Convert.ToDecimal(dr[i][4].ToString());;
new IESvc().UpdWageCost(WageCostRecord);
//RPer,B_Guid,BA_Guid数据需要比对！
string rper = "b73f1802-4ba4-4873-b423-86ea3d9b723f";
T_IERecord record = new T_IERecord();
record.IE_GUID = Guid.NewGuid().ToString();
record.RPer = rper;
DateTime dt;
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
dt = Convert.ToDateTime(dr[i][1].ToString(), dtFormat);
record.AffirmDate = dt;
record.Date = Convert.ToDateTime(dr[i][1].ToString());
record.State = "应付";
record.SumAmount = Convert.ToDecimal(dr[i][2].ToString()) + Convert.ToDecimal(dr[i][3].ToString()) + Convert.ToDecimal(dr[i][4].ToString());
record.C_GUID = Session["CurrentCompany"].ToString();
record.Creator = base.userData.LoginFullName;
record.CreateDate = DateTime.Now;
record.Currency = dr[i][6].ToString();
record.InvType = dr[i][7].ToString();
record.IEGroup = "1544d862-b1ab-42b8-9e97-9c2e1704665c";
record.Remark = dr[i][0].ToString();
record.TaxationAmount = 0;
record.TaxationType = "";
bool TorF = new IESvc().UpdExpenseRecord(record);
if (TorF)
{
result = "导入成功！";
}
else
{
result = "导入失败！";
}
}
}
catch (Exception)
{
result = "导入失败，请检查EXCEL格式是否错误！";
}
}
JsonResult json = new JsonResult();
json.Data = result;
return(json);
}
/// <summary>
/// Set the specified cell int he specified position. This method calls SetCell(int p_iRow, int p_iCol, Cells.ICellVirtual p_Cell)
/// </summary>
/// <param name="p_Position"></param>
/// <param name="p_Cell"></param>
public void SetCell(Position p_Position, Cells.ICellVirtual p_Cell)
{
SetCell(p_Position.Row, p_Position.Column, p_Cell);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a new Workbook.
Workbook excelbook = new Workbook();
//Get the first worksheet.
Worksheet worksheet = excelbook.Worksheets[0];
//Get the worksheet cells.
Cells cells = worksheet.Cells;
//Input a string value into A1 cell.
cells["A1"].PutValue("Select Value:");
//Set the font color of the cell.
cells["A1"].GetStyle().Font.Color = Color.Red;
//Set the font text bold.
cells["A1"].GetStyle().Font.IsBold = true;
//Input value into A2 cell.
cells["A2"].PutValue(0);
//Set the shading color to black with solid background.
cells["A2"].GetStyle().ForegroundColor = Color.Black;
cells["A2"].GetStyle().Pattern = BackgroundType.Solid;
//Set the font color of the cell.
cells["A2"].GetStyle().Font.Color = Color.White;
//set the font text bold.
cells["A2"].GetStyle().Font.IsBold = true;
//Add a spinner control.
Aspose.Cells.Drawing.Spinner spinner = excelbook.Worksheets[0].Shapes.AddSpinner(1, 0, 1, 0, 20, 18);
//Set the placement type of the spinner.
spinner.Placement = PlacementType.FreeFloating;
//Set the linked cell for the control.
spinner.LinkedCell = "A2";
//Set the maximum value.
spinner.Max = 10;
//Set the minimum value.
spinner.Min = 0;
//Set the incr. change for the control.
spinner.IncrementalChange = 2;
//Set it 3-D shading.
spinner.Shadow = true;
//Save the excel file.
excelbook.Save(dataDir + "book1.xls");
}
private void EnsureNoSpannedCellsExist(int row, int col, Cells.ICell p_cell)
{
var spanRange = new Range(row, col, row + p_cell.RowSpan - 1, col + p_cell.ColumnSpan - 1);
var ranges = spannedCellReferences.SpannedRangesCollection.GetRanges(
spanRange);
if (ranges.Count == 0)
return;
var start = new Position(row, col);
foreach (var range in ranges)
{
if (start.Equals(range.Start) == true)
continue;
Cells.ICell existingSpannedCell = this[range.Start];
if (existingSpannedCell == null)
throw new ArgumentException("internal error. please report this bug to developers");
throw new OverlappingCellException(string.Format(
"Given cell at position ({0}, {1}), " +
"intersects with another cell " +
"at position ({2}, {3}) '{4}'",
row, col,
existingSpannedCell.Row.Index,
existingSpannedCell.Column.Index,
existingSpannedCell.DisplayText));
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiate a new workbook
Workbook workbook = new Workbook();
// Get the cells in the first(default) worksheet
Cells cells = workbook.Worksheets[0].Cells;
// Get the A1 cell
Aspose.Cells.Cell p = cells["A1"];
// Enter a value
p.PutValue("Preface");
// Get the A10 cell
Aspose.Cells.Cell A = cells["A10"];
// Enter a value.
A.PutValue("page1");
// Get the H15 cell
Aspose.Cells.Cell D = cells["H15"];
// Enter a value
D.PutValue("page1(H15)");
// Add a new worksheet to the workbook
workbook.Worksheets.Add();
// Get the cells in the second sheet
cells = workbook.Worksheets[1].Cells;
// Get the B10 cell in the second sheet
Aspose.Cells.Cell B = cells["B10"];
// Enter a value
B.PutValue("page2");
// Add a new worksheet to the workbook
workbook.Worksheets.Add();
// Get the cells in the third sheet
cells = workbook.Worksheets[2].Cells;
// Get the C10 cell in the third sheet
Aspose.Cells.Cell C = cells["C10"];
// Enter a value
C.PutValue("page3");
// Create a main PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry pbeRoot = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
pbeRoot.Text = "Sections";
// Set the destination cell/location
pbeRoot.Destination = p;
// Set its sub entry array list
pbeRoot.SubEntry = new ArrayList();
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe1 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe1.Text = "Section 1";
// Set its destination cell
subPbe1.Destination = A;
// Define/Create a sub Bookmark entry object of "Section A"
Aspose.Cells.Rendering.PdfBookmarkEntry ssubPbe = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
ssubPbe.Text = "Section 1.1";
// Set its destination
ssubPbe.Destination = D;
// Create/Set its sub entry array list object
subPbe1.SubEntry = new ArrayList();
// Add the object to "Section 1"
subPbe1.SubEntry.Add(ssubPbe);
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe1);
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe2 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe2.Text = "Section 2";
// Set its destination
subPbe2.Destination = B;
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe2);
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe3 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe3.Text = "Section 3";
// Set its destination
subPbe3.Destination = C;
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe3);
// Create an instance of PdfSaveOptions
Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
// Set the PDF Bookmark root object
pdfSaveOptions.Bookmark = pbeRoot;
dataDir = dataDir + "PDFBookmarks_test.out.pdf";
// Save the pdf file
workbook.Save(dataDir, pdfSaveOptions);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public IEnumerable <T> SelectIf <T>()
{
return(Cells.Cast <IBaseCell>().Where(cell => cell is T).Cast <T>());
}
protected PreCheckGridRow()
{
Cells.Add(_iconCell);
Cells.Add(_descriptionCell);
Cells.Add(_solutionCell);
}
/// <summary>
/// Initializes a new instance of the CellBaseCollection class, containing elements
/// copied from an array.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the new CellBaseCollection.
/// </param>
public CellCollection(Cells.ICellVirtual[] items)
{
this.AddRange(items);
}
private DataGridViewCell createAndInitCell(CustomDataGridViewColumnDescriptor <T> columnDescriptor)
{
DataGridViewCell cell = getCellByType(columnDescriptor.Type);
if (columnDescriptor.Type == DataGridViewColumnType.CheckBox)
{
DataGridViewCheckBoxCell typedCell = (DataGridViewCheckBoxCell)cell;
typedCell.FalseValue = false;
typedCell.TrueValue = true;
}
if (columnDescriptor.Type == DataGridViewColumnType.Image)
{
DataGridViewImageCell typedCell = (DataGridViewImageCell)cell;
typedCell.ImageLayout = DataGridViewImageCellLayout.Zoom;
typedCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
if (columnDescriptor.Type == DataGridViewColumnType.ImageButton)
{
DataGridViewImageButtonCell typedCell = (DataGridViewImageButtonCell)cell;
typedCell.Image = columnDescriptor.ButtonImage;
typedCell.ImagePadding = columnDescriptor.ButtonImagePadding;
}
if (columnDescriptor.Type == DataGridViewColumnType.SmallIcon)
{
DataGridViewSmallIconCell typedCell = (DataGridViewSmallIconCell)cell;
typedCell.IconShown = columnDescriptor.IconShown;
typedCell.IconColor = columnDescriptor.IconColor;
typedCell.IconType = columnDescriptor.IconType;
typedCell.IconPadding = columnDescriptor.IconPadding;
}
if (columnDescriptor.Type == DataGridViewColumnType.Button)
{
cell.Value = columnDescriptor.ButtonText;
}
if (columnDescriptor.Type == DataGridViewColumnType.DisableButton)
{
cell.Value = columnDescriptor.ButtonText;
}
if (columnDescriptor.Type == DataGridViewColumnType.ComboBox)
{
DataGridViewComboBoxCell typedCell = (DataGridViewComboBoxCell)cell;
typedCell.DisplayStyle = DataGridViewComboBoxDisplayStyle.DropDownButton;
typedCell.ValueMember = "Value";
typedCell.DisplayMember = "Label";
typedCell.Items.AddRange(columnDescriptor.DropDownPopulatorMethod?.Invoke(item, cell));
}
Cells.Add(cell);
if (columnDescriptor.Type == DataGridViewColumnType.TextBox)
{
cell.ReadOnly = !columnDescriptor.TextEditable;
}
if (columnDescriptor.Type == DataGridViewColumnType.SmallIcon)
{
cell.ReadOnly = true;
}
columnDescriptor.InitializerMethod?.Invoke(item, cell);
columnDescriptor.UpdaterMethod?.Invoke(item, cell);
return(cell);
}
public void Rebuild(LevelConstructSet lcSet, GameObjectsSet mSet, GameMode gMode)
{
Debug.Log("rebuild ");
this.lcSet = lcSet;
vertSize = lcSet.VertSize;
horSize = lcSet.HorSize;
float deltaX = lcSet.DistX;
float deltaY = lcSet.DistY;
float scale = lcSet.Scale;
parent.localScale = new Vector3(scale, scale, scale);
this.goSet = mSet;
List <CellData> featCells = lcSet.featuredCells;
Cells.ForEach((c) => { c.DestroyGridObjects(); });
List <GridCell> tempCells = Cells;
Cells = new List <GridCell>(vertSize * horSize + horSize);
Rows = new List <Row <GridCell> >(vertSize);
xStep = (cellSize.x + deltaX);
yStep = (cellSize.y + deltaY);
cOffset = (1 - horSize) * xStep / 2.0f; // offset from center by x-axe
yStart = (vertSize - 1) * yStep / 2.0f;
// create rows
GridCell cell;
Row <GridCell> row;
int cellCounter = 0;
int ri = 0;
Sprite sRE = mSet.gridCellEven.GetComponent <SpriteRenderer>()?.sprite;
Sprite sRO = mSet.gridCellOdd.GetComponent <SpriteRenderer>().sprite;
for (int i = 0; i < vertSize; i++)
{
bool isEvenRow = (i % 2 == 0);
row = new Row <GridCell>(horSize);
for (int j = 0; j < row.Length; j++)
{
bool isEvenColumn = (j % 2 == 0);
Vector3 pos = new Vector3(j * xStep + cOffset, yStart - i * yStep, 0);
if (tempCells != null && cellCounter < tempCells.Count)
{
cell = tempCells[cellCounter];
cell.gameObject.SetActive(true);
cell.transform.localPosition = pos;
cellCounter++;
SpriteRenderer sR = cell.GetComponent <SpriteRenderer>();
if (sR)
{
if (isEvenRow)
{
sR.sprite = (!isEvenColumn) ? sRO : sRE;
}
else
{
sR.sprite = (isEvenColumn) ? sRO : sRE;
}
}
}
else
{
if (isEvenRow)
{
cell = UnityEngine.Object.Instantiate((!isEvenColumn) ? mSet.gridCellOdd : mSet.gridCellEven).GetComponent <GridCell>();
}
else
{
cell = UnityEngine.Object.Instantiate((isEvenColumn) ? mSet.gridCellOdd : mSet.gridCellEven).GetComponent <GridCell>();
}
cell.transform.parent = parent;
cell.transform.localPosition = pos;
cell.transform.localScale = Vector3.one;
}
Cells.Add(cell);
row[j] = cell;
}
Rows.Add(row);
ri++;
}
// destroy not used cells
if (cellCounter < tempCells.Count)
{
for (int i = cellCounter; i < tempCells.Count; i++)
{
UnityEngine.Object.Destroy(tempCells[i].gameObject);
}
}
// cache columns
Columns = new List <Column <GridCell> >(horSize);
Column <GridCell> column;
for (int c = 0; c < horSize; c++)
{
column = new Column <GridCell>(Rows.Count);
for (int r = 0; r < Rows.Count; r++)
{
column[r] = Rows[r][c];
}
Columns.Add(column);
}
for (int r = 0; r < Rows.Count; r++)
{
for (int c = 0; c < horSize; c++)
{
Rows[r][c].Init(r, c, Columns[c], Rows[r], gMode);
}
}
SetObjectsData(lcSet, gMode);
Debug.Log("rebuild cells: " + Cells.Count);
}
private void PopulateCell(int p_GridRow, int p_GridCol, System.Reflection.PropertyInfo p_PropInfo, Cells.Editors.EditorBase p_Editor, object p_Object)
{
grid[p_GridRow, p_GridCol] = new Cells.Cell(p_PropInfo.GetValue(p_Object,null));
grid[p_GridRow, p_GridCol].Editor = p_Editor;
Cells.Controllers.BindProperty l_Bind = new Cells.Controllers.BindProperty(p_PropInfo, p_Object);
grid[p_GridRow, p_GridCol].AddController(l_Bind);
Cells.Controllers.CustomEvents l_CustomEvents = new Cells.Controllers.CustomEvents();
l_CustomEvents.ValueChanged += new EventHandler(Grid_ValueChanged);
grid[p_GridRow, p_GridCol].AddController(l_CustomEvents);
}
/// <summary>
/// 将listview的信息存储到excel中
/// </summary>
/// <param name="view">需要存储的listview名</param>
/// <param name="outname">输出的名字</param>
/// <param name="HaveTime">是否需要在最前面加上时间</param>
/// <param name="action">是否替换excel名字中不允许的字符</param>
/// <returns>成功返回“成功保存”，失败返回文件名</returns>
public static string ListViewSaveExcel(ListView view, string outname, bool HaveTime = false, bool action = false)
{
if (File.Exists(outname))
{
//如果存在则删除
File.Delete(outname);
}
if (HaveTime)
{
string time = DateTime.Now.ToString("yyyyMMdd HHmmss ");
outname = time + outname;
}
ListView listview = view;
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
if (view.InvokeRequired)
{
Action <string> an = x =>
{
for (int t = 0; t < listview.Columns.Count; t++)
{
cells[0, t].PutValue(listview.Columns[t].Text);
}
for (int i = 0; i < listview.Items.Count; i++)
{
for (int j = 0; j < listview.Items[i].SubItems.Count; j++)
{
cells[i + 1, j].PutValue(listview.Items[i].SubItems[j].Text);
}
}
};
view.Invoke(an, "");
}
else
{
for (int t = 0; t < listview.Columns.Count; t++)
{
cells[0, t].PutValue(listview.Columns[t].Text);
}
for (int i = 0; i < listview.Items.Count; i++)
{
for (int j = 0; j < listview.Items[i].SubItems.Count; j++)
{
cells[i + 1, j].PutValue(listview.Items[i].SubItems[j].Text);
}
}
}
//是否替换
if (action)
{
outname = Regex.Replace(outname, "\\|/|:|\\*|\\?|\"|<|>|\\|", "_");
}
//保存
try
{
workbook.Save(outname);
return("成功保存");
}
catch
{
return(outname);
}
}
/// <summary>
/// Draw the image and the displaystring of the specified cell.
/// </summary>
/// <param name="p_Cell">The cell.</param>
/// <param name="p_CellPosition">The cell position.</param>
/// <param name="e">Paint arguments</param>
/// <param name="p_ClientRectangle">Rectangle position where draw the current cell, relative to the current view,</param>
/// <param name="p_Status">Cell status</param>
protected override void DrawCell_ImageAndText(Cells.ICellVirtual p_Cell, Position p_CellPosition, System.Windows.Forms.PaintEventArgs e, System.Drawing.Rectangle p_ClientRectangle, DrawCellStatus p_Status)
{
base.DrawCell_ImageAndText(p_Cell, p_CellPosition, e, p_ClientRectangle, p_Status);
RectangleBorder l_Border = Border;
Color l_ForeColor = ForeColor;
if (p_Status == DrawCellStatus.Focus)
{
l_Border = FocusBorder;
l_ForeColor = FocusForeColor;
}
else if (p_Status == DrawCellStatus.Selected)
{
l_Border = SelectionBorder;
l_ForeColor = SelectionForeColor;
}
for (int i = 0; i < positionedImages.Count; i++)
{
if (positionedImages[i] != null)
{
VisualModelBase.PaintImageAndText(e.Graphics,
p_ClientRectangle,
positionedImages[i].Image,
positionedImages[i].Alignment,
false,
null, // not used
null, // not used
false, // not used
l_Border,
l_ForeColor,
null,//not used
false,
false);
}
}
}
/// <summary>
/// 导出CH的excel通用方法
/// </summary>
/// <param name="myData">导出的数据</param>
private void ExportExcel(List <ExcelData> myData)
{
//列宽：
ushort[] colWidth = new ushort[] { 16, 16, 16, 14, 14, 16, 28, 14, 16,
28, 28, 16, 16, 32, 14, 18, 24, 12, 18,
18, 18, 16, 16, 24, 24, 12, 24, 18,
18, 18, 18, 18 };
//列名：
string[] colName = new string[] { "审核结果", "流水号", "下单日期", "制单人", "产品类别", "客户编码", "客户名称", "营业员", "营业员电话",
"备注", "收货单位", "ATTN", "收货电话", "收货地址", "产品代码", "产品名称", "规格型号", "单位", "订单数量",
"申请数量", "实出数量", "客户P/O", "客户P/N", "行备注", "订单号", "订单行号", "出库单号", "出库日期",
"快递单号", "叉板数", "件数", "周期" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = string.Format("出货申请单_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
Worksheet sheet = xls.Workbook.Worksheets.Add("出货信息列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in myData)
{
colIndex = 1;
//"审核结果","流水号","下单日期","办事处","产品类别","客户编码","客户名称","营业员","营业员电话",
//"备注","收货单位","ATTN","收货电话","收货地址","产品代码","产品名称","规格型号","单位","订单数量",
//"申请数量","客户P/O","客户P/N","行备注","订单号","订单行号","出库单号","出库日期"
cells.Add(++rowIndex, colIndex, d.auditStatus);
cells.Add(rowIndex, ++colIndex, d.h.sys_no);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.h.bill_date).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.h.User.real_name);
cells.Add(rowIndex, ++colIndex, d.h.product_type);
cells.Add(rowIndex, ++colIndex, d.h.customer_no);
cells.Add(rowIndex, ++colIndex, d.h.customer_name);
cells.Add(rowIndex, ++colIndex, d.h.clerk_name);
cells.Add(rowIndex, ++colIndex, d.h.clerk_phone);
cells.Add(rowIndex, ++colIndex, d.h.comment);
cells.Add(rowIndex, ++colIndex, d.h.delivery_unit);
cells.Add(rowIndex, ++colIndex, d.h.delivery_attn);
cells.Add(rowIndex, ++colIndex, d.h.delivery_phone);
cells.Add(rowIndex, ++colIndex, d.h.delivery_addr);
cells.Add(rowIndex, ++colIndex, d.e.item_no);
cells.Add(rowIndex, ++colIndex, d.e.item_name);
cells.Add(rowIndex, ++colIndex, d.e.item_model);
cells.Add(rowIndex, ++colIndex, d.e.unit_name);
cells.Add(rowIndex, ++colIndex, d.e.order_qty);
cells.Add(rowIndex, ++colIndex, d.e.apply_qty);
cells.Add(rowIndex, ++colIndex, d.e.real_qty);
cells.Add(rowIndex, ++colIndex, d.e.customer_po);
cells.Add(rowIndex, ++colIndex, d.e.customer_pn);
cells.Add(rowIndex, ++colIndex, d.e.comment);
cells.Add(rowIndex, ++colIndex, d.e.order_no);
cells.Add(rowIndex, ++colIndex, d.e.order_entry_no);
cells.Add(rowIndex, ++colIndex, d.h.k3_stock_no);
cells.Add(rowIndex, ++colIndex, d.h.k3_audit2_date == null ? "" : ((DateTime)d.h.k3_audit2_date).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.e.delivery_num);
cells.Add(rowIndex, ++colIndex, d.e.cardboard_num);
cells.Add(rowIndex, ++colIndex, d.e.packs);
cells.Add(rowIndex, ++colIndex, d.e.cycle);
}
xls.Send();
}
/// <summary>
/// 解析excel生成实体
/// </summary>
/// <param name="excelPath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
private IList <mdmExcelRawEntity> ParseExcelToEntity(string excelPath, string sheetName)
{
Workbook workbook = null;
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "代码系统";
}
Worksheet worksheet = workbook.Worksheets[sheetName];
Cells cells = worksheet.Cells;
IList <mdmExcelRawEntity> codsyslist = new List <mdmExcelRawEntity>();
#region excel解析
for (int i = 0; i <= cells.MaxDataRow; i++)
{
var row = cells.CheckRow(i);
if (row == null || row.FirstCell == null)
{
continue;
}
Cell cel = row[CellsHelper.ColumnNameToIndex("A")];
if (cel == null || cel.Value == null)
{
continue;
}
if (cel.IsMerged)
{
continue;
}
else
{
if (cel.GetStringValue(CellValueFormatStrategy.None) == "一级域代码")
{
//表头
}
else
{
mdmExcelRawEntity aa = new mdmExcelRawEntity();
aa.一级域代码 = row[CellsHelper.ColumnNameToIndex("A")].GetStringValue(CellValueFormatStrategy.None); // 一级域代码
aa.一级域 = row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None); //一级域
aa.二级域代码 = row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); //二级域代码
aa.二级域 = row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None); //二级域
aa.级域代码 = row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None); //三级域代码
aa.级域 = row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); //三级域
aa.代码系统 = row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None); //代码系统
aa.代码系统名称 = row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None); //代码系统名称
aa.标准来源 = row[CellsHelper.ColumnNameToIndex("I")].GetStringValue(CellValueFormatStrategy.None); //标准来源
aa.标准级别 = row[CellsHelper.ColumnNameToIndex("J")].GetStringValue(CellValueFormatStrategy.None); //标准级别
aa.标准完整性 = row[CellsHelper.ColumnNameToIndex("K")].GetStringValue(CellValueFormatStrategy.None); //标准完整性
aa.值集合 = row[CellsHelper.ColumnNameToIndex("L")].GetStringValue(CellValueFormatStrategy.None); //"值集合"
aa.ETL转换 = row[CellsHelper.ColumnNameToIndex("M")].GetStringValue(CellValueFormatStrategy.None); //"ETL转换"
aa.应用转换 = row[CellsHelper.ColumnNameToIndex("N")].GetStringValue(CellValueFormatStrategy.None); //"应用转换"
aa.示例数据 = row[CellsHelper.ColumnNameToIndex("O")].GetStringValue(CellValueFormatStrategy.None); //示例数据
codsyslist.Add(aa);
}
}
}
#endregion
return(codsyslist);
}
// Use this for initialization
void Start()
{
test = gameObject.GetComponent<Cells>();
}
//出货组报表
public void ExportStockTeamExcel(string sysNo, string stockNo, string orderNo, string customer, string model, DateTime fromDate, DateTime toDate, int userId)
{
var datas = GetStockTeamReport(sysNo, stockNo, orderNo, customer, model, fromDate, toDate, userId);
//列宽：
ushort[] colWidth = new ushort[] { 26, 24, 24, 16, 16, 20, 32, 12, 10, 12, 10,
18, 18, 24, 18, 18, 24, 48, 18, 18 };
//列名：
string[] colName = new string[] { "销售订单号", "K3出库单号", "客户名称", "客户P/O", "客户P/N", "产品名称", "规格型号", "出货数量", "单位", "叉板数", "件数",
"快递单号", "周期", "装箱规格尺寸/MM", "总毛重/KG", "备注", "收货单位", "送货地址", "收件人", "收件人电话" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = string.Format("出货报表_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
Worksheet sheet = xls.Workbook.Worksheets.Add("出货报表信息");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in datas)
{
colIndex = 1;
//"销售订单号","出库单号","客户名称","客户P/O","客户P/N","产品名称","规格型号","出货数量","单位","叉板数","件数",
//"快递单号","周期","装箱规格尺寸/MM","总毛重/KG","收货单位","送货地址"
cells.Add(++rowIndex, colIndex, d.orderNo);
cells.Add(rowIndex, ++colIndex, d.k3StockNo);
cells.Add(rowIndex, ++colIndex, d.customerName);
cells.Add(rowIndex, ++colIndex, d.customerPO);
cells.Add(rowIndex, ++colIndex, d.customerPN);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, d.applyQty);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.cardboardNum);
cells.Add(rowIndex, ++colIndex, d.packs);
cells.Add(rowIndex, ++colIndex, d.deliveryNumber);
cells.Add(rowIndex, ++colIndex, d.cycle);
cells.Add(rowIndex, ++colIndex, d.boxSize);
cells.Add(rowIndex, ++colIndex, d.totalGrossWeight);
cells.Add(rowIndex, ++colIndex, d.itemComment);
cells.Add(rowIndex, ++colIndex, d.deliveryUnit);
cells.Add(rowIndex, ++colIndex, d.deliveryAddr);
cells.Add(rowIndex, ++colIndex, d.contact);
cells.Add(rowIndex, ++colIndex, d.contactPhone);
}
xls.Send();
}
private static void Fill_WIPQty(string operationName, int startRow, int startCol, Cells cls, Styles styles)
{
startRow++;
cmdWIPQty.Parameters["OPERATION_NAME"].Value = operationName;
using (OracleDataReader rdWIPQty = cmdWIPQty.ExecuteReader())
{
while (rdWIPQty.Read())
{
CellValueStyle(cls[startRow + 0, 0], rdWIPQty["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 0, 1], "DTD", styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 0, startCol + 2], rdWIPQty["WipQty"], styles["Data"]);
CellValueStyle(cls[startRow + 1, 0], rdWIPQty["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 1, 1], "WTD", styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 1, startCol + 2], rdWIPQty["WipQty"], styles["Data"]);
CellValueStyle(cls[startRow + 2, 0], rdWIPQty["productname"], styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 2, 1], "MTD", styles["ColumnHeader"]);
CellValueStyle(cls[startRow + 2, startCol + 2], rdWIPQty["WipQty"], styles["Data"]);
startRow += 3;
}
}
}
public void SaveExcel()
{
DateTime now = DateTime.Today;
string pathTemp = HttpContext.Current.Server.MapPath("~/files/templete/StockTransfer.xls").ToLower();
string file = string.Format(@"StockTransfer for {0:dd} {0:MMMM} {0:yyyy}.xls", now).ToLower();
string path = MapPath("~/files/excel/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string pathUrl = string.Format("~/files/excel/{0}", file);
string pathOpen = HttpContext.Current.Server.MapPath(pathUrl).ToLower();
using (FileStream input = File.OpenRead(pathTemp), output = File.OpenWrite(pathOpen))
{
int read = -1;
byte[] buffer = new byte[4096];
while (read != 0)
{
read = input.Read(buffer, 0, buffer.Length);
output.Write(buffer, 0, read);
}
}
License lic = new License();
lic.SetLicense(HttpContext.Current.Server.MapPath(@"~\bin\License.lic"));
Workbook workbook = new Workbook();
workbook.Open(pathOpen);
Worksheet sheet0 = workbook.Worksheets[0];
Cells c1 = sheet0.Cells;
int r = 1;
c1[r, 2].PutValue(txt_LotNo.Text);
c1[r, 4].PutValue(txt_HblNo.Text);
c1[r, 7].PutValue(txt_ContNo.Text);
c1[r, 10].PutValue(txt_SKULine_Product.Text);
r = 3;
c1[r, 2].PutValue(SafeValue.SafeString(cmb_WareHouse.Value));
c1[r, 4].PutValue(txt_Location.Text);
c1[r, 7].PutValue(txt_CustName.Text);
r = 5;
c1[r, 2].PutValue(txt_from.Date.ToString("yyyy-MM-dd"));
c1[r, 4].PutValue(txt_end.Date.ToString("yyyy-MM-dd"));
//set style
Aspose.Cells.Style style0 = workbook.Styles[workbook.Styles.Add()];
//style0.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#B09067");
//style0.ForegroundColor = Color.FromName("#B09067");
style0.ForegroundColor = Color.Yellow;
style0.Pattern = BackgroundType.Solid;
//c1[t, 2].SetStyle(style0);
Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.Font.IsBold = false;
style1.Font.Size = 12;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//Select Data
int cnt = 0;
r = 10;
#region Data
DataTable dt = GetData(sql);
int Colnum = dt.Columns.Count; //Cell Colnum number
int Rownum = dt.Rows.Count; //Cell Row number
DataRow dr = null;
DateTime today = DateTime.Now;
int n = 0;
decimal handQty = 0;
decimal skuQty = 0;
string jobNo = "";
string lastContNo = "";
string lastBookingNo = "";
string lastSkuCode = "";
string lastHblNo = "";
decimal inQty = 0;
decimal outQty = 0;
decimal skuIn = 0;
decimal skuOut = 0;
decimal inWeight = 0;
decimal outWeight = 0;
decimal inVolume = 0;
decimal outVolume = 0;
decimal handWeight = 0;
decimal handVolume = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
int no = SafeValue.SafeInt(dt.Rows[i]["No"], 0);
string type = SafeValue.SafeString(dt.Rows[i]["CargoType"]);
string bookingNo = SafeValue.SafeString(dt.Rows[i]["BookingNo"]);
string transferNo = SafeValue.SafeString(dt.Rows[i]["TransferNo"]);
string refNo = SafeValue.SafeString(dt.Rows[i]["RefNo"]);
string hblNo = SafeValue.SafeString(dt.Rows[i]["HblNo"]);
string skuCode = SafeValue.SafeString(dt.Rows[i]["SkuCode"]);
if (type == "IN")
{
inQty = SafeValue.SafeDecimal(dt.Rows[i]["QtyOrig"]);
skuIn = SafeValue.SafeDecimal(dt.Rows[i]["PackQty"]);
inWeight = SafeValue.SafeDecimal(dt.Rows[i]["WeightOrig"]);
inVolume = SafeValue.SafeDecimal(dt.Rows[i]["VolumeOrig"]);
}
else
{
outQty = SafeValue.SafeDecimal(dt.Rows[i]["QtyOrig"]);
skuOut = SafeValue.SafeDecimal(dt.Rows[i]["PackQty"]);
outWeight = SafeValue.SafeDecimal(dt.Rows[i]["WeightOrig"]);
outVolume = SafeValue.SafeDecimal(dt.Rows[i]["VolumeOrig"]);
}
string contNo = SafeValue.SafeString(dt.Rows[i]["ContNo"]);
if (refNo == jobNo && bookingNo == lastBookingNo && skuCode == lastSkuCode && hblNo == lastHblNo)
{
if (n == 0)
{
handQty = inQty - outQty;
skuQty = skuIn - skuOut;
handWeight = inWeight - outWeight;
handVolume = inVolume - outVolume;
}
else
{
handQty = handQty - outQty;
skuQty = skuQty - skuOut;
handWeight = handWeight - outWeight;
handVolume = handVolume - outVolume;
}
c1[r, 0].PutValue(R.Text(i + 1));
c1[r, 1].PutValue(R.Text(dt.Rows[i]["JobNo"]));
c1[r, 2].PutValue(R.Text(Helper.Safe.SafeDateStr(dt.Rows[i]["JobDate"])));
c1[r, 3].PutValue(R.Text(dt.Rows[i]["TransferNo"]));
c1[r, 4].PutValue(R.Text(dt.Rows[i]["RefNo"]));
c1[r, 5].PutValue(R.Text(dt.Rows[i]["ClientId"]));
c1[r, 6].PutValue(R.Text(dt.Rows[i]["WareHouseCode"]));
c1[r, 7].PutValue(R.Text(dt.Rows[i]["Location"]));
c1[r, 8].PutValue(R.Text(dt.Rows[i]["BookingNo"]));
c1[r, 9].PutValue(R.Text(dt.Rows[i]["HblNo"]));
c1[r, 10].PutValue(R.Text(dt.Rows[i]["ContNo"]));
c1[r, 11].PutValue(R.Text(dt.Rows[i]["OpsType"]));
c1[r, 12].PutValue(R.Text(dt.Rows[i]["SkuCode"]));
c1[r, 13].PutValue(R.Text(dt.Rows[i]["QtyOrig"]));
c1[r, 14].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 15].PutValue(R.Text(dt.Rows[i]["WeightOrig"]));
c1[r, 16].PutValue(R.Text(dt.Rows[i]["VolumeOrig"]));
c1[r, 17].PutValue(R.Text(dt.Rows[i]["PackQty"]));
c1[r, 18].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 19].PutValue(R.Text(handQty));
c1[r, 20].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 21].PutValue(R.Text(handWeight));
c1[r, 22].PutValue(R.Text(handVolume));
c1[r, 23].PutValue(R.Text(skuQty));
c1[r, 24].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 25].PutValue(R.Text(dt.Rows[i]["Marking1"]));
c1[r, 26].PutValue(R.Text(dt.Rows[i]["Marking2"]));
c1[r, 27].PutValue(R.Text(dt.Rows[i]["Remark1"]));
n++;
}
else
{
n = 0;
jobNo = SafeValue.SafeString(dt.Rows[i]["JobNo"]);
c1[r, 0].PutValue(R.Text(i + 1));
c1[r, 1].PutValue(R.Text(dt.Rows[i]["JobNo"]));
c1[r, 2].PutValue(R.Text(Helper.Safe.SafeDateStr(dt.Rows[i]["JobDate"])));
c1[r, 3].PutValue(R.Text(dt.Rows[i]["TransferNo"]));
c1[r, 4].PutValue(R.Text(dt.Rows[i]["RefNo"]));
c1[r, 5].PutValue(R.Text(dt.Rows[i]["ClientId"]));
c1[r, 6].PutValue(R.Text(dt.Rows[i]["WareHouseCode"]));
c1[r, 7].PutValue(R.Text(dt.Rows[i]["Location"]));
c1[r, 8].PutValue(R.Text(dt.Rows[i]["BookingNo"]));
c1[r, 9].PutValue(R.Text(dt.Rows[i]["HblNo"]));
c1[r, 10].PutValue(R.Text(dt.Rows[i]["ContNo"]));
c1[r, 11].PutValue(R.Text(dt.Rows[i]["OpsType"]));
c1[r, 12].PutValue(R.Text(dt.Rows[i]["SkuCode"]));
c1[r, 13].PutValue(R.Text(dt.Rows[i]["QtyOrig"]));
c1[r, 14].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 15].PutValue(R.Text(dt.Rows[i]["WeightOrig"]));
c1[r, 16].PutValue(R.Text(dt.Rows[i]["VolumeOrig"]));
c1[r, 17].PutValue(R.Text(dt.Rows[i]["PackQty"]));
c1[r, 18].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 19].PutValue(R.Text(inQty));
c1[r, 20].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 21].PutValue(R.Text(dt.Rows[i]["WeightOrig"]));
c1[r, 22].PutValue(R.Text(dt.Rows[i]["VolumeOrig"]));
c1[r, 23].PutValue(R.Text(skuIn));
c1[r, 24].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 25].PutValue(R.Text(dt.Rows[i]["Marking1"]));
c1[r, 26].PutValue(R.Text(dt.Rows[i]["Marking2"]));
c1[r, 27].PutValue(R.Text(dt.Rows[i]["Remark1"]));
}
lastContNo = contNo;
lastBookingNo = bookingNo;
lastHblNo = hblNo;
lastSkuCode = skuCode;
dr = dt.Rows[i];
for (int j = 0; j <= 25; j++)
{
c1[r, j].SetStyle(style1);
}
cnt = i + 1;
c1.SetRowHeight(r, 20);
r++;
}
#endregion
workbook.Save(pathOpen, FileFormatType.Excel2003);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, FileFormatType.Excel2003);
byte[] bt = ms.GetBuffer();
try
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Length", bt.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=" + file);
Response.BinaryWrite(bt);
}
catch (Exception exc)
{
throw new Exception(exc.Message + "/" + exc.StackTrace);
}
ms.Dispose();
}
/// <summary>
/// Set the specified cell int he specified position. Abstract method of the GridVirtual control
/// </summary>
/// <param name="p_iRow"></param>
/// <param name="p_iCol"></param>
/// <param name="p_Cell"></param>
public virtual void SetCell(int p_iRow, int p_iCol, Cells.ICellVirtual p_Cell)
{
if (p_Cell is Cells.ICell)
InsertCell(p_iRow, p_iCol, (Cells.ICell)p_Cell);
else if (p_Cell == null)
InsertCell(p_iRow, p_iCol, null);
else
throw new SourceGridException("Expected ICell class");
}
/// <summary>
/// 动态生产简化模板，用于动态导出列表信息
/// </summary>
/// <param name="columns">模板中的列信息描述</param>
/// <returns></returns>
public byte[] CreateTemplate(IList <ColumnInfo> columns, string excelKey, string title)
{
var workbook = new Workbook();
var sheet = (Worksheet)workbook.Worksheets[0];
Cells cells = sheet.Cells;
sheet.FreezePanes(2, 1, 2, 0);//冻结第一行
//sheet.Name = jsonObject.sheetName;//接受前台的Excel工作表名
//为标题设置样式
var styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTitle.Font.Name = "宋体"; //文字字体
styleTitle.Font.Size = 18; //文字大小
styleTitle.Font.IsBold = true; //粗体
//题头样式
var styleHeader = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleHeader.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleHeader.Font.Name = "宋体"; //文字字体
styleHeader.Font.Size = 14; //文字大小
styleHeader.Font.IsBold = true; //粗体
styleHeader.IsTextWrapped = true; //单元格内容自动换行
styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//内容样式
var styleContent = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleContent.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleContent.Font.Name = "宋体"; //文字字体
styleContent.Font.Size = 12; //文字大小
styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//表格列数
var columnCount = columns.Count;
//生成行1 标题行
cells.Merge(0, 0, 1, columnCount); //合并单元格
cells[0, 0].PutValue(title); //填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 25);
//生成题头列行
for (int i = 0; i < columnCount; i++)
{
cells[1, i].PutValue(columns[i].ChineseName);
cells[1, i].SetStyle(styleHeader);
}
cells.SetRowHeight(1, 23);
//生成内容行，第三行起始。变量绑定表达式 &=[Data Source].[Field Name] 或者 &=DataSource.FieldName
for (int k = 0; k < columnCount; k++)
{
// 变量绑定表达式 &=[Data Source].[Field Name] 或者 &=DataSource.FieldName
var exp = string.Format("&=[{0}].[{1}]", columns[k].TableName, columns[k].FieldName);
cells[2, k].PutValue(exp);
// 设置时间单元格格式
if (!string.IsNullOrWhiteSpace(columns[k].DateFormat))
{
styleContent.Number = 14;
cells[2, k].SetStyle(styleContent);
styleContent.Number = 0;
}
else
{
cells[2, k].SetStyle(styleContent);
}
}
cells.SetRowHeight(2, 22);
//添加制表日期
//cells[2 + rowCount, columnCount - 1].PutValue("制表日期:" + DateTime.Now.ToShortDateString());
sheet.AutoFitColumns();//让各列自适应宽度
//sheet.AutoFitRows();//让各行自适应宽度
// 设置唯一Key，用于校验模板是否被修改过
//workbook.BuiltInDocumentProperties.Comments = string.Join(",", columns.Select(c => c.FieldName).ToArray());
//workbook.BuiltInDocumentProperties.Version = int.Parse(DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString());
// 是否生产临时文件供跟踪测试
// workbook.Save(@"D:/Tmpl.xls");
// 将报表内容保存为文件流（字节数组）
var excelStream = new MemoryStream();
workbook.Save(excelStream, SaveFormat.Excel97To2003);
var buffer = excelStream.ToArray();
excelStream.Close();
// 写入模板的元数据
//new DefaultExcelMetadataStorage().AddMetadata(excelKey, buffer, string.Join(",", columns.Select(c => c.FieldName).ToArray()));
return(buffer);
}
private void DirectSetCell(Position position, Cells.ICell cell)
{
if (OptimizeMode == CellOptimizeMode.ForRows)
{
GridRow row = Rows[position.Row];
if (position.Column >= Columns.Count)
throw new ArgumentException(string.Format(
"Grid has only {0} columns, you tried to insert cell into position {1}." +
"You should probably call Redim on grid to increase it's column size",
Columns.Count, position.ToString()));
row[Columns[position.Column] as GridColumn] = cell;
}
else if (OptimizeMode == CellOptimizeMode.ForColumns)
{
GridColumn col = Columns[position.Column] as GridColumn;
col[Rows[position.Row]] = cell;
}
else
throw new SourceGridException("Invalid OptimizeMode");
}
/// <summary>
/// Adds an instance of type Cells.ICellVirtual to the end of this CellBaseCollection.
/// </summary>
/// <param name="value">
/// The Cells.ICellVirtual to be added to the end of this CellBaseCollection.
/// </param>
public virtual void Add(Cells.ICellVirtual value)
{
this.List.Add(value);
}
/// <summary>
/// Check
/// </summary>
private void EnsureNoOtherCellsExist(int row, int col, Cells.ICell p_cell)
{
for (int y = row; y < row + p_cell.RowSpan; y++)
{
for (int x = col; x < col + p_cell.ColumnSpan; x++)
{
if (x == col && y == row)
continue;
Cells.ICell existingSpannedCell = this.DirectGetCell(y, x);
if (existingSpannedCell != null)
if (existingSpannedCell != p_cell)
throw new OverlappingCellException(string.Format(
"Given cell at position ({0}, {1}), " +
"intersects with another cell " +
"at position ({2}, {3}) '{4}'",
row, col,
existingSpannedCell.Row.Index,
existingSpannedCell.Column.Index,
existingSpannedCell.DisplayText));
}
}
}
public HistoryRow(PolicyAlert alert)
{
Alert = alert;
Cells.AddRange(_expand, _result, _dateTime, _description);
RefreshRow();
}
/// <summary>
/// Force a redraw of the specified cell
/// </summary>
/// <param name="p_Cell"></param>
public virtual void InvalidateCell(Cells.ICell p_Cell)
{
if (p_Cell!=null)
base.InvalidateRange(p_Cell.Range);
}
private static void putValue(Cells cell, object value, int row, int column, Style st)
{
cell[row, column].PutValue(value);
cell[row, column].SetStyle(st);
}
/// <summary>
/// Return the zero-based index of the first occurrence of a specific value
/// in this CellBaseCollection
/// </summary>
/// <param name="value">
/// The Cells.ICellVirtual value to locate in the CellBaseCollection.
/// </param>
/// <returns>
/// The zero-based index of the first occurrence of the _ELEMENT value if found;
/// -1 otherwise.
/// </returns>
public virtual int IndexOf(Cells.ICellVirtual value)
{
return this.List.IndexOf(value);
}
protected void btn_saveExcel_Click(object sender, EventArgs e)
{
//btn_Refresh_Click(null, null);
//gridExport1.WriteXlsToResponse("Driver", true);
License lic = new License();
lic.SetLicense(HttpContext.Current.Server.MapPath(@"~\Aspose.lic"));
Workbook workbook = new Workbook();
string sql_driver = string.Format(@"select Code from CTM_Driver");
DataTable tab_driver = ConnectSql.GetTab(sql_driver);
for (int i = 0; i < tab_driver.Rows.Count; i++)
{
string code = SafeValue.SafeString(tab_driver.Rows[i]["Code"]);
if (date_searchDate.Date < new DateTime(1900, 1, 1))
{
date_searchDate.Date = DateTime.Now;
}
string sql = string.Format(@"select det2.ContainerNo,det1.ContainerType,det2.ChessisCode,det2.FromCode,det2.ToCode,det2.FromTime,det2.Incentive1,det2.Incentive2,det2.Incentive3,det2.Statuscode
from CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.JobNo=det1.JobNo and det2.ContainerNo=det1.ContainerNo
left outer join CTM_Job as job on det2.JobNo=job.JobNo
where datediff(d,'{1}',det2.FromDate)=0 and det2.DriverCode='{0}' and job.StatusCode<>'CNL'
order by (case det2.Statuscode when 's' then 0 when 'c' then 1 else 2 end) ,det2.Id", code, date_searchDate.Date);//(select top(1) * from {0}) UNION (select * from {0} where rownum == (select count(*) from {0})) select top(10) * from {0} where rownum=1
DataTable dtTableName = ConnectSql.GetTab(sql);
//throw new Exception(dtTableName.Rows.Count.ToString());
Worksheet worksheet = workbook.Worksheets[0];;
if (i != 0)
{
worksheet = workbook.Worksheets.Add(code);
}
worksheet.Name = code;
Cells cells = worksheet.Cells;
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial"; //文字字体 ,宋体
style.Font.Size = 10; //文字大小
style.Font.IsBold = true; //粗体
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "Arial"; //文字字体 ,宋体
style1.Font.Size = 10; //文字大小
style1.Font.IsBold = false; //粗体
style1.Borders[BorderType.TopBorder].Color = Color.Black;
style1.Borders[BorderType.BottomBorder].Color = Color.Black;
style1.Borders[BorderType.LeftBorder].Color = Color.Black;
style1.Borders[BorderType.RightBorder].Color = Color.Black;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style1.BackgroundColor = Color.FromArgb(0xff, 0xff, 0x00);
style2.Font.Name = "Arial"; //文字字体 ,宋体
style2.Font.Size = 10; //文字大小
style2.Font.IsBold = false; //粗体
style2.Borders[BorderType.TopBorder].Color = Color.Black;
style2.Borders[BorderType.BottomBorder].Color = Color.Black;
style2.Borders[BorderType.LeftBorder].Color = Color.Black;
style2.Borders[BorderType.RightBorder].Color = Color.Black;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.HorizontalAlignment = TextAlignmentType.Left;//文字居中
cells[0, 0].PutValue("S/N");
cells[0, 1].PutValue("ContainerNo");
cells[0, 2].PutValue("ContainerType");
cells[0, 3].PutValue("Trailer");
cells[0, 4].PutValue("From");
cells[0, 5].PutValue("To");
cells[0, 6].PutValue("Time");
cells[0, 7].PutValue("Incentive");
cells[0, 8].PutValue("Overtime");
cells[0, 9].PutValue("Standby");
cells[0, 10].PutValue("Status");
cells[0, 0].SetStyle(style);
cells[0, 1].SetStyle(style);
cells[0, 2].SetStyle(style);
cells[0, 3].SetStyle(style);
cells[0, 4].SetStyle(style);
cells[0, 5].SetStyle(style);
cells[0, 6].SetStyle(style);
cells[0, 7].SetStyle(style);
cells[0, 8].SetStyle(style);
cells[0, 9].SetStyle(style);
cells[0, 10].SetStyle(style);
cells.SetColumnWidth(0, 5);
cells.SetColumnWidth(1, 20);
cells.SetColumnWidth(2, 20);
cells.SetColumnWidth(3, 20);
cells.SetColumnWidth(4, 20);
cells.SetColumnWidth(5, 20);
cells.SetColumnWidth(6, 10);
cells.SetColumnWidth(7, 10);
cells.SetColumnWidth(8, 10);
cells.SetColumnWidth(9, 10);
cells.SetColumnWidth(10, 5);
for (int n = 0; n < dtTableName.Rows.Count; n++)
{
cells[n + 1, 0].PutValue(n + 1);
cells[n + 1, 1].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["ContainerNo"]));
cells[n + 1, 2].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["ContainerType"]));
cells[n + 1, 3].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["ChessisCode"]));
cells[n + 1, 4].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["FromCode"]));
cells[n + 1, 5].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["ToCode"]));
cells[n + 1, 6].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["FromTime"]));
cells[n + 1, 7].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["Incentive1"]));
cells[n + 1, 8].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["Incentive2"]));
cells[n + 1, 9].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["Incentive3"]));
cells[n + 1, 10].PutValue(SafeValue.SafeString(dtTableName.Rows[n]["Statuscode"]));
cells[n + 1, 0].SetStyle(style1);
cells[n + 1, 1].SetStyle(style2);
cells[n + 1, 2].SetStyle(style2);
cells[n + 1, 3].SetStyle(style2);
cells[n + 1, 4].SetStyle(style2);
cells[n + 1, 5].SetStyle(style2);
cells[n + 1, 6].SetStyle(style2);
cells[n + 1, 7].SetStyle(style2);
cells[n + 1, 8].SetStyle(style2);
cells[n + 1, 9].SetStyle(style2);
cells[n + 1, 10].SetStyle(style2);
}
}
string path0 = string.Format("~/Excel/Driver_Trip_{0:yyyyMMdd}.xlsx",
DateTime.Now.ToString("yyyyMMdd-HHmmss") ?? "01-01-2014"); //Request.QueryString["d"]
string path = HttpContext.Current.Server.MapPath(path0); //POD_RECORD
//workbook.Save(path);
System.IO.MemoryStream ms = workbook.SaveToStream(); //生成数据流
byte[] bt = ms.ToArray();
workbook.Save(path);
Response.Redirect(path0.Substring(1));
}
/// <summary>
/// Removes the first occurrence of a specific Cells.ICellVirtual from this CellBaseCollection.
/// </summary>
/// <param name="value">
/// The Cells.ICellVirtual value to remove from this CellBaseCollection.
/// </param>
public virtual void Remove(Cells.ICellVirtual value)
{
this.List.Remove(value);
}
public void DocDuLieu(string filePath)
{
try
{
for (int j = 0; j < 10; j++)
{
try
{
Workbook workbook = new Workbook(filePath);
Cells cells = workbook.Worksheets[j].Cells;
try
{
int lastrow = cells.EndCellInColumn((short)0).Row;
for (int i = 0; i <= lastrow; i++)
{
try
{
int lostColumn = cells.EndCellInRow(i).Column;
Email email = new Email();
email.STT = (danhSachTaiKhoanTatCa.Count + 1).ToString();
try
{
email.UID = cells[i, CellsHelper.ColumnNameToIndex("B")].Value.ToString();
}
catch { }
try
{
email.Name = cells[i, CellsHelper.ColumnNameToIndex("C")].Value.ToString();
}
catch { }
try
{
email.Mail = cells[i, CellsHelper.ColumnNameToIndex("G")].Value.ToString();
}
catch { }
try
{
email.SoBan = int.Parse(cells[i, CellsHelper.ColumnNameToIndex("Q")].Value.ToString());
}
catch { }
try
{
email.QuocGia = (cells[i, CellsHelper.ColumnNameToIndex("I")].Value.ToString());
}
catch { }
if (email.SoBan != -1 && !string.IsNullOrEmpty(email.Mail) && !email.Mail.Contains(".vn"))
{
List <string> regex = email.Mail.Split(new[] { ".com" }, StringSplitOptions.None).ToList();
if (string.IsNullOrEmpty(regex[regex.Count - 1]))
{
danhSachTaiKhoanTatCa.Add(email);
}
}
}
catch
{
}
}
}
catch
{
}
}
catch
{
}
}
}
catch
{
MessageBox.Show("File excel lỗi!!", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
ReLoadData(cbLoaiMail.SelectedItem as LoaiMail, cbQuocGia.SelectedItem as QuocGia);
MessageBox.Show("Load xong!", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo);
xls.SummaryInformation.Author = "yongfa365"; //填加xls文件作者信息
xls.SummaryInformation.NameOfCreatingApplication = "liu yongfa"; //填加xls文件创建程序信息
xls.SummaryInformation.LastSavedBy = "LastSavedBy"; //填加xls文件最后保存者信息
xls.SummaryInformation.Comments = "Comments"; //填加xls文件作者信息
xls.SummaryInformation.Title = "title"; //填加xls文件标题信息
xls.SummaryInformation.Subject = "Subject"; //填加文件主题信息
xls.DocumentSummaryInformation.Company = "company"; //填加文件公司信息
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");//状态栏标题名称
Cells cells = sheet.Cells;
foreach (DataColumn col in dtSource.Columns)
{
Cell cell = cells.Add(1, col.Ordinal + 1, col.ColumnName);
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = true; //字体为粗体
}
#region 填充内容
XF dateStyle = xls.NewXF();
dateStyle.Format = "yyyy-mm-dd";
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int rowIndex = i + 2;
int colIndex = j + 1;
string drValue = dtSource.Rows[i][j].ToString();
switch (dtSource.Rows[i][j].GetType().ToString())
{
case "System.String": //字符串类型
cells.Add(rowIndex, colIndex, drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cells.Add(rowIndex, colIndex, dateV, dateStyle);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cells.Add(rowIndex, colIndex, boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cells.Add(rowIndex, colIndex, intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cells.Add(rowIndex, colIndex, doubV);
break;
case "System.DBNull": //空值处理
cells.Add(rowIndex, colIndex, null);
break;
default:
cells.Add(rowIndex, colIndex, null);
break;
}
}
}
#endregion
//foreach (DataRow row in dtSource.Rows)
//{
// rowIndex++;
// colIndex = 0;
// foreach (DataColumn col in dtSource.Columns)
// {
// colIndex++;
// cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());//全都当文本型处理
// }
//}
xls.FileName = strFileName;
xls.Save();
}
/// <summary>
/// DataTable生成Excel
/// </summary>
/// <param name="dtList">DataTable</param>
/// <param name="fileName">文件名</param>
/// <returns>返回文件名(包含扩展名)</returns>
public static string ExportToExcel(DataTable dtList, string fileName)
{
string pathToFiles = System.Web.HttpContext.Current.Server.MapPath(ExportFilesPath);
string etsName = ".xls";
//获取保存路径
string path = pathToFiles + fileName + etsName;
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
//cell.SetRowHeight(0, 20); //设置行高
//表头样式
Style stHeadLeft = wb.Styles[wb.Styles.Add()];
stHeadLeft.HorizontalAlignment = TextAlignmentType.Left; //文字居中
stHeadLeft.Font.Name = "宋体";
stHeadLeft.Font.IsBold = true; //设置粗体
stHeadLeft.Font.Size = 14; //设置字体大小
Style stHeadRight = wb.Styles[wb.Styles.Add()];
stHeadRight.HorizontalAlignment = TextAlignmentType.Right; //文字居中
stHeadRight.Font.Name = "宋体";
stHeadRight.Font.IsBold = true; //设置粗体
stHeadRight.Font.Size = 14; //设置字体大小
//内容样式
Style stContentLeft = wb.Styles[wb.Styles.Add()];
stContentLeft.HorizontalAlignment = TextAlignmentType.Left;
stContentLeft.Font.Size = 10;
Style stContentRight = wb.Styles[wb.Styles.Add()];
stContentRight.HorizontalAlignment = TextAlignmentType.Right;
stContentRight.Font.Size = 10;
//赋值给Excel内容
for (int col = 0; col < dtList.Columns.Count; col++)
{
Style stHead = null;
Style stContent = null;
//设置表头
string columnType = dtList.Columns[col].DataType.ToString();
switch (columnType.ToLower())
{
case "system.string":
stHead = stHeadLeft;
stContent = stContentLeft;
break;
default:
stHead = stHeadRight;
stContent = stContentRight;
break;
}
//插入表头到Excel内
cell[0, col].PutValue(dtList.Columns[col].ColumnName);
cell[0, col].SetStyle(stHead);
for (int row = 0; row < dtList.Rows.Count; row++)
{
object _value = dtList.Rows[row][col];
if (_value != Convert.DBNull)
{
//插入内容到Excel内
switch (columnType.ToLower())
{
case "system.int32":
cell[row + 1, col].PutValue(Convert.ToInt32(_value));
break;
case "system.int64":
cell[row + 1, col].PutValue(Convert.ToInt64(_value));
break;
case "system.datetime":
cell[row + 1, col].PutValue(Convert.ToDateTime(_value).ToString("yyyy/M/d HH:mm"));
break;
case "system.boolean":
cell[row + 1, col].PutValue(Convert.ToBoolean(_value));
break;
default:
cell[row + 1, col].PutValue(_value.ToString());
break;
}
}
cell[row + 1, col].SetStyle(stContent);
}
}
wb.Save(path);
return(ExportFilesPath + fileName + etsName);
}
/// <summary>
/// Determines whether a specfic Cells.ICellVirtual value is in this CellBaseCollection.
/// </summary>
/// <param name="value">
/// The Cells.ICellVirtual value to locate in this CellBaseCollection.
/// </param>
/// <returns>
/// true if value is found in this CellBaseCollection;
/// false otherwise.
/// </returns>
public virtual bool Contains(Cells.ICellVirtual value)
{
return this.List.Contains(value);
}
public bool IsBlank()
{
return(Cells.All(c => string.IsNullOrEmpty(c.Value)));
}
