protected virtual void BuildSheet(XlsDocument xlsDoc, int sheetIndex, List <T> data, IList <ColumnSetting <T> > columnMap, string title)
{
int startDataRowIndex;
Worksheet worksheet = CreateSheet(xlsDoc, sheetIndex, columnMap, title, out startDataRowIndex);
BuildSheetData(xlsDoc, worksheet, startDataRowIndex, data, columnMap);
}
public static OpenXmlPackage.DocumentType DetectOutputType(XlsDocument xls)
{
OpenXmlPackage.DocumentType returnType = OpenXmlPackage.DocumentType.Document;
try
{
//ToDo: Find better way to detect macro type
if (xls.Storage.FullNameOfAllEntries.Contains("\\_VBA_PROJECT_CUR"))
{
if (xls.WorkBookData.Template)
{
returnType = OpenXmlPackage.DocumentType.MacroEnabledTemplate;
}
else
{
returnType = OpenXmlPackage.DocumentType.MacroEnabledDocument;
}
}
else
{
if (xls.WorkBookData.Template)
{
returnType = OpenXmlPackage.DocumentType.Template;
}
else
{
returnType = OpenXmlPackage.DocumentType.Document;
}
}
}
catch (Exception)
{
}
return(returnType);
}
// GET: api/AutoSendMail
public string GetData()
{
string excelName = "报修结果导出" + DateTime.Now.ToString("yyyyMMdd");
string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["ITSMModel"].ToString();
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(connectString);
string filter = string.Empty;
conn.Open();
SqlCommand cmd = conn.CreateCommand(); //创建SqlCommand对象
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select a.BXDate, a.BXEmployee ,a.BXDept, c.Name as BXFaultName,AssetCode, a.Note as BXNote,BXDealNote ,BXDealEmployee, BXDealTime= case a.StatusId when 1 then NULL else a.BXDealTime end, b.Name as SatisfactionName, d.Name as StatusName from RepairApplyBills a "
+ "inner join FaultTypes c on c.Id = a.FaultTypeId "
+ "inner join SatisfactionLevels b on b.Id = a.SatisfactionLevelId "
+ "inner join Status d on d.Id = a.StatusId "
+ "where 1=1 " + filter; //sql语句
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = cmd;
myDataAdapter.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("无可导出数据！");
}
XlsDocument xls = Common.DataTableExportToExcel(ds.Tables[0], excelName, "导出结果");
Common.sendMail("测试附件", "测试附件", "*****@*****.**", "杨勇杰", "E:\\Code\\自主研发\\ESB\\Export\\" + excelName + ".xls");
conn.Close();
return(null);
}
protected virtual Worksheet CreateSheet(XlsDocument xlsDoc, int sheetIndex, IList <ColumnSetting <T> > columnMap, string title, out int startDataRowIndex)
{
Worksheet worksheet = xlsDoc.Workbook.Worksheets.Count > 0 ? xlsDoc.Workbook.Worksheets[0] : xlsDoc.Workbook.Worksheets.Add("Sheet" + sheetIndex.ToString());
int headerRowIndex = 1;
//if (string.IsNullOrWhiteSpace(templateExcelPath))
//{
if (!string.IsNullOrWhiteSpace(title))
{
ExcelFileExporterHelper.MergeRegion(worksheet, ExcelFileExporterHelper.GetSheetTitleXF(xlsDoc), title.Trim(), headerRowIndex, 1, headerRowIndex, columnMap.Count);
headerRowIndex = headerRowIndex + 1;
}
var xf = ExcelFileExporterHelper.GetDataCellXF(xlsDoc);
int excelColIndex = 0;
foreach (var col in columnMap)
{
excelColIndex++;
worksheet.Cells.Add(headerRowIndex, excelColIndex, col.ColumnName, xf);
}
decimal[] rstContainer = new decimal[columnMap.Count];
headerRowIndex = headerRowIndex + 1;
startDataRowIndex = headerRowIndex;
return(worksheet);
}
public async Task <XlsDocument> Export()
{
string excelName = "打印机代码导出" + DateTime.Now.ToString("yyyyMMdd");
string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["ITSMModel"].ToString();
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(connectString);
conn.Open();
SqlCommand cmd = conn.CreateCommand(); //创建SqlCommand对象
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Id, No, Name, Center, case Color when 1 then '有' else '无' end as Color, "
+ "case FiveFloor when 1 then '有' else '无' end as FiveFloor, "
+ "case SixFloor when 1 then '有' else '无' end as SixFloor, "
+ "case SevenFloor when 1 then '有' else '无' end as SevenFloor, "
+ "case EighthFloor when 1 then '有' else '无' end as EighthFloor, "
+ "Note from PrinterPermissions"; //sql语句
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = cmd;
myDataAdapter.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("无可导出数据！");
}
XlsDocument xls = DataTableExportToExcel(ds.Tables[0], excelName, "导出结果");
conn.Close();
return(xls);
}
/*
* //Write Whole Table data
* public void Write(DataTable data)
* {
*
* //Set list separator from This Machine regional Settings
* CsvWriter.columnDelimiter = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
* StreamWriter writer = new StreamWriter(FileName, false, Encoding.GetEncoding("windows-1251"));
* CsvWriter.WriteToStream(writer, data, true, false);
* writer.Close();
* writer = null;
* }
*/
public void Write(DataTable table)
{
using (var file2Save = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write))
{
XlsDocument newDoc = new XlsDocument();
Workbook wbk = newDoc.Workbook;
Worksheet sht = wbk.Worksheets.Add("Result");
for (int i = 0; i < table.Columns.Count; i++)
{
var col = table.Columns[i];
sht.Cells.Add(1, i + 1, col.ColumnName);
}
// заполняем результат работы
for (int i = 0; i < table.Rows.Count; i++)
{
var row = table.Rows[i];
for (int ic = 0; ic < row.ItemArray.Length; ic++)
{
var cell = (row[ic] != null)?row[ic]:string.Empty;
sht.Cells.Add(2 + i, 1 + ic, cell.ToString());
}
}
newDoc.Save(file2Save);
file2Save.Close();
}
}
/// <summary>
/// 创建一个表格单元格的样式，fontSize=0表示使用默认大小11,默认居中
/// </summary>
/// <param name="doc"></param>
/// <param name="cellLine">是否使用边框线</param>
/// <param name="underline">字体是下划线样式，默认没有下划线</param>
/// <param name="fontSize">字体大小，0表示11</param>
/// <returns></returns>
public static AppLibrary.WriteExcel.XF NewXf(XlsDocument doc, CellLine cellLine, UnderlineTypes underline = UnderlineTypes.None, int fontSize = 0)
{
int size = fontSize;
if (fontSize == 0)
{
size = 10;
}
AppLibrary.WriteExcel.XF xf = doc.NewXF();
xf.Font.FontName = "宋体";
xf.Font.Height = (ushort)(size * 20);
xf.Font.Underline = underline;
xf.TextWrapRight = true;
xf.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
xf.VerticalAlignment = AppLibrary.WriteExcel.VerticalAlignments.Centered;
if (cellLine.ShowTop)
{
xf.TopLineStyle = 1;
}
if (cellLine.ShowRight)
{
xf.RightLineStyle = 1;
}
if (cellLine.ShowBottom)
{
xf.BottomLineStyle = 1;
}
if (cellLine.ShowLeft)
{
xf.LeftLineStyle = 1;
}
return(xf);
}
protected void Page_Load(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument(); //Create a new MyXls Document
xls.Send(); //MyXls adds a single empty sheet named Sheet1 when Send
//is called, if you didn't add one yourself.
}
private XlsDocument DataTableExportToExcel(DataTable dataTable, string excelName, string sheetName)
{
XlsDocument xls = new XlsDocument(); //新建一个xls文档
xls.FileName = excelName + ".xls"; //设定Excel文件名
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"第一个Sheet Demo"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格(cell)集合
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, "备注");
for (int row = 0; row < dataTable.Rows.Count; row++)
{
for (int column = 0; column < dataTable.Columns.Count; column++)
{
object val = dataTable.Rows[row][column];
cells.Add(row + 2, column + 1, val.ToString());
}
}
//xls.Save("E:\\Code\\ESB\\自主研发\\ESB\\Export\\", true);
xls.Save("D:\\App_Site\\ITSM\\Export\\", true);
//xls.Send();
return(xls);
}
/// <summary>
/// 导出Excel
/// </summary>
public static void ExportExcelForPercent(string sheetName, string xlsname, DataTable table)
{
//DataTable table = GetDataTableForPercent(areaid, dt);
if (table == null || table.Rows.Count == 0)
{
return;
}
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
//填充表头
foreach (DataColumn col in table.Columns)
{
sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
}
//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
}
}
//保存
xls.FileName = xlsname;
xls.Save();
xls = null;
}
internal XF GetDataCellXF(XlsDocument xls, Type dataType)
{
if (m_XF == null)
{
VerticalAlignments v1 = ConvertAlignments(VerticalAlignment).GetValueOrDefault(VerticalAlignments.Centered);
bool hasBorder1 = HasBorder.GetValueOrDefault(true);
XF xf = xls.NewXF();
xf.VerticalAlignment = v1;
xf.Font.Height = (ushort)(10 * this.FONT_HEIGHT_SCALE);
if (hasBorder1)
{
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.TextWrapRight = true;
}
m_XF = xf;
}
HorizontalAlignments h1 = ConvertAlignments(HorizontalAlignment).GetValueOrDefault(SetDefaultHorizontalAlignmentsForType(dataType));
m_XF.HorizontalAlignment = h1;
return(m_XF);
}
public static string getAllDataToExcel()
{
int code = 1;
string message = "success";
List <StudentsUser> listUser = new List <StudentsUser>();
using (StudentsUserBLL bll = new StudentsUserBLL())
{
listUser = bll.getStudentUser();
}
XlsDocument xls = new XlsDocument();
if (listUser.Count != 0)
{
xls.FileName = DateTime.Now.ToString("u").Replace(":", "_").Replace(" ", "_") + ".xls";
Worksheet sheet = xls.Workbook.Worksheets.Add("考生数据");
Cells cells = sheet.Cells;
cells.Add(1, 1, "身份证号");
cells.Add(1, 2, "姓名");
cells.Add(1, 3, "准考证");
cells.Add(1, 4, "手机号");
for (int i = 1; i <= listUser.Count; i++)
{
cells.Add(i + 1, 1, listUser[i - 1].Sfzh);
cells.Add(i + 1, 2, listUser[i - 1].StuName);
cells.Add(i + 1, 3, listUser[i - 1].Zkzh);
cells.Add(i + 1, 4, listUser[i - 1].Mobile);
}
xls.Save(HttpContext.Current.Server.MapPath("~/uploads") + "//");
}
return(JsonConvert.SerializeObject(
new { code = code, message = message, filePath = "uploads/" + xls.FileName }));
}
protected void Test()
{
XlsDocument xls = new XlsDocument();
xls.FileName = "D:\\12333333.xls";
string sheetName = "chc 实例";
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格（cell）集合
//单元格1-base
Cell cell = cells.Add(1, 2, "抗"); //设定第一行，第二例单元格的值
cell.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cell.Font.FontName = "方正舒体"; //设定字体
cell.Font.Height = 20 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cell.UseBorder = true; //使用边框
cell.BottomLineStyle = 2; //设定边框底线为粗线
cell.BottomLineColor = Colors.DarkRed; //设定颜色为暗红
//cell的格式还可以定义在一个xf对象中
CellFormat cellXF = xls.NewXF(); //为xls生成一个XF实例（XF是cell格式对象）
cellXF.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXF.Font.FontName = "方正舒体"; //设定字体
cellXF.Font.Height = 20 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXF.UseBorder = true; //使用边框
cellXF.BottomLineStyle = 2; //设定边框底线为粗线
cellXF.BottomLineColor = Colors.DarkRed; //设定颜色为暗红
cell = cells.Add(2, 2, "震", cellXF); //以设定好的格式填加cell
cellXF.Font.FontName = "仿宋_GB2312";
cell = cells.Add(3, 2, "救", cellXF); //格式可以多次使用
ColumnInfo colInfo = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = 1; //起始列为第二列
colInfo.ColumnIndexEnd = 5; //终止列为第六列
colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
colInfo.ColumnIndexEnd = 6; //可以更改列对象的值
ColumnInfo colInfo2 = new ColumnInfo(xls, sheet); //通过新生成一个列格式对象，才到能设定其它列宽度
colInfo2.ColumnIndexStart = 7;
colInfo2.ColumnIndexEnd = 8;
colInfo2.Width = 1 * 256;
sheet.AddColumnInfo(colInfo2);
MergeArea meaA = new MergeArea(1, 2, 3, 4); //一个合并单元格实例(合并第一行、第三例 到 第二行、第四例)
sheet.AddMergeArea(meaA); //填加合并单元格
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.Font.Height = 48 * 20;
cellXF.Font.Bold = true;
cellXF.Pattern = 3; //设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternBackgroundColor = Colors.DarkRed; //填充的底色
cellXF.PatternColor = Colors.DarkGreen; //设定填充线条的颜色
cell = cells.Add(1, 3, "灾", cellXF);
xls.Save();
}
/// <summary>
/// 将EXCEL导出到DataTable
/// </summary>
/// <param name="excelPath">excel路径</param>
/// <param name="sheetIndex">Worksheets『从0开始』</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelPath, int sheetIndex)
{
CheckedHanlder.CheckedExcelFileParamter(excelPath, true);
XlsDocument _excelDoc = new XlsDocument(excelPath);
DataTable _table = new DataTable();
Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex];
ushort _colCount = _sheet.Rows[1].CellCount;
ushort _rowCount = (ushort)_sheet.Rows.Count;
for (ushort j = 1; j <= _colCount; j++)
{
_table.Columns.Add(new DataColumn(j.ToString()));
}
for (ushort i = 1; i <= _rowCount; i++)
{
DataRow _row = _table.NewRow();
for (ushort j = 1; j <= _colCount; j++)
{
_row[j - 1] = _sheet.Rows[i].GetCell(j).Value;
}
_table.Rows.Add(_row);
}
return(_table);
}
static void SaveXLS(List <string> codes)
{
try
{
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
XF titleXF = xlsDoc.NewXF();
titleXF.Font.Bold = true;
titleXF.Font.FontName = "宋体";
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add("sheet1");
for (int i = 0; i < codes.Count; i++)
{
sheet.Cells.Add(i + 1, 1, codes[i]);
}
xlsDoc.Save();
}
catch (Exception ex)
{
throw new Exception("生成Excel文件失败", ex);
}
}
public void ExportToExcel(DataTable dtSource, string strFileName)
{
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");
int i = 0;
//write data from datatable in Excel file
foreach (DataColumn column in dtSource.Columns)
{
sheet.Cells.Add(1, i + 1, column.ColumnName);
i++;
}
for (int j = 0; j < dtSource.Rows.Count; j++)
{
for (int k = 0; k < dtSource.Columns.Count; k++)
{
sheet.Cells.Add(j + 2, k + 1, dtSource.Rows[j][k].ToString());
}
}
// save
xls.FileName = strFileName;
if (File.Exists(strFileName))
{
File.Delete(strFileName);
}
xls.Save();
}
/// <summary>
/// 返回需要导出的Excel字节
/// </summary>
/// <param name="filetitle">Excel文件名</param>
/// <param name="titles">Excel标题</param>
/// <param name="dt">需要导出的Table数据</param>
/// <returns>Excel字节流</returns>
protected byte[] GetExcelBytes(string filetitle, List <string> titles, DataTable dt)
{
XlsDocument xls = new XlsDocument();
xls.FileName = filetitle;
Worksheet wks = xls.Workbook.Worksheets.Add(filetitle);
Cells cell = wks.Cells;
XF xf = xls.NewXF();
for (int i = 1; i <= dt.Rows.Count; i++)
{
for (int j = 1; j <= titles.Count; j++)
{
if (i == 1)
{
cell.Add(i, j, titles[j - 1], xf);
}
else
{
cell.Add(i + 1, j, dt.Rows[i - 1][j - 1], xf);
}
}
}
return(xls.Bytes.ByteArray);
}
/// <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");//状态栏标题名称
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 20 * 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;
Cells cells = sheet.Cells;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col.ColumnName, xf);
cell.Font.Bold = true;
}
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>
/// 释放资源
/// </summary>
public void Dispose()
{
doc = null;
ds.Dispose();
if (!ds.IsNull())
{
ds = null;
}
}
public override byte[] RetByte(List <string[]> list, int operateid, string title)
{
string[] titles = { "序号", "合同号", " 姓名", "身份证号", "名义金额", "月本金",
"月利息", "扣款失败手续费", "罚息", "总合计", "月利率", "贷款期限","账号","开户省市", "放贷月份", "扣款月份", "科目名称" };
XlsDocument xls = new XlsDocument();
xls.FileName = title;
Worksheet wks = xls.Workbook.Worksheets.Add(title);
Cells cell = wks.Cells;
XF xf = ExportExtend.SetCommonStyle(xls);
ExportExtend.SetColumnWidth(xls, wks, 1, 20);
ExportExtend.SetColumnWidth(xls, wks, 2, 11);
ExportExtend.SetColumnWidth(xls, wks, 3, 25);
ExportExtend.SetColumnWidth(xls, wks, 4, 12);
ExportExtend.SetColumnWidth(xls, wks, 5, 12);
ExportExtend.SetColumnWidth(xls, wks, 6, 12);
ExportExtend.SetColumnWidth(xls, wks, 7, 15);
ExportExtend.SetColumnWidth(xls, wks, 8, 12);
ExportExtend.SetColumnWidth(xls, wks, 9, 12);
ExportExtend.SetColumnWidth(xls, wks, 10, 12);
ExportExtend.SetColumnWidth(xls, wks, 11, 10);
ExportExtend.SetColumnWidth(xls, wks, 12, 25);
ExportExtend.SetColumnWidth(xls, wks, 14, 13);
ExportExtend.SetColumnWidth(xls, wks, 15, 13);
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 == 1)
{
Cell cel = cell.Add(i + 1, 1, i, xf);
}
else if (j >= 5 && j <= 10)//金额格式
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim().ToDecimal(), xf);
}
else if (j == 12)
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim().ToInt(), xf);
}
else
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim(), xf);
}
}
}
return(xls.Bytes.ByteArray);
}
public static void ExportExcelForPercentForWebs(string sheetName, string xlsname, string[] areaid)
{
XlsDocument xls = new XlsDocument();
DataTable table = GetDataTableForPercents(areaid);
Worksheet sheet;
try
{
sheet = xls.Workbook.Worksheets.Add("员工信息表");
if (table == null || table.Rows.Count == 0)
{
return;
}
//填充表头
foreach (DataColumn col in table.Columns)
{
sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
}
//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
}
}
using (MemoryStream ms = new MemoryStream())
{
xls.Save(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
xls = null;
HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + xlsname));
byte[] data = ms.ToArray();
System.Web.HttpContext.Current.Response.BinaryWrite(data);
}
}
catch (Exception e)
{
Log.Error(e.StackTrace);
}
finally
{
sheet = null;
xls = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "MyXLS Hello World.xls";
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("Hello, World!");
sheet.Cells.AddValueCell(1, 1, "Hello, World!");
xls.Send();
}
/// <summary>
/// 按行写入数据
/// </summary>
/// <param name="filePath">完整的保存路径 包含文件名</param>
/// <param name="firstTitleRow">第一行的Title信息</param>
/// <param name="rowsInfo">每行数据</param>
public static void ExcelExportWriteByRow(string filePath, List <object> firstTitleRow,
List <List <object> > rowsInfo)
{
try
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
string sheetName = Path.GetFileNameWithoutExtension(filePath);
var xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
for (int j = 0; j < firstTitleRow.Count; j++)
{
if (firstTitleRow[j] == null)
{
continue;
}
sheet.Cells.Add(1, j + 1, firstTitleRow[j].ToString());
}
for (int j = 0; j < rowsInfo.Count; j++)
{
if (j > MaxRowCount)
{
sheet.Cells.Add(j + 2, 1, "超出100000列，不再写入.");
break;
}
for (int k = 0; k < rowsInfo[j].Count; k++)
{
if (rowsInfo[j][k] == null)
{
continue;
}
sheet.Cells.Add(j + 2, k + 1, rowsInfo[j][k].ToString());
}
}
string strFilePath = Path.GetDirectoryName(filePath);
try
{
xls.FileName = sheetName + ".xls";
}
catch (Exception ex)
{
Wlst.Cr.Core.UtilityFunction.WriteLog.WriteLogError("系统在导出报表设置报表名称时候报错：但跳过，名称：" + sheetName + ";ex:" + ex);
}
xls.Save(strFilePath);
xls = null;
}
catch (Exception ex)
{
Wlst.Cr.Core.UtilityFunction.WriteLog.WriteLogError("系统在导出报表时出错：" + ex);
}
}
/// <summary>
/// 生成XSL
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="path">保存路径(包含文件名)</param>
/// <param name="overwrite">是否覆盖</param>
/// <returns></returns>
public static bool CreateXLS(DataSet ds, string path, bool overwrite)
{
if (File.Exists(path) && !overwrite)
{
return(false);
}
try
{
//1.创建xls对象
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = Path.GetFileName(path);
for (int i = 0; i < ds.Tables.Count; i++)
{
//2.创建表
string sheetName = string.IsNullOrEmpty(ds.Tables[i].TableName) ? "Sheet" + i.ToString() : ds.Tables[i].TableName;
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add(sheetName);
//3.创建行列,注意cellRow,cellColumn都必须>=1
Cells cells = sheet.Cells;
//3.1 添加字段名
for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
{
Cell cell = cells.Add(1, col + 1, ds.Tables[i].Columns[col].ColumnName);
cell.Font.Weight = FontWeight.Bold;
}
//3.2 添加记录
for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
{
for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
{
cells.Add(row + 2, col + 1, string.IsNullOrEmpty(ds.Tables[i].Rows[row][col].ToString()) ? "-" : ds.Tables[i].Rows[row][col].ToString());
}
}
}
//4.准备保存文件夹
if (!Directory.Exists(Path.GetDirectoryName(path)))
{
Directory.CreateDirectory(Path.GetDirectoryName(path));
}
//5.保存
xlsDoc.Save(Path.GetDirectoryName(path), overwrite);
}
catch
{
return(false);
}
return(true);
}
public override byte[] RetByte(List <string[]> list, int operateid,
List <Customer> customer, string title)
{
string[] titles = { "币种", "顺序号", "协议编号", "收款单位名称", "付款金额", "用途", "备注"
, "付款账户短信通知手机号码", "自定义序号", "是否是工行账号", "付方账号开户行行号" };
XlsDocument xls = new XlsDocument();
xls.FileName = title;
Worksheet wks = xls.Workbook.Worksheets.Add(title);
Cells cell = wks.Cells;
XF xf = ExportExtend.SetCommonStyle(xls);
ExportExtend.SetColumnWidth(xls, wks, 1, 25);
ExportExtend.SetColumnWidth(xls, wks, 3, 25);
ExportExtend.SetColumnWidth(xls, wks, 4, 15);
ExportExtend.SetColumnWidth(xls, wks, 7, 25);
ExportExtend.SetColumnWidth(xls, wks, 12, 12);
ExportExtend.SetColumnWidth(xls, wks, 15, 25);
ExportExtend.SetColumnWidth(xls, wks, 17, 20);
ExportExtend.SetColumnWidth(xls, wks, 18, 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 == 1)
{
Cell cel = cell.Add(i + 1, 1, i, xf);
}
else if ((j >= 5 && j <= 13))
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].ToDecimal(), xf);
}
else if (j == 14)
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].ToInt(), xf);
}
else
{
Cell cel = cell.Add(i + 1, j, list[i - 1][j - 1].Trim(), xf);
}
}
}
return(xls.Bytes.ByteArray);
}
public static string getstuAllInfoToExcle()
{
int code = 1;
string message = "返回数据成功";
List <StuAllInfo> ListUser = new List <StuAllInfo>();
using (StuAllInfoBLL bll = new StuAllInfoBLL())
{
ListUser = bll.getStuAllInfo();
}
XlsDocument xls = new XlsDocument();//建立excle工作表
if (ListUser.Count != 0)
{
xls.FileName = DateTime.Now.ToString("u").Replace(":", "-").Replace("Z", "") + ".xls";
Worksheet sheet = xls.Workbook.Worksheets.Add("考生数据");
Cells cells = sheet.Cells;
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, "第二志愿");
for (int i = 2; i < ListUser.Count; i++)
{
cells.Add(i, 1, ListUser[i - 2].Sfzh);
cells.Add(i, 2, ListUser[i - 2].StuName);
cells.Add(i, 3, ListUser[i - 2].Xb == 1 ? "男" : "女");
cells.Add(i, 4, ListUser[i - 2].Zkzh);
cells.Add(i, 5, ListUser[i - 2].Mobile);
if (ListUser[i - 2].FrsZY != null)
{
cells.Add(i, 6, getZymcByZydm(ListUser[i - 2].FrsZY));
}
if (ListUser[i - 2].SecZY != null)
{
if (ListUser[i - 2].SecZY.Trim().Length != 0)
{
cells.Add(i, 7, getZymcByZydm(ListUser[i - 2].SecZY));
}
cells.Add(i, 7, getZymcByZydm(ListUser[i - 2].SecZY));
}
}
xls.Save(HttpContext.Current.Server.MapPath("~/uploads") + "//");
}
return(JsonConvert.SerializeObject(new
{
code = code,
message = message,
filePath = "uploads/" + xls.FileName,
}));
}
public XLSType createXlsRequestTest(string number, string street1, string street2, string city, string requestID)
{
XlsDocument xLSDocument = XlsDocument.CreateDocument();
XLSType xLSType = xLSDocument.XLS.Append();
xLSType.lang.Value = ServiceConfigProperties.Language;
// should be added one only
RequestUtil.addXlsHeader(ref xLSType);
addGeocodeRequest(ref xLSType, number, street1, street2, city, requestID);
return(xLSType);
}
public void CreateSpreadsheetFromIEnumerable()
{
DataSourceConverter <DisneyCharacter> converter = new DataSourceConverter <DisneyCharacter>();
converter.Fields.Add(new AdapterBoundField <DisneyCharacter>("Name", "Character Name"));
converter.Fields.Add(new AdapterBoundField <DisneyCharacter>("Age", null, "{0:###}"));
XlsDocument doc = new XlsDocument();
converter.CreateWorksheet(doc, _dataList, "testing");
ValidateXlsToIList(doc);
}
public string Export(DataTable dt)
{
XlsDocument xls = new XlsDocument();
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + "库区库存";
xls.FileName = fileName;
int rowIndex = 1;
Worksheet sheet = xls.Workbook.Worksheets.Add("库区库存");
Cells cells = sheet.Cells;
sheet.Cells.Merge(1, 1, 1, 2);
Cell cell = cells.Add(1, 1, "库区库存");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 1, "产品代码");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 2, "产品名称");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 3, "单位编码");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 4, "单位名称");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 5, "库存数量");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell = cells.Add(2, 6, "库区名称");
cell.Font.Bold = true;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
foreach (DataRow row in dt.Rows)
{
cells.Add(rowIndex + 2, 1, "" + row["CURRENTPRODUCT"] + "");
cells.Add(rowIndex + 2, 2, "" + row["PRODUCTNAME"] + "");
cells.Add(rowIndex + 2, 3, "" + row["UNITCODE"] + "");
cells.Add(rowIndex + 2, 4, "" + row["UNITNAME"] + "");
cells.Add(rowIndex + 2, 5, "" + row["QUANTITY"] + "");
cells.Add(rowIndex + 2, 6, "" + row["AREANAME"] + "");
rowIndex++;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
string file = System.Web.HttpContext.Current.Server.MapPath("~/Excel/");
xls.Save(file);
//xls.Send();
return(fileName);
}
public void CreateWorksheetWithDataTable()
{
DataSourceConverter <DataRow> converter = new DataSourceConverter <DataRow>();
converter.Fields.Add(new AdapterBoundField <DataRow>("Name", "Character Name"));
converter.Fields.Add(new AdapterBoundField <DataRow>("Age", "Age", "{0:###}"));
XlsDocument doc = new XlsDocument();
converter.CreateWorksheet(doc, _data, _data.TableName);
Assert.AreEqual(1, doc.Workbook.Worksheets.Count);
ValidateWorksheetFromDataTable(doc.Workbook.Worksheets[0], _data);
}
/// <summary>
/// Populates an xls document from a <see cref="DataSet"/>
/// </summary>
/// <param name="dataset">Source <see cref="DataSet"/> </param>
/// <param name="document">Document to populate with the data.</param>
/// <returns><see cref="XlsDocument"/> containg the data from the <see cref="DataSet"/>.</returns>
public XlsDocument CreateDocument(XlsDocument document, DataSet dataset)
{
document = document ?? new XlsDocument();
if (_tableAdapters == null || _tableAdapters.Count == 0)
{
_tableAdapters = CreateDefaultAdapters(dataset);
}
for (int i = 0; i < _tableAdapters.Count; i++)
{
_tableAdapters[i].PopulateWorksheet(document, null, dataset.Tables[i], dataset.Tables[i].TableName);
}
return document;
}
public void WithCustomConverters()
{
DataSetConverter converter = new DataSetConverter();
DataSourceConverter<DataRow> table1Converter = new DataSourceConverter<DataRow>();
table1Converter.Fields.Add(new AdapterBoundField<DataRow>("Name", "Character Name"));
table1Converter.Fields.Add(new AdapterBoundField<DataRow>("Age", "Age", "{0:###}"));
converter.TableAdapters.Add(table1Converter);
converter.TableAdapters.Add(DataSetConverter.CreateDefaultDataTableConverter(_data.Tables[1]));
XlsDocument doc = new XlsDocument();
converter.CreateDocument(doc, _data);
Assert.AreEqual(_data.Tables.Count, doc.Workbook.Worksheets.Count);
DataSourceConverterTest.ValidateWorksheetFromDataTable(doc.Workbook.Worksheets[0], _data.Tables[0]);
ValidateSheetFromDataTable(doc.Workbook.Worksheets[1], _data.Tables[1]);
}
public void OnBoundField_CellDataBoundCalled()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Test");
Row row = sheet.Rows.AddRow(1);
Cell cell = sheet.Cells.Add(1, 1);
AdapterBoundField<int> field = new AdapterBoundField<int>("test");
field.CellDataBound = TestCellDataBound;
field.OnBoundField(this, cell, row, 50);
Assert.IsNotNull(_storedArgs, "event not called");
Assert.IsNotNull(_storedSender, "event not called");
Assert.AreEqual(this, _storedSender);
Assert.AreEqual(50, _storedArgs.DataItem);
Assert.AreEqual(cell, _storedArgs.Cell);
Assert.AreEqual(row, _storedArgs.Row);
}
public void WithDefaultConverters()
{
DataSetConverter converter = new DataSetConverter();
XlsDocument doc = new XlsDocument();
converter.CreateDocument(doc, _data);
Assert.AreEqual(_data.Tables.Count, doc.Workbook.Worksheets.Count);
for (int i = 0; i < doc.Workbook.Worksheets.Count; i++)
{
Worksheet worksheet = doc.Workbook.Worksheets[i];
Assert.AreEqual(_data.Tables[i].TableName, worksheet.Name);
// validte header row
ValidateSheetFromDataTable(worksheet, _data.Tables[i]);
}
}
