internal static void trigger(XF.Event which_event)
{
basic_event e = find_event(which_event);
if (e != null)
e();
}
/// <summary>
/// Add a xf biff record to the internal data list
/// </summary>
/// <param name="xf"></param>
public void addXFDataValue(XF xf)
{
XFData xfdata = new XFData();
xfdata.fStyle = xf.fStyle;
xfdata.ifmt = xf.ifmt;
xfdata.ixfParent = xf.ixfParent;
if (xf.fWrap != 0)
{
xfdata.wrapText = true;
xfdata.hasAlignment = true;
}
if (xf.alc != 0xFF)
{
xfdata.hasAlignment = true;
xfdata.horizontalAlignment = xf.alc;
}
if (xf.alcV != 0x02)
{
xfdata.hasAlignment = true;
}
xfdata.verticalAlignment = xf.alcV;
if (xf.fJustLast != 0)
{
xfdata.hasAlignment = true;
xfdata.justifyLastLine = true;
}
if (xf.fShrinkToFit != 0)
{
xfdata.hasAlignment = true;
xfdata.shrinkToFit = true;
}
if (xf.trot != 0)
{
xfdata.hasAlignment = true;
xfdata.textRotation = xf.trot;
}
if (xf.cIndent != 0)
{
xfdata.hasAlignment = true;
xfdata.indent = xf.cIndent;
}
if (xf.iReadOrder != 0)
{
xfdata.hasAlignment = true;
xfdata.readingOrder = xf.iReadOrder;
}
// the first three fontids are zero based
// beginning with four the fontids are one based
if (xf.ifnt > 4)
{
xfdata.fontId = xf.ifnt - 1;
}
else
{
xfdata.fontId = xf.ifnt;
}
if (xf.fStyle == 1)
{
this.xfCellStyleDataList.Add(xfdata);
}
else
{
this.xfCellDataList.Add(xfdata);
}
int countxf = this.XFCellDataList.Count + this.xfCellStyleDataList.Count;
FillData fd = new FillData((StyleEnum)xf.fls, xf.icvFore, xf.icvBack);
int fillDataId = this.addFillDataValue(fd);
TraceLogger.DebugInternal(fd.ToString() + " -- Number XF " + countxf.ToString() + " -- Number FillData: " + this.fillDataList.Count);
xfdata.fillId = fillDataId;
// add border data
BorderData borderData = new BorderData();
// diagonal value
borderData.diagonalValue = (ushort)xf.grbitDiag;
// create and add borderparts
BorderPartData top = new BorderPartData((ushort)xf.dgTop, xf.icvTop);
borderData.top = top;
BorderPartData bottom = new BorderPartData((ushort)xf.dgBottom, xf.icvBottom);
borderData.bottom = bottom;
BorderPartData left = new BorderPartData((ushort)xf.dgLeft, xf.icvLeft);
borderData.left = left;
BorderPartData right = new BorderPartData((ushort)xf.dgRight, xf.icvRight);
borderData.right = right;
BorderPartData diagonal = new BorderPartData((ushort)xf.dgDiag, xf.icvDiag);
borderData.diagonal = diagonal;
int borderId = this.addBorderDataValue(borderData);
xfdata.borderId = borderId;
}
/// <summary>
/// 绑定数据库生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = Server.UrlEncode(xlsName);
int rowIndex = 2;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 17 * 256;
sheet.AddColumnInfo(colInfo);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 6);
MergeRegion(ref sheet, xf, "分公司", 2, 2, 1, 1);
MergeRegion(ref sheet, xf, "外包单位", 2, 2, 2, 2);
MergeRegion(ref sheet, xf, "障碍与服务指标（85分）", 2, 2, 3, 3);
MergeRegion(ref sheet, xf, "基础管理考核（15分）", 2, 2, 4, 4);
MergeRegion(ref sheet, xf, "额外奖罚", 2, 2, 5, 5);
MergeRegion(ref sheet, xf, "汇总得分", 2, 2, 6, 6);
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
if (colIndex != 1)
{
cell.Font.Bold = false; //字体为粗体
}
}
}
MergeRegion(ref sheet, xf, "设计院", 3, 4, 2, 2);
MergeRegion(ref sheet, xf, "北京合力", 5, 8, 2, 2);
xls.Send();
Response.Flush();
Response.End();
}
protected virtual void SetXFForHeader(int sheetIndex, int excelColumnIndex, XF xf)
{
}
protected void ExcelOut(List <UserInfo> userList)
{
//生成Excel开始
string FilePath = "~/xml/Demo.xls";
XlsDocument xls = new XlsDocument(); //创建空xls文档
xls.FileName = Server.MapPath(FilePath); //保存路径，如果直接发送到客户端的话只需要名称 生成名称
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("会员列表"); //创建一个工作页为Dome
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, 6);//合并单元格 行与列
sheet.AddMergeArea(ma);
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
//cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
//cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
//cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//列操作基本
Cell cell = cells.Add(1, 1, "会员列表", cellXF);
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true; //设置粗体
cell.Font.ColorIndex = 0; //设置颜色码
cell.Font.FontFamily = FontFamilies.Roman; //设置字体 默认为宋体
//创建列结束
//创建数据
int LineNum = 3;
if (userList.Count > 0)
{
cells.Add(2, 1, "序号");
cells.Add(2, 2, "姓名");
cells.Add(2, 3, "公司简称");
cells.Add(2, 4, "品牌");
cells.Add(2, 5, "手机");
cells.Add(2, 6, "Email");
//cells.Add(2, 7, "原始岗位");
//cells.Add(2, 5, "性别");
//cells.Add(2, 8, "用户ID");
//cells.Add(2, 9, "工作岗位");
//cells.Add(2, 9, "学习岗位");
//cells.Add(2, 10, "帐户类型");
foreach (UserInfo Info in userList)
{
cells.Add(LineNum, 1, LineNum - 2);
cells.Add(LineNum, 2, Info.RealName);
cells.Add(LineNum, 3, ReadCompany(Info.CompanyID).CompanySimpleName);
cells.Add(LineNum, 4, ReadBrandName(ProductBrandBLL.ReadProductBrandCacheList(StringHelper.SubString(ReadCompany(Info.CompanyID).BrandId, "17"))));
cells.Add(LineNum, 5, Info.Mobile);
cells.Add(LineNum, 6, Info.Email);
//cells.Add(LineNum, 7, Info.PostName);
//cells.Add(LineNum, 5, EnumHelper.ReadEnumChineseName<SexType>(Info.Sex));
//cells.Add(LineNum, 2, PostBLL.ReadPost(Info.Department).PostName);
//cells.Add(LineNum, 8, Info.UserName);
//cells.Add(LineNum, 9, BLLPost.ReadPost(Info.PostId).PostName);
//cells.Add(LineNum, 9, PostBLL.ReadPost(Info.StudyPostID).PostName);
//cells.Add(LineNum, 10, AdminGroupBLL.ReadAdminGroupCache(Info.GroupID).Name);
LineNum = LineNum + 1;
}
}
//
//生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
//ScriptHelper.Alert(Server.MapPath("~/Demo.xls"));
if (File.Exists(Server.MapPath(FilePath)))
{
File.Delete(Server.MapPath(FilePath));//删除
}
//保存文档
xls.Save(Server.MapPath(FilePath)); //保存到服务器
xls.Send(); //发送到客户端
}
/// <summary>
/// Extracts the data from the stream
/// </summary>
public override void extractData()
{
BiffHeader bh;
//try
//{
while (this.StreamReader.BaseStream.Position < this.StreamReader.BaseStream.Length)
{
bh.id = (RecordType)this.StreamReader.ReadUInt16();
bh.length = this.StreamReader.ReadUInt16();
// Debugging output
TraceLogger.DebugInternal("BIFF {0}\t{1}\t", bh.id, bh.length);
switch (bh.id)
{
case RecordType.BoundSheet8:
{
// Extracts the Boundsheet data
BoundSheet8 bs = new BoundSheet8(this.StreamReader, bh.id, bh.length);
TraceLogger.DebugInternal(bs.ToString());
SheetData sheetData = null;
switch (bs.dt)
{
case BoundSheet8.SheetType.Worksheet:
sheetData = new WorkSheetData();
this.oldOffset = this.StreamReader.BaseStream.Position;
this.StreamReader.BaseStream.Seek(bs.lbPlyPos, SeekOrigin.Begin);
WorksheetExtractor se = new WorksheetExtractor(this.StreamReader, sheetData as WorkSheetData);
this.StreamReader.BaseStream.Seek(oldOffset, SeekOrigin.Begin);
break;
case BoundSheet8.SheetType.Chartsheet:
ChartSheetData chartSheetData = new ChartSheetData();
this.oldOffset = this.StreamReader.BaseStream.Position;
this.StreamReader.BaseStream.Seek(bs.lbPlyPos, SeekOrigin.Begin);
chartSheetData.ChartSheetSequence = new ChartSheetSequence(this.StreamReader);
this.StreamReader.BaseStream.Seek(oldOffset, SeekOrigin.Begin);
sheetData = chartSheetData;
break;
default:
TraceLogger.Info("Unsupported sheet type: {0}", bs.dt);
break;
}
if (sheetData != null)
{
// add general sheet info
sheetData.boundsheetRecord = bs;
this.workBookData.addBoundSheetData(sheetData);
}
}
break;
case RecordType.Template:
{
this.workBookData.Template = true;
}
break;
case RecordType.SST:
{
/* reads the shared string table biff record and following continue records
* creates an array of bytes and then puts that into a memory stream
* this all is used to create a longer biffrecord then 8224 bytes. If theres a string
* beginning in the SST that is then longer then the 8224 bytes, it continues in the
* CONTINUE BiffRecord, so the parser has to read over the SST border.
* The problem here is, that the parser has to overread the continue biff record header
*/
//SST sst;
//UInt16 length = bh.length;
//// save the old offset from this record begin
//this.oldOffset = this.StreamReader.BaseStream.Position;
//// create a list of bytearrays to store the following continue records
//// List<byte[]> byteArrayList = new List<byte[]>();
//byte[] buffer = new byte[length];
//LinkedList<VirtualStreamReader> vsrList = new LinkedList<VirtualStreamReader>();
//buffer = this.StreamReader.ReadBytes((int)length);
//// byteArrayList.Add(buffer);
//// create a new memory stream and a new virtualstreamreader
//MemoryStream bufferstream = new MemoryStream(buffer);
//VirtualStreamReader binreader = new VirtualStreamReader(bufferstream);
//BiffHeader bh2;
//bh2.id = (RecordType)this.StreamReader.ReadUInt16();
//while (bh2.id == RecordType.Continue)
//{
// bh2.length = (UInt16)(this.StreamReader.ReadUInt16());
// buffer = new byte[bh2.length];
// // create a buffer with the bytes from the records and put that array into the
// // list
// buffer = this.StreamReader.ReadBytes((int)bh2.length);
// // byteArrayList.Add(buffer);
// // create for each continue record a new streamreader !!
// MemoryStream contbufferstream = new MemoryStream(buffer);
// VirtualStreamReader contreader = new VirtualStreamReader(contbufferstream);
// vsrList.AddLast(contreader);
// // take next Biffrecord ID
// bh2.id = (RecordType)this.StreamReader.ReadUInt16();
//}
//// set the old position of the stream
//this.StreamReader.BaseStream.Position = this.oldOffset;
SST sst = new SST(this.StreamReader, bh.id, bh.length);
//this.StreamReader.BaseStream.Position = this.oldOffset + bh.length;
this.workBookData.SstData = sst;
}
break;
case RecordType.EOF:
{
// Reads the end of the internal file !!!
this.StreamReader.BaseStream.Seek(0, SeekOrigin.End);
}
break;
case RecordType.ExternSheet:
{
ExternSheet extsheet = new ExternSheet(this.StreamReader, bh.id, bh.length);
this.externSheets.Add(extsheet);
this.workBookData.addExternSheetData(extsheet);
}
break;
case RecordType.SupBook:
{
SupBook supbook = new SupBook(this.StreamReader, bh.id, bh.length);
this.supBooks.Add(supbook);
this.workBookData.addSupBookData(supbook);
}
break;
case RecordType.XCT:
{
XCT xct = new XCT(this.StreamReader, bh.id, bh.length);
this.XCTList.Add(xct);
this.workBookData.addXCT(xct);
}
break;
case RecordType.CRN:
{
CRN crn = new CRN(this.StreamReader, bh.id, bh.length);
this.CRNList.Add(crn);
this.workBookData.addCRN(crn);
}
break;
case RecordType.ExternName:
{
ExternName externname = new ExternName(this.StreamReader, bh.id, bh.length);
this.workBookData.addEXTERNNAME(externname);
}
break;
case RecordType.Format:
{
Format format = new Format(this.StreamReader, bh.id, bh.length);
this.workBookData.styleData.addFormatValue(format);
}
break;
case RecordType.XF:
{
XF xf = new XF(this.StreamReader, bh.id, bh.length);
this.workBookData.styleData.addXFDataValue(xf);
}
break;
case RecordType.Style:
{
Style style = new Style(this.StreamReader, bh.id, bh.length);
this.workBookData.styleData.addStyleValue(style);
}
break;
case RecordType.Font:
{
Font font = new Font(this.StreamReader, bh.id, bh.length);
this.workBookData.styleData.addFontData(font);
}
break;
case RecordType.NAME:
case RecordType.Lbl:
{
Lbl name = new Lbl(this.StreamReader, bh.id, bh.length);
this.workBookData.addDefinedName(name);
}
break;
case RecordType.BOF:
{
this.workBookData.BOF = new BOF(this.StreamReader, bh.id, bh.length);
}
break;
case RecordType.CodeName:
{
this.workBookData.CodeName = new CodeName(this.StreamReader, bh.id, bh.length);
}
break;
case RecordType.FilePass:
{
throw new ExtractorException(ExtractorException.FILEENCRYPTED);
}
break;
case RecordType.Palette:
{
Palette palette = new Palette(this.StreamReader, bh.id, bh.length);
workBookData.styleData.setColorList(palette.rgbColorList);
}
break;
default:
{
// this else statement is used to read BiffRecords which aren't implemented
byte[] buffer = new byte[bh.length];
buffer = this.StreamReader.ReadBytes(bh.length);
TraceLogger.Debug("Unknown record found. ID {0}", bh.id);
}
break;
}
}
//}
//catch (Exception ex)
//{
// TraceLogger.Error(ex.Message);
// TraceLogger.Debug(ex.ToString());
//}
}
public void Print()
{
//区域
var activitareaid = HttpContext.Current.Request.QueryString["activitareaid"].ToString();
//经销商
var agencyid = HttpContext.Current.Request.QueryString["agencyid"].ToString();
//类型
var saleactivitytypeid = HttpContext.Current.Request.QueryString["saleactivitytypeid"].ToString();
var begindate = HttpContext.Current.Request.QueryString["begindate"].ToString();
var enddate = HttpContext.Current.Request.QueryString["enddate"].ToString();
IDatabase database = DataFactory.Database();
IRepository <ActivityViewModel> re = new Repository <ActivityViewModel>();
DataTable data = new DataTable();
string strwhere = "";
if (!string.IsNullOrEmpty(activitareaid))
{
strwhere += " and ActivitAreaId=" + activitareaid;
}
if (!string.IsNullOrEmpty(agencyid))
{
strwhere += " and AgencyId=" + agencyid;
}
if (!string.IsNullOrEmpty(saleactivitytypeid))
{
strwhere += " and SaleActivityTypeId=" + saleactivitytypeid;
}
if (!string.IsNullOrEmpty(begindate))
{
strwhere += " and ActivityDate>='" + begindate + "'";
}
if (!string.IsNullOrEmpty(enddate))
{
strwhere += " and ActivityDate<='" + enddate + "'";
}
data = database.FindTableBySql("select * from View_Activity where IsDelete=0 " + strwhere + "");
var newd = data.Columns.Add("publishwaycontent", typeof(String));
var list = new Utilities.PublishWay().ToSelectListItem();
for (int i = 0; i < data.Rows.Count; i++)
{
string str = data.Rows[i]["publishway"].ToString();
if (!string.IsNullOrEmpty(str))
{
string[] arrstr = str.Split(',');
string publishwaycontent = "";
if (!string.IsNullOrEmpty(str))
{
foreach (var item in list)
{
foreach (var item1 in arrstr)
{
if (item.Value == item1)
{
publishwaycontent += item.Text + ",";
}
}
}
}
data.Rows[i]["publishwaycontent"] = publishwaycontent.Substring(0, publishwaycontent.Length - 1);
}
else
{
data.Rows[i]["publishwaycontent"] = "";
}
}
AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
// HttpContext.Current.Response.Write();
doc.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string sheetname = "SHEET";
Worksheet sheet = doc.Workbook.Worksheets.Add(sheetname);
Cells cells = sheet.Cells;
XF XFstyle = doc.NewXF();
XFstyle.HorizontalAlignment = HorizontalAlignments.Centered;
cells.Add(1, 1, "经销商名称");
cells.Add(1, 2, "区域");
cells.Add(1, 3, "日期");
cells.Add(1, 4, "活动类别");
cells.Add(1, 5, "活动客流量");
cells.Add(1, 6, "活动潜客量");
cells.Add(1, 7, "活动车主量");
cells.Add(1, 8, "活动现场订单量");
cells.Add(1, 9, "活动成本金额");
cells.Add(1, 10, "活动后续订单量");
cells.Add(1, 11, "宣传类型");
int f = 1;
for (int i = 0; i < data.Rows.Count; i++)
{
f++;
cells.Add(f, 1, data.Rows[i]["agencyname"]);
cells.Add(f, 2, data.Rows[i]["activityareaname"]);
cells.Add(f, 3, DateTime.Parse(data.Rows[i]["activitydate"].ToString()).ToString("yyyy-MM-dd"));
cells.Add(f, 4, data.Rows[i]["saleactivitytypename"]);
cells.Add(f, 5, data.Rows[i]["passengerflow"]);
cells.Add(f, 6, data.Rows[i]["latentpassengerflow"]);
cells.Add(f, 7, data.Rows[i]["carowner"]);
cells.Add(f, 8, data.Rows[i]["orderquantity"]);
cells.Add(f, 9, data.Rows[i]["primecost"]);
cells.Add(f, 10, data.Rows[i]["laterorderquantity"]);
cells.Add(f, 11, data.Rows[i]["publishwaycontent"]);
}
doc.Send();
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
protected override void Create()
{
XF fmtHeaderCell = NewXF();
fmtHeaderCell.Font.Bold = true;
XF fmtText = NewXF();
XF fmtTimestamp = NewXF();
fmtTimestamp.Format = "hh:mm:ss";
XF fmtData = NewXF();
fmtData.Format = "#,##0";
foreach (EmitScoreDataSet.CategoryRow category in _dataSet.Category)
{
Worksheet sht = Workbook.Worksheets.AddNamed(category.CategoryName);
sht.Cells.AddValueCell(1, 1, "Team", fmtHeaderCell);
sht.Cells.AddValueCell(1, 2, "Total Points", fmtHeaderCell);
sht.Cells.AddValueCell(1, 3, "Total Time", fmtHeaderCell);
sht.Cells.AddValueCell(1, 4, "Nett Points", fmtHeaderCell);
sht.Cells.AddValueCell(1, 5, "Time Disqualified", fmtHeaderCell);
EmitScoreDataSet.ReportTeamResultRow[] tr =
(EmitScoreDataSet.ReportTeamResultRow[])
_dataSet.ReportTeamResult.Select(String.Format("CategoryId='{0}'", category.CategoryId));
for (int r = 0; r < tr.Length; r++)
{
sht.Cells.AddValueCell(r + 2, 1, tr[r].TeamName, fmtText);
if (!tr[r].IsTotalPointsNull())
{
sht.Cells.AddValueCell(r + 2, 2, tr[r].TotalPoints, fmtData);
}
if (!tr[r].IsTotalTimeSecondsNull())
{
DateTime totalTime = Swipe.CreateBaseDate();
totalTime = totalTime.AddSeconds(tr[r].TotalTimeSeconds);
sht.Cells.AddValueCell(r + 2, 3, totalTime.ToString("HH:mm:ss"), fmtTimestamp);
}
if (!tr[r].IsNettPointsNull())
{
sht.Cells.AddValueCell(r + 2, 4, tr[r].NettPoints, fmtData);
}
if (!tr[r].IsTimeDisqualifiedNull())
{
if (tr[r].TimeDisqualified == 1)
{
sht.Cells.AddValueCell(r + 2, 5, "Yes", fmtText);
}
}
}
}
foreach (EmitScoreDataSet.CategoryRow category in _dataSet.Category)
{
Worksheet sht = Workbook.Worksheets.AddNamed(String.Format("{0} Detail", category.CategoryName));
sht.Cells.AddValueCell(1, 1, "Team", fmtHeaderCell);
sht.Cells.AddValueCell(1, 2, "Group", fmtHeaderCell);
sht.Cells.AddValueCell(1, 3, "Group Id", fmtHeaderCell);
sht.Cells.AddValueCell(1, 4, "Location Id", fmtHeaderCell);
sht.Cells.AddValueCell(1, 5, "Points", fmtHeaderCell);
sht.Cells.AddValueCell(1, 6, "Time", fmtHeaderCell);
sht.Cells.AddValueCell(1, 7, "Cum.Time", fmtHeaderCell);
sht.Cells.AddValueCell(1, 8, "Sequence", fmtHeaderCell);
EmitScoreDataSet.ReportGroupResultRow[] tr =
(EmitScoreDataSet.ReportGroupResultRow[])
_dataSet.ReportGroupResult.Select(String.Format("CategoryId='{0}'", category.CategoryId));
for (int r = 0; r < tr.Length; r++)
{
if (!tr[r].IsTeamNameNull())
{
sht.Cells.AddValueCell(r + 2, 1, tr[r].TeamName, fmtText);
}
sht.Cells.AddValueCell(r + 2, 2, tr[r].GroupName, fmtText);
sht.Cells.AddValueCell(r + 2, 3, tr[r].GroupId, fmtText);
sht.Cells.AddValueCell(r + 2, 4, tr[r].LocationId, fmtText);
if (!tr[r].IsPointsNull())
{
sht.Cells.AddValueCell(r + 2, 5, tr[r].Points, fmtData);
}
if (!tr[r].IsTimeNull())
{
sht.Cells.AddValueCell(r + 2, 6, tr[r].Time.ToString("HH:mm:ss"), fmtTimestamp);
}
if (!tr[r].IsCumTimeNull())
{
sht.Cells.AddValueCell(r + 2, 7, tr[r].CumTime.ToString("HH:mm:ss"), fmtTimestamp);
}
sht.Cells.AddValueCell(r + 2, 8, tr[r].ResultId, fmtData);
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss"));
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Bold = true;
titleXF1.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "NO#", titleXF1);
cells.Add(1, 2, "Status", titleXF1);
cells.Add(1, 3, "Claim Type", titleXF1);
cells.Add(1, 4, "Amount", titleXF1);
cells.Add(1, 5, "Process", titleXF1);
cells.Add(1, 6, "Current Approver", titleXF1);
cells.Add(1, 7, "Owner", titleXF1);
cells.Add(1, 8, "Submitted by", titleXF1);
cells.Add(1, 9, "Submit date", titleXF1);
cells.Add(1, 10, "Remark", titleXF1);
//添加数据
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml.InnerXml);
for (int i = 0; i < doc.SelectNodes("records").Item(0).SelectNodes("record").Count; i++)
{
if (!string.IsNullOrEmpty(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml))
{
cells.Add(2 + i, 4, Convert.ToDouble(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml), titleXF);
}
else
{
cells.Add(2 + i, 4, "", titleXF);
}
cells.Add(2 + i, 1, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("No").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 2, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Draft1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 3, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Type1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 5, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Status1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 6, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Approver").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 7, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Person").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 8, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedBy").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 9, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedDate").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 10, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Remark").Item(0).InnerXml, titleXF);
}
xls.Send();
}
public void ProcessRequest(HttpContext context)
{
List <DataLists> dataLists = new List <DataLists>();
{
int LocationID = 1;
//List<DataList> dataLists = new List<DataList>();
int RunMachNum = 0;
int AllMachNum;
using (JDJS_WMS_DB_USEREntities wms = new JDJS_WMS_DB_USEREntities())
{
var devices = wms.JDJS_WMS_Device_Info;
AllMachNum = devices.Count();
foreach (var device in devices)
{
int cncids = device.ID;
var state = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (state != null)
{
if (state.ProgState == 1)
{
int cncid = device.ID;
var platecnc = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == cncid);
if (platecnc.Count() > 0)
{
//RunMachNum++;
}
}
}
DataLists data = new DataLists();
data.cncNum = device.MachNum;
data.cncType = device.MachState;
data.State = "/";
data.Time = DateTime.Now.Date.ToString();
var state1 = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (state1 != null)
{
if (state1.ProgState != -1)
{
data.State = "开机";
RunMachNum++;
}
}
var processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 2);
if (processing.Count() > 0)
{
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
var next = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime);
if (next.Count() > 0)
{
int ids = Convert.ToInt32(next.FirstOrDefault().ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == next.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingFileName = OrderNums + "-P" + processNums.ToString();
}
var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != processing.FirstOrDefault().ProcessID).OrderBy(r => r.StartTime);
if (nextTask.Count() > 0)
{
int ids = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingProcess = OrderNums + "-" + processNums.ToString() + "序";
}
}
else
{
processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime);
if (processing.Count() > 0)
{
if (processing.Count() > 1)
{
var pro = processing.ToList();
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
int ids = Convert.ToInt32(pro[1].ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
int idsss = Convert.ToInt32(pro[1].OrderID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == idsss).FirstOrDefault().Order_Number.ToString();
data.waitingFileName = OrderNums + "-P" + processNums.ToString();
var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != ids).OrderBy(r => r.StartTime);
if (nextTask.Count() > 0)
{
int idss = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID);
int processNumss = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == idss).FirstOrDefault().ProcessID);
string OrderNumss = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingProcess = OrderNumss + "-" + processNumss.ToString() + "序";
}
}
else
{
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
}
}
else
{
data.doingFileName = "无排配";
data.JiaWei = "/";
}
}
var endProcess = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == cncids && r.isFlag != 0);
var end = endProcess.OrderByDescending(r => r.EndTime).FirstOrDefault();
if (end != null)
{
data.StopTime = end.EndTime.ToString();
}
dataLists.Add(data);
}
foreach (var item in dataLists)
{
item.RunMachNum = RunMachNum.ToString();
item.AllMachNum = AllMachNum.ToString();
item.OtherMachNum = (AllMachNum - RunMachNum).ToString();
}
var lists = dataLists.OrderByDescending(r => r.progress);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
var model = new { code = 0, data = lists };
var json = serializer.Serialize(model);
}
}
XlsDocument doc = new XlsDocument();
doc.FileName = DateTime.Now.ToString("yyyyMMddhhmmssms") + "样机开机状况.xls";
Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
Cells cells = sheet.Cells;
ColumnInfo col = new ColumnInfo(doc, sheet); //创建列样式对象
col.ColumnIndexStart = 5; //起始列，索引从0开始
col.ColumnIndexEnd = 5; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col.Width = 8888; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col); //将列样式作用于此工作表
ColumnInfo col2 = new ColumnInfo(doc, sheet); //创建列样式对象
col2.ColumnIndexStart = 2; //起始列，索引从0开始
col2.ColumnIndexEnd = 2; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col2.Width = 4444; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col2); //将列样式作用于此工作表
ColumnInfo col4 = new ColumnInfo(doc, sheet); //创建列样式对象
col4.ColumnIndexStart = 4; //起始列，索引从0开始
col4.ColumnIndexEnd = 4; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col4.Width = 4444; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col4); //将列样式作用于此工作表
MergeArea ma1 = new MergeArea(1, 1, 1, 7); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma1); //添加合并单元格到工作表
MergeArea ma2 = new MergeArea(2, 2, 2, 6); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma2); //添加合并单元格到工作表
XF xf = doc.NewXF(); //单元格样式对象
xf.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf.PatternColor = Colors.Green; //填充背景底色
xf.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf.Font.FontName = "黑体"; //字体
xf.Font.Height = 15 * 15; //字体大小
//xf.UseBorder = false ; //使用边框
//xf.BottomLineStyle = 1; //边框样式
//xf.BottomLineColor = Colors.Black; //边框颜色
XF xf1 = doc.NewXF(); //单元格样式对象
xf1.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf1.HorizontalAlignment = HorizontalAlignments.Right; //水平居中
xf1.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf1.PatternColor = Colors.Green; //填充背景底色
xf1.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf1.Font.FontName = "宋体"; //字体
xf1.Font.Height = 13 * 13; //字体大小
//xf1.UseBorder = false ; //使用边框
//xf1.BottomLineStyle = 1; //边框样式
//xf1.BottomLineColor = Colors.Black; //边框颜色
XF xf2 = doc.NewXF(); //单元格样式对象
xf2.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf2.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf2.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf2.PatternColor = Colors.Green; //填充背景底色
xf2.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf2.Font.FontName = "宋体"; //字体
xf2.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
XF xf3 = doc.NewXF(); //单元格样式对象
xf3.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf3.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf3.Pattern = 1; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf3.PatternColor = Colors.Green; //填充背景底色
xf3.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf3.Font.FontName = "宋体"; //字体
xf3.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
cells.Add(1, 1, "智能中心样机开机状况", xf); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(2, 2, "日期：" + DateTime.Now.Date.ToShortDateString(), xf1); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(3, 1, "类别", xf2);
cells.Add(3, 2, "机台号", xf2);
cells.Add(3, 3, "文件名", xf2);
cells.Add(3, 4, "夹位", xf2);
cells.Add(3, 5, "开机状态", xf2);
cells.Add(3, 6, "CNC预计结束时间", xf2);
cells.Add(3, 7, "备注", xf2);
int index = 4;
for (int i = 0; i < dataLists.Count(); i++)
{
cells.Add(i + 4, 1, dataLists[i].cncType, xf2);
cells.Add(i + 4, 2, dataLists[i].cncNum, xf2);
if (dataLists[i].doingFileName == "无排配")
{
cells.Add(i + 4, 3, dataLists[i].doingFileName, xf2);
cells.Add(i + 4, 4, dataLists[i].JiaWei, xf2);
cells.Add(i + 4, 5, dataLists[i].State, xf2);
}
else
{
cells.Add(i + 4, 3, dataLists[i].doingFileName, xf3);
cells.Add(i + 4, 4, dataLists[i].JiaWei, xf3);
cells.Add(i + 4, 5, dataLists[i].State, xf3);
}
cells.Add(i + 4, 6, dataLists[i].StopTime, xf2);
cells.Add(i + 4, 7, "", xf2);
index++;
}
cells.Add(index, 1, "机台总数", xf);
//cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2);
cells.Add(index, 3, "实际开机数量", xf);
//cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2);
cells.Add(index, 5, "未开机数量", xf);
//cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2);
if (dataLists.Count() > 0)
{
int lastIndex = dataLists.Count() - 1;
//cells.Add(index, 1, "机台总数", xf);
cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2);
//cells.Add(index, 3, "实际开机数量", xf);
cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2);
//cells.Add(index, 5, "未开机数量", xf);
cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2);
}
PathInfo pathInfo = new PathInfo();
string path = pathInfo.upLoadPath() + @"ExcelFile\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
doc.Save(path);
context.Response.Write(pathInfo.downLoadPath() + @"ExcelFile\" + doc.FileName);
}
/// <summary>
/// 导出生成excel文件
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="fileName">文件名.xls</param>
/// <param name="longColumnsIndex">需要设置长宽度的列的集合eg."1,2,3,4"</param>
public static void CreateXls(DataTable dt, string fileName, string longColumnsIndex)
{
XlsDocument xls = new XlsDocument();
if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
xls.FileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
else
{
xls.FileName = fileName;
}
int rowIndex = 1;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置整体列宽带
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 22;
colInfo.Width = 14 * 256;
sheet.AddColumnInfo(colInfo);
if (longColumnsIndex.Length > 0)
{
//单独设置需要长宽度的列
string[] longIndexs = longColumnsIndex.Split(',');
if (longIndexs.Length > 0)
{
//遍历数组，设置每一列的宽度
for (int i = 0; i < longIndexs.Length; i++)
{
ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
colInfo1.ColumnIndexStart = ushort.Parse(longIndexs[i]);
colInfo1.ColumnIndexEnd = ushort.Parse(longIndexs[i]);
colInfo1.Width = 36 * 256;
sheet.AddColumnInfo(colInfo1);
}
}
}
//设置样式
XF xf = xls.NewXF();
xf.UseProtection = false;
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col.ColumnName, xf);
}
sheet.Rows[1].RowHeight = 24 * 20;
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
//设置行高
sheet.Rows[(ushort)rowIndex].RowHeight = 24 * 20;
}
xls.Send(XlsDocument.SendMethods.Attachment);
}
private CellStyle CreateStyleFromXF(XF xf)
{
//throw new NotImplementedException();
return(new CellStyle());
}
//private void ResultExport(object sender, RoutedEventArgs e)
//{
// if (dt1.Rows.Count == 0)
// {
// MessageBox.Show("没有数据！");
// }
// else
// {
// string filename = "";
// //Microsoft.Win32.OpenFileDialog dialogOpenFile = new Microsoft.Win32.OpenFileDialog();
// //dialogOpenFile.ShowDialog();
// Microsoft.Win32.SaveFileDialog dialogSaveFile = new Microsoft.Win32.SaveFileDialog();
// dialogSaveFile.Filter = "Excel文件（*.xls）|*.xls";
// if (dialogSaveFile.ShowDialog() == true)
// {
// filename = dialogSaveFile.FileName;
// }
// else
// {
// return;
// }
// ExcelWriterOC excel = new ExcelWriterOC();
// excel.ExcelWriter(filename);
// excel.BeginWrite();
// excel.WriteString(0, 0, "ID");
// excel.WriteString(0, 1, "标题");
// excel.WriteString(0, 2, "链接");
// excel.WriteString(0, 3, "收录");
// excel.WriteString(0, 4, "时间");
// short excelline = 1;
// foreach (DataRow dr in dt1.Rows)
// {
// excel.WriteString(excelline, 0, dr[0].ToString());
// excel.WriteString(excelline, 1, dr[1].ToString());
// excel.WriteString(excelline, 2, dr[2].ToString());
// excel.WriteString(excelline, 3, dr[3].ToString());
// excel.WriteString(excelline, 4, dr[4].ToString());
// excelline++;
// }
// excel.EndWrite();
// }
//}
private void ResultExport(object sender, RoutedEventArgs e)
{
if (dt1.Rows.Count == 0)
{
MessageBox.Show("没有数据！");
}
else
{
string filename = "";
Microsoft.Win32.SaveFileDialog dialogSaveFile = new Microsoft.Win32.SaveFileDialog();
dialogSaveFile.Filter = "Excel文件（*.xls）|*.xls";
if (dialogSaveFile.ShowDialog() == true)
{
filename = dialogSaveFile.FileName;
}
else
{
return;
}
string extension = System.IO.Path.GetExtension(filename);
DataTable exportdt = dt1;
if (extension == ".xls")
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("百度收录");
//单元格格式
XF xf = doc.NewXF();
xf.Format = StandardFormats.Date_Time;
//列格式
ColumnInfo colid = new ColumnInfo(doc, sheet);
colid.ColumnIndexStart = 0;
colid.ColumnIndexEnd = 0;
colid.Width = 8 * 256;
sheet.AddColumnInfo(colid);
ColumnInfo coltitle = new ColumnInfo(doc, sheet);
coltitle.ColumnIndexStart = 1;
coltitle.ColumnIndexEnd = 1;
coltitle.Width = 70 * 256;
sheet.AddColumnInfo(coltitle);
ColumnInfo colurl = new ColumnInfo(doc, sheet);
colurl.ColumnIndexStart = 2;
colurl.ColumnIndexEnd = 2;
colurl.Width = 70 * 256;
sheet.AddColumnInfo(colurl);
ColumnInfo colinclude = new ColumnInfo(doc, sheet);
colinclude.ColumnIndexStart = 3;
colinclude.ColumnIndexEnd = 3;
colinclude.Width = 8 * 256;
sheet.AddColumnInfo(colinclude);
ColumnInfo coltime = new ColumnInfo(doc, sheet);
coltime.ColumnIndexStart = 4;
coltime.ColumnIndexEnd = 4;
coltime.Width = 18 * 256;
sheet.AddColumnInfo(coltime);
Worksheet ws = sheet;
for (int row = 0; row < dt1.Rows.Count; row++)
{
for (int column = 0; column < dt1.Columns.Count; column++)
{
ws.Cells.Add(row + 1, column + 1, dt1.Rows[row][column].ToString());
}
}
doc.Save();
}
}
}
/// <summary>
/// 导出excel通用方法
/// </summary>
/// <param name="myData">导出的数据</param>
private void ExportExcel(List <ExcelData> myData)
{
//列名：
string[] colName = new string[] { "审核结果", "流水号", "下单日期", "业务类型", "单据类型", "单据编号", "客户名称", "办事处", "营业员",
"制单人", "变更项目", "附注", "明细类型", "单据行号", "物料名称", "规格型号", "数量", "成交价", "含税单价",
"成本价", "成交金额", "交货日期", "币别", "K3关联数量" };
//設置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 < colName.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(16 * 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;
//"审核结果","流水号","下单日期","业务类型","单据类型","单据编号","客户名称","办事处","营业员",
//"制单人","变更项目","附注","明细类型","单据行号","物料名称","规格型号","数量","成交价","含税单价",
//"成本价","成交金额","交货日期","币别","K3关联数量"
cells.Add(++rowIndex, colIndex, d.auditStatus);
cells.Add(rowIndex, ++colIndex, d.h.sys_no);
cells.Add(rowIndex, ++colIndex, d.h.bill_date.ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.h.tran_type);
cells.Add(rowIndex, ++colIndex, d.h.bill_type);
cells.Add(rowIndex, ++colIndex, d.h.bill_no);
cells.Add(rowIndex, ++colIndex, d.h.customer_name);
cells.Add(rowIndex, ++colIndex, d.h.agency_name);
cells.Add(rowIndex, ++colIndex, d.h.clerk_name);
cells.Add(rowIndex, ++colIndex, d.h.applier_name);
cells.Add(rowIndex, ++colIndex, d.h.change_project);
cells.Add(rowIndex, ++colIndex, d.h.comment);
cells.Add(rowIndex, ++colIndex, d.e.detail_type == "before" ? "原单" : "改单");
cells.Add(rowIndex, ++colIndex, d.e.entry_no);
cells.Add(rowIndex, ++colIndex, d.e.item_name);
cells.Add(rowIndex, ++colIndex, d.e.item_model);
cells.Add(rowIndex, ++colIndex, d.e.qty);
cells.Add(rowIndex, ++colIndex, d.e.deal_price);
cells.Add(rowIndex, ++colIndex, d.e.tax_price);
cells.Add(rowIndex, ++colIndex, d.e.cost);
cells.Add(rowIndex, ++colIndex, d.e.deal_sum);
cells.Add(rowIndex, ++colIndex, d.e.fetch_date);
cells.Add(rowIndex, ++colIndex, d.e.currency_name);
cells.Add(rowIndex, ++colIndex, d.e.relate_qty);
}
xls.Send();
}
private static basic_event find_event(XF.Event e)
{
return _eventbank[(int)e];
}
public static void ReadFromDataTable(ref DataTable dt, ref Worksheet ws, int StartRow, int StartCol, XF xf)
{
int row = StartRow;
int col = StartCol;
foreach (DataRow dataRow in dt.Rows)
{
col = StartCol;
foreach (object dataItem in dataRow.ItemArray)
{
object value = dataItem;
if (dataItem == DBNull.Value)
{
value = null;
}
if (dataRow.Table.Columns[col - StartCol].DataType == typeof(byte[]))
{
value = string.Format("[ByteArray({0})]", ((byte[])value).Length);
}
ws.Cells.Add(row, col++, value, xf);
}
row++;
}
}
internal static void unregister(XF.Event which_event, basic_event method)
{
_eventbank[(int)which_event] -= method;
}
///// <summary>
///// 绑定数据库生成XLS报表
///// </summary>
///// <param name="ds">获取DataSet数据集</param>
///// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = System.Web.HttpUtility.UrlEncode(xlsName, System.Text.Encoding.UTF8);
int rowIndex = 5;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 18 * 256;
sheet.AddColumnInfo(colInfo);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 10);
MergeRegion(ref sheet, xf, "姓名", 2, 5, 1, 1);
MergeRegion(ref sheet, xf, "日常工作（权重30分）", 2, 2, 2, 4);
MergeRegion(ref sheet, xf, "KPI考核（权重60分）", 2, 2, 5, 8);
MergeRegion(ref sheet, xf, "胜任度(权重10分)", 2, 2, 9, 9);
MergeRegion(ref sheet, xf, "得分", 2, 5, 10, 10);
Cell cell1 = cells.Add(3, 2, "A", xf);
Cell cell2 = cells.Add(3, 3, "B", xf);
Cell cell3 = cells.Add(3, 4, "C", xf);
Cell cell4 = cells.Add(3, 5, "D", xf);
Cell cell5 = cells.Add(3, 6, "E", xf);
MergeRegion(ref sheet, xf, "F", 3, 3, 7, 8);
Cell cell6 = cells.Add(3, 9, "G", xf);
Cell cell7 = cells.Add(4, 2, "劳动纪律", xf);
Cell cell8 = cells.Add(4, 3, "信息报道", xf);
Cell cell9 = cells.Add(4, 4, "基础报表", xf);
Cell cell10 = cells.Add(4, 5, "网运指标", xf);
Cell cell11 = cells.Add(4, 6, "工作效率", xf);
MergeRegion(ref sheet, xf, "其它加扣分", 4, 4, 7, 8);
Cell cell12 = cells.Add(4, 9, "贡献度胜任度职业道德", xf);
Cell cell13 = cells.Add(5, 2, "迟到或早退一次扣1分、事假每天扣2分，病假每天扣1分执行。无故旷工半天，扣5分。无故旷工一天，扣10分，扣分随天数增加而增加.", xf);
Cell cell14 = cells.Add(5, 3, "每人每月至少上报信息1篇，每差一篇扣5分。凡信息被市公司采用的加2分，被省公司采用的加5分。", xf);
Cell cell15 = cells.Add(5, 4, "未按时出规定报表一次扣2分，被省公司通报一次扣5分。", xf);
Cell cell16 = cells.Add(5, 5, "本岗位挂靠指标完成情况", xf);
Cell cell17 = cells.Add(5, 6, "不能按时按要求完成本岗位工作任务（周工作计划内容、日常工作内容），效率低的每次每项扣2分", xf);
MergeRegion(ref sheet, xf, "全省有通报的各项工作中，排名前三加5分，排名前6加3分，低于全省平均水平扣2分，排名12名之后扣3分，排名后三扣5分", 5, 5, 7, 8);
Cell cell18 = cells.Add(5, 9, "部门员工得分=部门正职得分*50+部门副职得分*30%+员工互评得分*20%; 部门副职得分=员工打分均值", xf);
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
}
xls.Send();
Response.Flush();
Response.End();
}
private CellStyle CreateStyleFromXF(XF xf)
{
return(new CellStyle());
}
/// <summary>
/// 绑定数据库生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = Server.UrlEncode(xlsName);
int rowIndex = 2;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//设置月份
string[] colums1 = { "被盗单位", "1月", "", "2月", "", "3月", "", "4月", "", "5月", "", "6月", "", "7月", "", "8月", "", "9月", "", "10月", "", "11月", "", "12月", "" };
foreach (string col1 in colums1)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col1, xf);
}
//设置次数和金额
for (int i = 1; i < 26; i++)
{
Cell cell;
if (i == 1)
{
cell = cells.Add(2, i, "", xf);
}
else if (i % 2 == 1)
{
cell = cells.Add(2, i, "金额", xf);
}
else
{
cell = cells.Add(2, i, "次数", xf);
}
}
//合并单元格
sheet.Cells.Merge(1, 2, 1, 1);
for (int i = 1; i < 13; i++)
{
MergeRegion(ref sheet, xf, i.ToString() + "月", 1, 1, i * 2, i * 2 + 1);
}
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
}
xls.Send();
Response.Flush();
Response.End();
}
/// <summary>
/// 绑定数据库生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = Server.UrlEncode(xlsName);
int rowIndex = 1;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置列样式
ColumnInfo cl = new ColumnInfo(xls, sheet);
cl.Width = 20 * 256;
cl.ColumnIndexStart = 1;
cl.ColumnIndexEnd = 2;
sheet.AddColumnInfo(cl);
ColumnInfo cl1 = new ColumnInfo(xls, sheet);
cl1.Width = 20 * 256;
cl1.ColumnIndexStart = 7;
cl1.ColumnIndexEnd = 7;
sheet.AddColumnInfo(cl1);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col.ColumnName, xf);
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
}
xls.Send();
Response.Flush();
Response.End();
}
private void btnTest_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();
//添加文件属性
xls.SummaryInformation.Author = "伍华聪"; //作者
xls.SummaryInformation.Subject = "测试Myxls的Excel文件生成";
xls.DocumentSummaryInformation.Company = "http://www.iqidi.com 广州爱启迪技术有限公司";
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");//状态栏标题名称
Cells cells = sheet.Cells;
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.Pattern = 1;
xf.PatternColor = Colors.Default30;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
xf.Font.Height = 11 * 20;
xf.Font.ColorIndex = 1;
cells.Add(1, 1, "姓名", xf);
cells.Add(1, 2, "年龄", xf);
cells.Add(1, 3, "Email邮箱", xf);
cells.Add(1, 4, "描述", xf);
for (int i = 0; i < 500; i++)
{
Cell nameCell = cells.Add(i + 2, 1, RandomChinese.GetRandomChinese2(3));
nameCell.Font.FontFamily = FontFamilies.Roman; //字体
nameCell.Font.Bold = true; //字体为粗体
cells.Add(i + 2, 2, new Random().Next(20, 50));
cells.Add(i + 2, 3, "*****@*****.**");
cells.Add(i + 2, 4, RandomChinese.GetRandomChinese2(50));
Thread.Sleep(10);
}
string saveFile = FileDialogHelper.SaveExcel("wuhuacong.xls", "C:\\");
if (!string.IsNullOrEmpty(saveFile))
{
xls.FileName = saveFile;
xls.Save(true);
if (MessageUtil.ShowYesNoAndTips("保存成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(saveFile);
}
}
}
public static XF.Button ContentLayout(this XF.Button self, XF.Button.ButtonContentLayout value)
{
self.ContentLayout = value;
return self;
}
public override byte[] RetByte(List <string[]> list, int operationid, string title)
{
string[] titles = { "币种", "顺序号", " 付方开户行名", "付方卡号", "缴费编号", "付方户名"
, "收款单位开户行名", "省份", "收款方地区名", "收款方地区代码", "收款账号","协议编号",
"收款单位名称", "付款金额", "用途", "备注", "付款账户短信通知手机号码",
"自定义序号", "是否工行账号", "付方账号开户行行号" };
XlsDocument xls = new XlsDocument();
xls.FileName = title;
Worksheet wks = xls.Workbook.Worksheets.Add("Sheet1");
Cells cell = wks.Cells;
XF xf = ExportExtend.SetCommonStyle(xls);
ExportExtend.SetColumnWidth(xls, wks, 2, 15);
ExportExtend.SetColumnWidth(xls, wks, 3, 23);
ExportExtend.SetColumnWidth(xls, wks, 4, 22);
ExportExtend.SetColumnWidth(xls, wks, 5, 15);
ExportExtend.SetColumnWidth(xls, wks, 6, 18);
ExportExtend.SetColumnWidth(xls, wks, 7, 10);
ExportExtend.SetColumnWidth(xls, wks, 8, 15);
ExportExtend.SetColumnWidth(xls, wks, 9, 15);
ExportExtend.SetColumnWidth(xls, wks, 10, 22);
ExportExtend.SetColumnWidth(xls, wks, 11, 22);
ExportExtend.SetColumnWidth(xls, wks, 12, 25);
ExportExtend.SetColumnWidth(xls, wks, 13, 15);
ExportExtend.SetColumnWidth(xls, wks, 14, 20);
ExportExtend.SetColumnWidth(xls, wks, 16, 25);
ExportExtend.SetColumnWidth(xls, wks, 17, 15);
ExportExtend.SetColumnWidth(xls, wks, 18, 15);
ExportExtend.SetColumnWidth(xls, wks, 19, 20);
for (int i = 1; i <= list.Count; i++)
{
if (i == 1)
{
for (int j = 1; j <= list[i - 1].Length; j++)
{
Cell cel = cell.Add(i, j, titles[j - 1], xf);
}
}
for (int j = 1; j <= list[i - 1].Length; j++)
{
if (j == 2)
{
Cell cel = cell.Add(i + 1, 2, i, xf);
}
else if (j == 10)
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim().ToInt(), xf);
}
else if (j == 14)
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim().ToDecimal(), xf);
}
else
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim(), xf);
}
}
}
return(xls.Bytes.ByteArray);
}
// 负责生成一个Sheet的主方法，调用辅助 protected virtual方法来完成，如果Override该方法则可以完全控制生成逻辑
protected virtual void BuildSheet(Worksheet worksheet, int sheetIndex, DataTable data,
List <ColumnData> columnSetting, List <TextInfo> textInfoList)
{
columnSetting = ProcessColumnDataList(data, columnSetting);
// set header with
SetSheetColumnsWith(worksheet, sheetIndex, columnSetting, data);
int headerRowIndex = 0;
//if (textInfo != null)
//{
// if (textInfo.Title != null && textInfo.Title.Trim().Length > 0)
// {
// MergeRegion(worksheet, GetSheetTitleXF(), textInfo.Title.Trim(), headerRowIndex, 1, headerRowIndex, columnSetting.Count);
// headerRowIndex = headerRowIndex + 1;
// }
// if (textInfo.Memo != null && textInfo.Memo.Trim().Length > 0)
// {
// MergeRegion(worksheet, GetSheetMemoXF(), textInfo.Memo.Trim(), headerRowIndex, 1, headerRowIndex, columnSetting.Count);
// headerRowIndex = headerRowIndex + 1;
// }
//}
XF sxf = GetDataHeaderXF(sheetIndex, 1, HorizontalAlignments.Left, VerticalAlignments.Centered, false);
headerRowIndex = 5;
if (String.IsNullOrWhiteSpace(FileTitle))
{
FileTitle = "未能获得报表名";
}
for (int r = 1; r <= 4; r++)
{
worksheet.Cells.Merge(r, r, 1, columnSetting.Count);
}
worksheet.Cells.Add(1, 1, "报表名字：" + FileTitle, sxf);
worksheet.Cells.Add(2, 1, "公司名称：浙江泰隆商业银行电商平台", sxf);
worksheet.Cells.Add(3, 1, "制定时间：" + DateTime.Now.ToString("G"), sxf);
worksheet.Cells.Add(4, 1, "打印时间：" + DateTime.Now.ToString("G"), sxf);
if (textInfoList != null && textInfoList.Count > 0)
{
headerRowIndex = textInfoList.Count + headerRowIndex;
for (int r = 1; r <= textInfoList.Count; r++)
{
worksheet.Cells.Merge(r + 4, r + 4, 1, columnSetting.Count);
worksheet.Cells.Add(r + 4, 1, textInfoList[r - 1].Title + " " + textInfoList[r - 1].Memo, sxf);
}
}
// add header row to excel sheet
int excelColIndex = 0;
bool hasFooter = false;
foreach (ColumnData col in columnSetting)
{
excelColIndex++;
XF xf = GetDataHeaderXF(sheetIndex, excelColIndex,
ConvertAlignments(col.HorizontalAlignment),
ConvertAlignments(col.VerticalAlignment),
col.HasBorder);
worksheet.Cells.Add(headerRowIndex, excelColIndex, col.Title, xf);
if (col.FooterType != FooterType.None)
{
hasFooter = true;
}
}
decimal[] rstContainer = new decimal[columnSetting.Count];
// add content row to excel sheet
int j = 0;
for (j = 0; j < data.Rows.Count; j++)
{
if (j >= MaxDataTableRowCountLimit)
{
break;
}
for (int c = 1; c <= columnSetting.Count; c++)
{
ColumnData cols = columnSetting[c - 1];
object tmp = data.Rows[j][cols.FieldIndex.Value];
if (cols.FooterType != FooterType.None)
{
rstContainer[c - 1] = rstContainer[c - 1] + Convert.ToDecimal(tmp);
}
object d = FormatCellValue(tmp, cols.ValueFormat, sheetIndex, j + 1, c);
XF xf = GetDataCellXF(sheetIndex, tmp.GetType(), j + 1, c,
ConvertAlignments(cols.HorizontalAlignment),
ConvertAlignments(cols.VerticalAlignment),
cols.HasBorder);
worksheet.Cells.Add(j + headerRowIndex + 1
, c, d, xf);
}
}
if (hasFooter)
{
for (int c = 1; c <= columnSetting.Count; c++)
{
ColumnData cols = columnSetting[c - 1];
object x;
if (cols.FooterType == FooterType.None)
{
x = string.Empty;
}
else if (cols.FooterType == FooterType.Sum)
{
x = "总计：" + FormatCellValue(rstContainer[c - 1], cols.ValueFormat, sheetIndex, j + 1, c);
}
else
{
x = "平均：" + FormatCellValue(rstContainer[c - 1] / (decimal)j, cols.ValueFormat, sheetIndex, j + 1, c);
}
XF xf = GetDataCellXF(sheetIndex, x.GetType(), j + 1, c,
ConvertAlignments(cols.HorizontalAlignment),
ConvertAlignments(cols.VerticalAlignment),
cols.HasBorder);
worksheet.Cells.Add(j + headerRowIndex + 1
, c, x, xf);
}
}
}
/// <summary>
/// 导出申请单excel，包括我的申请和我的审批
/// </summary>
/// <param name="fc"></param>
public void ExporDRData(string queryJson, string pageType)
{
SearchMyApplyParams p = JsonConvert.DeserializeObject <SearchMyApplyParams>(queryJson);
p.account = currentAccount;
p.userId = currentUser.userId;
p.userName = currentUser.userName;
var result = new List <CheckApplyListModel>();
string fileName = "";
if (pageType.Equals("apply"))
{
fileName = "我的送货申请_";
result = new DRSv().SearchMyApplyList(p, canCheckAll).ToList();
}
else if (pageType.Equals("audit"))
{
fileName = "审核送货申请_";
result = new DRSv().SearchMyAuditList(p, canCheckAll).ToList();
}
ushort[] colWidth = new ushort[] { 16, 18, 12, 16, 10, 24,
32, 16, 16, 12, 18, 18, 16,
16, 16, 16, 16, 18, 16, 18, 16 };
string[] colName = new string[] { "发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
"规格型号", "订单数量", "申请数量", "单位", "物料编码", "备注", "订单类型",
"采购方式", "订料员", "采购员", "贸易类型", "PR单号", "采购日期", "入库单号", "入库日期" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = fileName + DateTime.Now.ToString("MMddHHmmss");
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 result)
{
colIndex = 1;
//"发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
//"规格型号", "订单数量", "申请数量", "单位", "物料编码","订单类型",
//"采购方式","订料员","采购员","贸易类型","PR单号","采购日期","入库单号","入库日期"
cells.Add(++rowIndex, colIndex, ((DateTime)d.sendDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.billNo);
cells.Add(rowIndex, ++colIndex, d.pStatus);
cells.Add(rowIndex, ++colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.poQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.sendQty));
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.comment);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.inStockBillNo);
cells.Add(rowIndex, ++colIndex, d.inStockBillDate == null?"":((DateTime)d.inStockBillDate).ToString("yyyy-MM-dd"));
}
xls.Send();
WLog("导出申请单Excel", fileName + ":" + queryJson);
}
protected virtual void SetXFForDataCell(int sheetIndex, int excelRowIndex, int excelColumnIndex, XF xf)
{
}
public void ExportPOData(string queryJson)
{
GetK3POParams p = JsonConvert.DeserializeObject <GetK3POParams>(queryJson);
p.poNumbers = p.poNumbers == null ? "" : p.poNumbers.Trim();//去掉前后空格
p.itemInfo = p.itemInfo == null ? "" : p.itemInfo.Trim();
p.account = currentAccount;
p.userId = currentUser.userId;
p.userNumber = currentUser.userName;
p.k3HasAudit = true;
List <K3POs> result;
List <decimal> qtyList;
try {
result = new DRSv().GetPOs(p).OrderBy(po => po.poDate).Take(1000).ToList();
//一次性关联1000条到数据库查询会导致内存溢出，所以大于500条的，分2次获取
var infos = result.Take(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList = new DRSv().GetPOTransitQty(infos);
if (result.Count() > 500)
{
infos = result.Skip(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList.AddRange(new DRSv().GetPOTransitQty(infos));
}
}
catch {
return;
}
ushort[] colWidth = new ushort[] { 18, 10, 20, 24, 32, 16,
16, 16, 16, 12, 24, 12, 16,
12, 12, 16, 12, 12, 16 };
string[] colName = new string[] { "订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
"入库数量", "申请数量", "可申请数量", "订料员", "申购部门", "单位", "物料编码",
"订单类型", "采购方式", "订单日期", "贸易类型", "币别", "采购员" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = "订单明细表_" + DateTime.Now.ToString("MMddHHmmss");
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);
}
for (var i = 0; i < result.Count(); i++)
{
var d = result[i];
var q = qtyList[i];
colIndex = 1;
//"订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
//"入库数量", "申请数量", "可申请数量", "订料员", "申购部门","单位","物料编码",
//"订单类型","采购方式","订单日期","贸易类型","币别","采购员"
cells.Add(++rowIndex, colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.realteQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", q));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty - d.realteQty - q));
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.departmentName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
}
xls.Send();
WLog("导出K3订单Excel", "行数：" + result.Count() + ";" + queryJson);
//return "已成功导出行数："+result.Count();
}
/// <summary>
/// DataSet转Excel（SheetName使用对应DataTable的TableName）。
/// 不支持在已存在的Excel文件中添加新的sheet（如果文件已存在，则会覆盖）。
/// </summary>
/// <param name="ds">待写入Excel的DataSet对象</param>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="isColumnWritten">DataTable的列名是否要写入</param>
/// <param name="cellStyle">单元格格式。如果为null，则不设置单元格格式</param>
public static void ToExcel(DataSet ds, string excelFilePath, bool isColumnWritten, CellStyle cellStyle)
{
if (string.IsNullOrEmpty(excelFilePath))
{
throw new ArgumentException(ErrorFilePath);
}
if (ds == null || ds.Tables.Count <= 0)
{
return;
}
//创建Excel文档
XlsDocument xls = new XlsDocument();
xls.SummaryInformation.Author = Author;
xls.SummaryInformation.Subject = Subject;
xls.SummaryInformation.Comments = Comments;
xls.DocumentSummaryInformation.Company = Company;
foreach (DataTable dt in ds.Tables)
{
if (dt == null || dt.Rows.Count <= 0)
{
continue;
}
int cellRow = 1;//从第几行开始写数据
string sheetName = GetValidSheetName(dt.TableName);
if (string.IsNullOrEmpty(sheetName) || xls.Workbook.Worksheets.Any(worksheet => worksheet.Name == sheetName))
{
sheetName = string.Format("Sheet{0}", xls.Workbook.Worksheets.Count + 1);
}
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
sheet.SheetType = WorksheetTypes.Worksheet;
Cells cells = sheet.Cells;
//写标题
if (isColumnWritten)
{
#region 设置标题格式
XF xfDataHead = xls.NewXF();
if (cellStyle != null)
{
xfDataHead.HorizontalAlignment = cellStyle.TitleFontCenter ? HorizontalAlignments.Centered : HorizontalAlignments.Left;
xfDataHead.Font.FontName = cellStyle.TitleFontName;
xfDataHead.Font.Bold = cellStyle.TitleFontBold;
xfDataHead.Font.Height = cellStyle.TitleFontSize;
if (cellStyle.Border)
{
xfDataHead.UseBorder = true;
xfDataHead.TopLineStyle = 1;
xfDataHead.BottomLineStyle = 1;
xfDataHead.LeftLineStyle = 1;
xfDataHead.RightLineStyle = 1;
}
xfDataHead.CellLocked = false;
xfDataHead.UseProtection = false;
xfDataHead.UseNumber = true;
xfDataHead.UseMisc = true;
}
#endregion
for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++)
{
if (cellStyle != null)
{
cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption, xfDataHead);
}
else
{
cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption);
}
}
cellRow++;
}
#region 设置内容格式
XF xfDataContent = xls.NewXF();
if (cellStyle != null)
{
xfDataContent.HorizontalAlignment = HorizontalAlignments.Left;
xfDataContent.Font.FontName = cellStyle.ContentFontName;
xfDataContent.Font.Height = cellStyle.ContentFontSize;
if (cellStyle.Border)
{
xfDataContent.UseBorder = true;
xfDataContent.TopLineStyle = 1;
xfDataContent.BottomLineStyle = 1;
xfDataContent.LeftLineStyle = 1;
xfDataContent.RightLineStyle = 1;
}
xfDataContent.CellLocked = false;
xfDataContent.UseProtection = false;
xfDataContent.UseNumber = true;
xfDataContent.UseMisc = true;
}
#endregion
//写内容
foreach (DataRow dr in dt.Rows)
{
for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++)
{
if (cellStyle != null)
{
ColumnInfo colInfo = new ColumnInfo(xls, sheet)
{
ColumnIndexStart = (ushort)(cellColumn - 1),
ColumnIndexEnd = (ushort)(cellColumn - 1),
Width = cellStyle.ColumnWidth
};
sheet.AddColumnInfo(colInfo);
}
object obj = dr[cellColumn - 1] != DBNull.Value ? (dt.Columns[cellColumn - 1].DataType == typeof(DateTime)
? Convert.ToDateTime(dr[cellColumn - 1]).ToString("yyyy-MM-dd HH:mm:ss")
: dr[cellColumn - 1])
: string.Empty;
if (cellStyle != null)
{
cells.Add(cellRow, cellColumn, obj, xfDataContent);
}
else
{
cells.Add(cellRow, cellColumn, obj);
}
}
cellRow++;
}
}
xls.FileName = Path.GetFileName(excelFilePath);
xls.Save(Path.GetDirectoryName(excelFilePath), true);
//xls.Send(XlsDocument.SendMethods.Attachment);//浏览器保存文件
}
//数据的导入和导出
protected void ExportExcel(DataTable dt, string filename)
{
XlsDocument xls = new XlsDocument(); //新建一个xls文档
xls.FileName = filename; //设定文件名
string sheetName = "动车票价和定员表";
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格（cell）集合
////设置列的宽度
ColumnInfo colInfo = new ColumnInfo(xls, sheet);//生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = 0; //起始列为第一列,ColumnIndexStart是从0开始
colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
//列样式
XF cellXFColumnText = xls.NewXF();
cellXFColumnText.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXFColumnText.Font.FontName = "宋体"; //设定字体
cellXFColumnText.Font.Height = 10 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXFColumnText.Pattern = 1; //设定单元格填充风格。如果设定为0，则是纯色填充
cellXFColumnText.PatternBackgroundColor = Colors.Grey; //填充的底色
cellXFColumnText.PatternColor = Colors.Grey; //设定填充线条的颜色
cellXFColumnText.BottomLineStyle = 1; //设定边框底线为粗线
cellXFColumnText.BottomLineColor = Colors.Grey;
cellXFColumnText.TopLineStyle = 1; //设定边框底线为粗线
cellXFColumnText.TopLineColor = Colors.Grey;
cellXFColumnText.LeftLineStyle = 1;
cellXFColumnText.LeftLineColor = Colors.Grey;
cellXFColumnText.RightLineStyle = 1;
cellXFColumnText.RightLineColor = Colors.Grey;
cells.Add(1, 1, "序号", cellXFColumnText); //设定第一行，第一列单元格的值
cells.Add(1, 2, "动车类型", cellXFColumnText); //设定第一行，第二列单元格的值
cells.Add(1, 3, "动车类别", cellXFColumnText); //设定第一行，第三列单元格的值
cells.Add(1, 4, "一等座基本票价", cellXFColumnText); //设定第一行，第四列单元格的值
cells.Add(1, 5, "一等座定员", cellXFColumnText); //设定第一行，第五列单元格的值
cells.Add(1, 6, "二等座基本票价", cellXFColumnText);
cells.Add(1, 7, "二等座定员", cellXFColumnText);
cells.Add(1, 8, "动卧上铺基本票价", cellXFColumnText);
cells.Add(1, 9, "动卧下铺基本票价", cellXFColumnText);
cells.Add(1, 10, "动卧定员", cellXFColumnText);
cells.Add(1, 11, "商务座基本票价", cellXFColumnText);
cells.Add(1, 12, "商务座定员", cellXFColumnText);
//列值样式
XF cellXFColumnValue = xls.NewXF();
cellXFColumnValue.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXFColumnValue.Font.FontName = "宋体"; //设定字体
cellXFColumnValue.Font.Height = 10 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXFColumnValue.UseBorder = true; //使用边框
cellXFColumnValue.BottomLineStyle = 1; //设定边框底线为粗线
cellXFColumnValue.BottomLineColor = Colors.Grey;
cellXFColumnValue.TopLineStyle = 1; //设定边框底线为粗线
cellXFColumnValue.TopLineColor = Colors.Grey;
cellXFColumnValue.LeftLineStyle = 1;
cellXFColumnValue.LeftLineColor = Colors.Grey;
cellXFColumnValue.RightLineStyle = 1;
cellXFColumnValue.RightLineColor = Colors.Grey;
int currentRow = 2;
foreach (DataRow dr in dt.Rows)
{
cells.Add(currentRow, 1, Convert.ToString(dr["id"]), cellXFColumnValue); //动车类型
cells.Add(currentRow, 2, Convert.ToString(dr["HIGHTRAINTYPE"]), cellXFColumnValue); //动车类型
cells.Add(currentRow, 3, Convert.ToString(dr["MILETYPE"]), cellXFColumnValue); //里程
cells.Add(currentRow, 4, Convert.ToString(dr["RATE1"]), cellXFColumnValue); //一等座票价
cells.Add(currentRow, 5, Convert.ToString(dr["PCOUNT1"]), cellXFColumnValue);
cells.Add(currentRow, 6, Convert.ToString(dr["RATE2"]), cellXFColumnValue);
cells.Add(currentRow, 7, Convert.ToString(dr["PCOUNT2"]), cellXFColumnValue);
cells.Add(currentRow, 8, Convert.ToString(dr["RATE3"]), cellXFColumnValue);
cells.Add(currentRow, 9, Convert.ToString(dr["RATE31"]), cellXFColumnValue);
cells.Add(currentRow, 10, Convert.ToString(dr["PCOUNT3"]), cellXFColumnValue);
cells.Add(currentRow, 11, Convert.ToString(dr["RATE4"]), cellXFColumnValue);
cells.Add(currentRow, 12, Convert.ToString(dr["PCOUNT4"]), cellXFColumnValue);
currentRow++;
}
xls.Send(XlsDocument.SendMethods.Inline);
}
public static void SetXF(ref Worksheet ws, ushort LeftRowIndex, ushort LeftColIndex, ushort RightRowIndex, ushort RightColIndex, XF xf)
{
Cell c;
for (ushort i = LeftRowIndex; i <= RightRowIndex; i++)
{
for (ushort n = LeftColIndex; n <= RightColIndex; n++)
{
if (ws.Rows[i].CellExists(n))
{
c = ws.Rows[i].CellAtCol(n);
}
else
{
c = ws.Cells.Add(i, n, "", xf);
}
//c.Style = xf.Style;
//c.Format = xf.Format;
}
}
}
public void CreateExcel(string fileName, DataTable DataSource)
{
XlsDocument xls = new XlsDocument();
#region 列标题样式
XF columnTitleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
columnTitleXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
columnTitleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
columnTitleXF.UseBorder = true; // 使用边框
columnTitleXF.TopLineStyle = 1; // 上边框样式
columnTitleXF.TopLineColor = Colors.Black; // 上边框颜色
columnTitleXF.BottomLineStyle = 1; // 下边框样式
columnTitleXF.BottomLineColor = Colors.Black; // 下边框颜色
columnTitleXF.LeftLineStyle = 1; // 左边框样式
columnTitleXF.LeftLineColor = Colors.Black; // 左边框颜色
columnTitleXF.RightLineStyle = 1; // 右边框样式
columnTitleXF.RightLineColor = Colors.Black; // 右边框颜色
columnTitleXF.Font.Bold = true; // 是否加楚
#endregion
#region 数据单元格样式
XF dataXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXF.UseBorder = true; // 使用边框
dataXF.TopLineStyle = 1; // 上边框样式
dataXF.TopLineColor = Colors.Black; // 上边框颜色
dataXF.BottomLineStyle = 1; // 下边框样式
dataXF.BottomLineColor = Colors.Black; // 下边框颜色
dataXF.LeftLineStyle = 1; // 左边框样式
dataXF.LeftLineColor = Colors.Black; // 左边框颜色
dataXF.RightLineStyle = 1; // 右边框样式
dataXF.RightLineColor = Colors.Black; // 右边框颜色
dataXF.Font.FontName = "宋体";
dataXF.Font.Height = 12 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXF.TextWrapRight = true; // 自动换行
#endregion
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add("export_1_");
ColumnInfo columnInfo = new ColumnInfo(xls, sheet);
columnInfo.ColumnIndexStart = 0;
columnInfo.ColumnIndexEnd = (ushort)(DataSource.Columns.Count - 1);
columnInfo.Width = 15 * 330;
sheet.AddColumnInfo(columnInfo);
// 开始填充数据到单元格
Cells cells = sheet.Cells;
for (int j = 0; j < DataSource.Columns.Count; j++)
{
cells.Add(1, j + 1, DataSource.Columns[j].ColumnName, columnTitleXF);
}
if (DataSource.Rows.Count == 0)
{
cells.Add(3, 1, "没有可用数据");
return;
}
for (int l = 0; l < DataSource.Rows.Count; l++)
{
for (int j = 0; j < DataSource.Columns.Count; j++)
{
cells.Add(l + 2, j + 1, DataSource.Rows[l][j].ToString(), dataXF);
}
}
int fileI = fileName.LastIndexOf("\\");
xls.FileName = fileName.Substring(fileI + 1, fileName.Length - (fileI + 1));
string path = fileName.Substring(0, fileI + 1);
try
{
xls.Save(path, true);
}
catch (Exception)
{
throw new MyException("未导出成功，文件可能被占用");
}
}
private CellStyle CreateStyleFromXF(XF xf)
{
CellStyle style = new CellStyle();
style.BackColor = SharedResource.ColorPalette[xf.PatternColorIndex];
return style;
}
void btnDown_Click(object sender, EventArgs e)
{
XlsDocument xd = new XlsDocument();
#region 单元格样式
#region 表头
XF xf1 = xd.NewXF(); //为xls生成一个XF实例（XF是cell格式对象）
xf1.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
xf1.Font.Height = 12 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
xf1.UseBorder = true; //使用边框
xf1.BottomLineStyle = 2; //设定边框底线为粗线
xf1.LeftLineStyle = 2; //设定边框左线为粗线
xf1.TopLineStyle = 2; //
xf1.RightLineStyle = 2; //
xf1.Font.Bold = true;
#endregion
#region 表头
XF xf2 = xd.NewXF(); //为xls生成一个XF实例（XF是cell格式对象）
xf2.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
xf2.Font.Height = 10 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
xf2.UseBorder = true; //使用边框
xf2.BottomLineStyle = 2; //设定边框底线为粗线
xf2.LeftLineStyle = 2; //设定边框左线为粗线
xf2.TopLineStyle = 2; //
xf2.RightLineStyle = 2; //
#endregion
#region 表头
XF xf3 = xd.NewXF(); //为xls生成一个XF实例（XF是cell格式对象）
xf3.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
xf3.Font.Height = 16 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
#endregion
#endregion
xd.FileName = HttpUtility.UrlEncode("出库调拨单");
Worksheet ws;
DataTable dt = new DataTable();
int i = 0;
int intRN;
ws = xd.Workbook.Worksheets.Add("出库调拨单");
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
var t = db.StorageOutNotice.SingleOrDefault(a => a.StorageOutNoticeID == _noticeid);
#region 表头
ws.Cells.Add(1, 1, "西南油气分公司物资供应处", xf3);
ws.Cells.Add(2, 1, "物资设备调拨单", xf3);
ws.Cells.Add(3, 1, "项目名称：" + t.ProjectInfo.ProjectName);
ws.Cells.Add(3, 2, null);
ws.Cells.Add(3, 3, null);
ws.Cells.Add(3, 4, null);
ws.Cells.Add(3, 5, "业主单位：" + t.BusinessUnitInfo1.BusinessUnitName);
ws.Cells.Add(3, 6, null);
ws.Cells.Add(3, 7, null);
ws.Cells.Add(3, 8, null);
ws.Cells.Add(3, 9, null);
ws.Cells.Add(3, 10, null);
ws.Cells.Add(3, 11, null);
ws.Cells.Add(3, 12, "日 期：" + DateTime.Now.ToString());
ws.Cells.Add(3, 13, null);
ws.Cells.Add(3, 14, null);
ws.Cells.Add(3, 15, null);
ws.Cells.Add(4, 1, "项目性质：" + t.ProjectInfo.ProjectProperty);
ws.Cells.Add(4, 2, null);
ws.Cells.Add(4, 3, null);
ws.Cells.Add(4, 4, null);
ws.Cells.Add(4, 5, null);
ws.Cells.Add(4, 6, null);
ws.Cells.Add(4, 7, null);
ws.Cells.Add(4, 8, null);
ws.Cells.Add(4, 9, null);
ws.Cells.Add(4, 10, null);
ws.Cells.Add(4, 11, null);
ws.Cells.Add(4, 12, null);
ws.Cells.Add(4, 13, null);
ws.Cells.Add(4, 14, null);
ws.Cells.Add(4, 15, null);
ws.Cells.Add(5, 1, "项目阶段：" + t.ProjectStage);
ws.Cells.Add(5, 2, null);
ws.Cells.Add(5, 3, null);
ws.Cells.Add(5, 4, null);
ws.Cells.Add(5, 5, "施工单位：" + t.BusinessUnitInfo.BusinessUnitName);
ws.Cells.Add(5, 6, null);
ws.Cells.Add(5, 7, null);
ws.Cells.Add(5, 8, null);
ws.Cells.Add(5, 9, null);
ws.Cells.Add(5, 10, null);
ws.Cells.Add(5, 11, null);
ws.Cells.Add(5, 12, "编 号：" + t.StorageOutNoticeCode);
ws.Cells.Add(5, 13, null);
ws.Cells.Add(5, 14, null);
ws.Cells.Add(5, 15, null);
ws.Cells.Add(6, 1, "财务编码", xf1);
ws.Cells.Add(6, 2, "物料编码", xf1);
ws.Cells.Add(6, 3, "物资名称", xf1);
ws.Cells.Add(6, 4, "规格型号", xf1);
ws.Cells.Add(6, 5, "生产厂家", xf1);
ws.Cells.Add(6, 6, "进库日期", xf1);
ws.Cells.Add(6, 7, "预发数", xf1);
ws.Cells.Add(6, 8, "", xf1);
ws.Cells.Add(6, 9, "", xf1);
ws.Cells.Add(6, 10, "实发数量", xf1);
ws.Cells.Add(6, 11, "", xf1);
ws.Cells.Add(6, 12, "", xf1);
ws.Cells.Add(6, 13, "计量单位", xf1);
ws.Cells.Add(6, 14, "单价（元）", xf1);
ws.Cells.Add(6, 15, "金额（元）", xf1);
ws.Cells.Add(7, 1, "", xf1);
ws.Cells.Add(7, 2, "", xf1);
ws.Cells.Add(7, 3, "", xf1);
ws.Cells.Add(7, 4, "", xf1);
ws.Cells.Add(7, 5, "", xf1);
ws.Cells.Add(7, 6, "", xf1);
ws.Cells.Add(7, 7, "根/件/套", xf1);
ws.Cells.Add(7, 8, "米", xf1);
ws.Cells.Add(7, 9, "吨", xf1);
ws.Cells.Add(7, 10, "根/件/套", xf1);
ws.Cells.Add(7, 11, "米", xf1);
ws.Cells.Add(7, 12, "吨", xf1);
ws.Cells.Add(7, 13, "", xf1);
ws.Cells.Add(7, 14, "", xf1);
ws.Cells.Add(7, 15, "", xf1);
ws.AddMergeArea(new MergeArea(1, 1, 1, 15));
ws.AddMergeArea(new MergeArea(2, 2, 1, 15));
ws.AddMergeArea(new MergeArea(6, 7, 1, 1));
ws.AddMergeArea(new MergeArea(6, 7, 2, 2));
ws.AddMergeArea(new MergeArea(6, 7, 3, 3));
ws.AddMergeArea(new MergeArea(6, 7, 4, 4));
ws.AddMergeArea(new MergeArea(6, 7, 5, 5));
ws.AddMergeArea(new MergeArea(6, 7, 6, 6));
ws.AddMergeArea(new MergeArea(6, 6, 7, 9));
ws.AddMergeArea(new MergeArea(6, 6, 10, 12));
ws.AddMergeArea(new MergeArea(6, 7, 13, 13));
ws.AddMergeArea(new MergeArea(6, 7, 14, 14));
ws.AddMergeArea(new MergeArea(6, 7, 15, 15));
#endregion
#region 数据源
#region 绑定主体数据
var souce = (from a in db.StorageOutDetails
join b in db.StorageStocks on a.MaterialID equals b.MaterialID
where a.StorageOutNoticeID == _noticeid
select new
{
a.MaterialInfo.FinanceCode,
mcode = "",
a.MaterialInfo.MaterialName,
a.MaterialInfo.SpecificationModel,
madefr = "",
b.StorageTime,
a.Gentaojian,
a.Metre,
a.Ton,
G = "",
M = "",
T = "",
cunit = "",
uprice = "",
sum = "",
}).Distinct();
dt = ReporterHelper.LinqQueryToDataTable(souce);
if (dt.Rows.Count == 0)
{
return;
}
ReporterHelper.ReadFromDataTable(ref dt, ref ws, 8, 1, xf2);
#endregion
intRN = 8 + dt.Rows.Count;
}
#endregion
#region 表尾
ws.Cells.Add(intRN, 1, "备注：");
ws.Cells.Add(intRN, 2, "");
ws.Cells.Add(intRN, 3, "");
ws.Cells.Add(intRN, 4, "");
ws.Cells.Add(intRN, 5, "");
ws.Cells.Add(intRN, 6, "");
ws.Cells.Add(intRN, 7, "");
ws.Cells.Add(intRN, 8, "");
ws.Cells.Add(intRN, 9, "");
ws.Cells.Add(intRN, 10, "");
ws.Cells.Add(intRN, 11, "");
ws.Cells.Add(intRN, 12, "");
ws.Cells.Add(intRN, 13, "");
ws.Cells.Add(intRN, 14, "");
ws.Cells.Add(intRN, 15, "");
ws.Cells.Add(intRN + 1, 1, "生产技术主管：");
ws.Cells.Add(intRN + 1, 2, "");
ws.Cells.Add(intRN + 1, 3, "");
ws.Cells.Add(intRN + 1, 4, "调度员：");
ws.Cells.Add(intRN + 1, 5, "物资主管：");
ws.Cells.Add(intRN + 1, 6, "");
ws.Cells.Add(intRN + 1, 7, "");
ws.Cells.Add(intRN + 1, 8, "");
ws.Cells.Add(intRN + 1, 9, "");
ws.Cells.Add(intRN + 1, 10, "");
ws.Cells.Add(intRN + 1, 11, "");
ws.Cells.Add(intRN + 1, 12, "");
ws.Cells.Add(intRN + 1, 13, "");
ws.Cells.Add(intRN + 1, 14, "领料：");
ws.Cells.Add(intRN + 1, 15, "");
ws.Cells.Add(intRN + 2, 1, "");
ws.Cells.Add(intRN + 2, 2, "");
ws.Cells.Add(intRN + 2, 3, "");
ws.Cells.Add(intRN + 2, 4, "");
ws.Cells.Add(intRN + 2, 5, "");
ws.Cells.Add(intRN + 2, 6, "");
ws.Cells.Add(intRN + 2, 7, "");
ws.Cells.Add(intRN + 2, 8, "");
ws.Cells.Add(intRN + 2, 9, "");
ws.Cells.Add(intRN + 2, 10, "");
ws.Cells.Add(intRN + 2, 11, "");
ws.Cells.Add(intRN + 2, 12, "");
ws.Cells.Add(intRN + 2, 13, "");
ws.Cells.Add(intRN + 2, 14, "");
ws.Cells.Add(intRN + 2, 15, "");
ws.Cells.Add(intRN + 3, 1, "");
ws.Cells.Add(intRN + 3, 2, "");
ws.Cells.Add(intRN + 3, 3, "");
ws.Cells.Add(intRN + 3, 4, "");
ws.Cells.Add(intRN + 3, 5, "");
ws.Cells.Add(intRN + 3, 6, "");
ws.Cells.Add(intRN + 3, 7, "");
ws.Cells.Add(intRN + 3, 8, "");
ws.Cells.Add(intRN + 3, 9, "");
ws.Cells.Add(intRN + 3, 10, "");
ws.Cells.Add(intRN + 3, 11, "");
ws.Cells.Add(intRN + 3, 12, "");
ws.Cells.Add(intRN + 3, 13, "");
ws.Cells.Add(intRN + 3, 14, "");
ws.Cells.Add(intRN + 3, 15, "");
ws.Cells.Add(intRN + 4, 1, "第一联：生产技术组");
ws.Cells.Add(intRN + 4, 2, "");
ws.Cells.Add(intRN + 4, 3, "");
ws.Cells.Add(intRN + 4, 4, "第二联：");
ws.Cells.Add(intRN + 4, 5, "物资管理员");
ws.Cells.Add(intRN + 4, 6, "");
ws.Cells.Add(intRN + 4, 7, "");
ws.Cells.Add(intRN + 4, 8, "第四联：资产组做帐");
ws.Cells.Add(intRN + 4, 9, "");
ws.Cells.Add(intRN + 4, 10, "");
ws.Cells.Add(intRN + 4, 11, "第四联：资产组做帐");
ws.Cells.Add(intRN + 4, 12, "");
ws.Cells.Add(intRN + 4, 13, "");
ws.Cells.Add(intRN + 4, 14, "");
ws.Cells.Add(intRN + 4, 15, "第五联：领料凭证");
ws.AddMergeArea(new MergeArea(intRN, intRN, 1, 15));
#endregion
xd.Send();
Response.Flush();
Response.End();
}