public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
{
MemoryStream ms = new MemoryStream();
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < headers.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(headers[i]);
}
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 批量导出本校第一批派位学生
/// </summary>
/// <returns></returns>
public FileResult ExportStu2()
{
// schoolname = "401";
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
List <Staff> listRainInfo = db.Staffs.ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("电脑号");
row1.CreateCell(1).SetCellValue("姓名");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
}
public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> list)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
int i = 0;
foreach (var item in title)
{
row1.CreateCell(i).SetCellValue(item);
i++;
}
//将数据逐步写入sheet1各个行
for (int j = 0; j < list.Count; j++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
rowtemp.CreateCell(1).SetCellValue(list[j].userName);
rowtemp.CreateCell(2).SetCellValue(list[j].phone);
}
return(book);
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
{
SetCellRangeAddress(sheet, 0, 0, 0, 1);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
SetCellRangeAddress(sheet, 0, 0, 6, 7);
SetCellRangeAddress(sheet, 0, 0, 9, 10);
SetCellRangeAddress(sheet, 0, 0, 12, 14);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("输入功率标定");
row.CreateCell(3).SetCellValue("输出功率标定");
row.CreateCell(6).SetCellValue("反射功率标定");
row.CreateCell(9).SetCellValue("ALC功率标定");
row.CreateCell(12).SetCellValue("衰减补偿");
row.GetCell(0).CellStyle = GetCellStyle(book);
row.GetCell(3).CellStyle = GetCellStyle(book);
row.GetCell(6).CellStyle = GetCellStyle(book);
row.GetCell(9).CellStyle = GetCellStyle(book);
row.GetCell(12).CellStyle = GetCellStyle(book);
row = sheet.CreateRow(1);
for (int i = 0; i < 4; i++)
{
row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
row.GetCell(3 * i).CellStyle = GetCellStyle(book);
row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
}
row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
row.GetCell(12).CellStyle = GetCellStyle(book);
row.GetCell(13).CellStyle = GetCellStyle(book);
row.GetCell(14).CellStyle = GetCellStyle(book);
}
public IActionResult ExportExcel(string keyword = "")
{
DataAccess.TASM_MACHINEManager manager = new DataAccess.TASM_MACHINEManager();
List <MachineModel> list = new List <MachineModel>();
manager.ListByWhere(keyword, ref list);
HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作簿Excel
ISheet sheet1 = excelBook.CreateSheet("项目履历表");
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("设备名称");
row1.CreateCell(1).SetCellValue("设备类型");
row1.CreateCell(2).SetCellValue("设备序号");
row1.CreateCell(3).SetCellValue("客户名称");
row1.CreateCell(4).SetCellValue("合同编号");
row1.CreateCell(5).SetCellValue("订单时间");
row1.CreateCell(6).SetCellValue("送货时间");
row1.CreateCell(7).SetCellValue("送货单号");
row1.CreateCell(8).SetCellValue("验收时间");
row1.CreateCell(9).SetCellValue("创建时间");
for (int i = 0; i < list.Count(); i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(list[i].NAME);
rowTemp.CreateCell(1).SetCellValue(list[i].TYPESNAME);
rowTemp.CreateCell(2).SetCellValue(list[i].SERIAL);
rowTemp.CreateCell(3).SetCellValue(list[i].CUSTOMER);
rowTemp.CreateCell(4).SetCellValue(list[i].CONTRACT);
rowTemp.CreateCell(5).SetCellValue(list[i].ORDERTIME.Value.ToString("yyyy-MM-dd"));
rowTemp.CreateCell(6).SetCellValue(list[i].DELIVERYTIME.Value);
rowTemp.CreateCell(7).SetCellValue(list[i].DELIVERYNUMBER);
rowTemp.CreateCell(8).SetCellValue(list[i].CHECKTIME.Value);
rowTemp.CreateCell(9).SetCellValue(list[i].CREATETIME.Value);
}
var fileName = "设备台账" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";//文件名
//将Excel表格转化为流，输出
MemoryStream bookStream = new MemoryStream();
excelBook.Write(bookStream);
bookStream.Seek(0, SeekOrigin.Begin);
return(File(bookStream, "application/vnd.ms-excel", fileName));
}
//导出 dt,和表头都传
public Stream ExportExcel(DataTable dt, List <EColName> list)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//组建表头
int index = 0;
foreach (var i in list)
{
row1.CreateCell(index).SetCellValue(i.col);
i.sortseq = index;
index = index + 1;
}
//将数据逐步写入sheet1各个行
double d0 = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < list.Count; jj++)
{
string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
string coldata = dt.Rows[i][colname].ToString();
string dbtype = dt.Columns[colname].DataType.ToString();
if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
{
if (double.TryParse(coldata, out d0))
{
d0 = Convert.ToDouble(coldata);
}
rowtemp.CreateCell(jj).SetCellValue(d0);
}
else
{
rowtemp.CreateCell(jj).SetCellValue(coldata);
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(ms);
}
public string TQGZ_DAOCHU(string data)
{
string token = AppClass.GetSession("token").ToString();
int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID"));
DaoRuMsg msg = new DaoRuMsg();
try
{
WMS_JPD_JHD_TQGZ[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <WMS_JPD_JHD_TQGZ[]>(data);
FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/过账授权导出模板.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = new XSSFWorkbook(file);
ISheet worksheet1 = workbook.GetSheet("过账授权");
if (worksheet1 == null)
{
msg.Msg = "err";
msg.Info = "工作薄中没有工作表";
return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
}
//worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4));
int row1 = 1;
for (int i = 0; i < model.Length; i++)
{
int count = 0;
NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1);
row.CreateCell(count++).SetCellValue(model[i].JHD);
row.CreateCell(count++).SetCellValue(model[i].XSZZ);
row.CreateCell(count++).SetCellValue(model[i].XSZZNAME);
row.CreateCell(count++).SetCellValue(model[i].SDF);
row.CreateCell(count++).SetCellValue(model[i].SDFNAME);
row.CreateCell(count++).SetCellValue(model[i].KFZT == 1 ? "未开放" : "已开放");
row.CreateCell(count++).SetCellValue(model[i].CJTIME);
row.CreateCell(count++).SetCellValue(model[i].CJRNAME);
row.CreateCell(count++).SetCellValue(model[i].XGTIME);
row.CreateCell(count++).SetCellValue(model[i].XGRNAME);
row1++;
}
worksheet1.ForceFormulaRecalculation = true;
string now = DateTime.Now.ToString("yyyy-MM");
FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create);
workbook.Write(file1);
file1.Close();
msg.Msg = now;
return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
}
catch (Exception e)
{
msg.Msg = "err";
msg.Info = e.ToString();
return(Newtonsoft.Json.JsonConvert.SerializeObject(msg));
}
}
private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow)
{
//int datastartrow = 0;
if (!string.IsNullOrEmpty(datadesc))
{
NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow);
for (int i = 0; i < 10; i++)
{
desc.CreateCell(i);
}
NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9);
sheet1.AddMergedRegion(address);
sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc);
datastartrow++;
}
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow);
int tick = 0;
foreach (string value in columnInfo.Values)
{
row1.CreateCell(tick).SetCellValue(value);
tick++;
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1);
int index = 0;
foreach (string key in columnInfo.Keys)
{
bool isnumberic = false;
if (key.ToLower() == "rowid")
{
rowtemp.CreateCell(index).SetCellValue(i + 1);
}
else
{
string data = GetValue(dt.Rows[i], key, out isnumberic);
if (isnumberic && data != "--")
{
rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data));
}
else
{
rowtemp.CreateCell(index).SetCellValue(data);
}
}
index++;
}
}
}
//导出 只传dt
public Stream ExportExcel(DataTable dt)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//组建表头
DataColumnCollection dcc = dt.Columns;
int index = 0;
foreach (DataColumn i in dcc)
{
row1.CreateCell(index).SetCellValue(i.ColumnName);
index = index + 1;
}
double d0 = 0;
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < dcc.Count; jj++)
{
string dbtype = dcc[jj].DataType.ToString();
string coldata = dt.Rows[i][dcc[jj].ColumnName].ToString();
if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
{
if (double.TryParse(coldata, out d0))
{
d0 = Convert.ToDouble(coldata);
}
rowtemp.CreateCell(jj).SetCellValue(d0);
}
else
{
rowtemp.CreateCell(jj).SetCellValue(coldata);
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(ms);
}
private void ExcelExport()
{
string sheet1Title = "Sheet1";
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(sheet1Title);
NPOI.SS.UserModel.IRow headerRow4Sheet1 = sheet1.CreateRow(0);
ExcelCellStyle cellStyle = new ExcelCellStyle(book);
ICell cell;//初始化ICell准备设值
#region 设定Sheet1的头行
cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户ID");
cell = headerRow4Sheet1.CreateCell(1); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户名");
cell = headerRow4Sheet1.CreateCell(2); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户地址");
#endregion
/*0行已经设为表头行，内容从第1行开始设值*/
int sheet1RowID = 1;
Customer[] listCustomer = client.GetCustomer("");
foreach (Customer customer in listCustomer)
{
IRow r = sheet1.CreateRow(sheet1RowID);
cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style;
sheet1RowID += 1;//每做一个循环就到下一行设置
}
/*以下是做自适应宽度*/
ChangeStyle(book, sheet1);
/*最后作输出流导出文件*/
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("客户列表信息", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="dt"></param>
/// <param name="sheetName"></param>
public static void Export(string[] fileName, string[] title, string sheetName, DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i].ToString());
}
List <string> ids = new List <string>();
int index = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ids.Contains(dt.Rows[i]["ID"].ToString()))
{
continue;
}
else
{
ids.Add(dt.Rows[i]["ID"].ToString());
row = sheet.CreateRow(index + 1);
index++;
for (int j = 0; j <= fileName.Length; j++)
{
if (j == 0)
{
row.CreateCell(j).SetCellValue(index + 1);
}
else
{
row.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j - 1]].ToString());
}
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + sheetName + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public static void ExcelOut(DataTable dt, string fullXlsFilePath)
{
//创建Excel文件的对象
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headRow = sheet1.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headRow.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//加上设置大小下载下来的.xlsx文件打开时才没有错误
FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);
book.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意
FileStream fs = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
long fileSize = fs.Length;
//加上设置大小下载下来的.xlsx文件打开时才没有错误
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
}
private static void CreatAddCell(NPOI.SS.UserModel.IRow dataRow, string value, NPOI.SS.UserModel.ICellStyle style, int col)
{
var cell = dataRow.CreateCell(col);
cell.SetCellValue(value);
cell.CellStyle = style;
}
public void NpoiExcel(DataTable dt, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
public static MemoryStream ExportExcel(DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
public void NpoiExcel(DataTable dt, string title, string s)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
string[] ss = s.Split(',');
for (int i = 0; i < ss.Length; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(ss[i]);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
//double a = 0;
//string b = "";
//try
//{
// a = Convert.ToDouble(dt.Rows[I][j].ToString());
// row2.CreateCell(j).SetCellValue(a);
// sheet.SetColumnWidth(j, 20 * 150);
//}
//catch
//{
// b = dt.Rows[I][j].ToString();
// row2.CreateCell(j).SetCellValue(b);
// sheet.SetColumnWidth(j, 20 * 150);
//}
string DgvValue = dt.Rows[I][j].ToString();
if (j == 8 || j == 9)
{
row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
}
else
{
row2.CreateCell(j).SetCellValue(DgvValue);
}
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream ExportMemory(DataTable source, List <string> caption)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssfff");
string fileName = "查询结果" + dateTime + ".xls";
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
/// <summary>
/// 服务商结算订单导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;//流位置归零
return(ms);
//写入到客户端
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
if (caption == null)
{
caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
}
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
/// <summary>
/// Excel导出返回文件路径
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
/// <param name="FilePath"></param>
/// <param name="ext"></param>
/// <returns></returns>
public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
}
FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));
book.Write(fs2);
fs2.Close();
return(FilePath);
//写入到客户端
}
public void NpoiExcel(System.Data.DataTable dt, string file)
{
try
{
double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
int sheetCount = (int)(Math.Ceiling(sheetCountdbl));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
for (int c = 0; c < sheetCount; c++)
{
string sheetname = "Sheet" + (c + 1).ToString();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
int js = 0;//计数
//表内容
for (int i = c * 60000; i < dt.Rows.Count; i++)
{
if (js > 59999)
{
break;
}
IRow row = sheet.CreateRow(js + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = style;
cell.SetCellValue(dt.Rows[i][j].ToString());
}
js++;
}
}
FileStream fs = File.Create(file);
book.Write(fs);
fs.Flush();
fs.Close();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 批量导出
/// </summary>
/// <returns></returns>
public FileResult ExportStu()
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
int pager_totalcount = (int)Session["pager_totalcount"];
int totalCount = 0;
//获取list数据
List <ArticleEntity> infoList = new AchieveDAL.MyTestDAL().GetArticleList("", pager_totalcount, 1, out totalCount);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//创建时间 名称 商户订单号 | 交易号 对方 金额(元) 状态
row1.CreateCell(0).SetCellValue("编号");
row1.CreateCell(1).SetCellValue("标题");
row1.CreateCell(2).SetCellValue("内容");
int Width = 256;
sheet1.SetColumnWidth(0, 10 * Width);
sheet1.SetColumnWidth(1, 25 * Width);
sheet1.SetColumnWidth(2, 60 * Width);
if (infoList != null)
{
var list = infoList.OrderByDescending(p => p.ID);
if (list != null)
{
int i = 0;
//将数据逐步写入sheet1各个行
foreach (var item in list)
{
i = i + 1;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i);
rowtemp.CreateCell(0).SetCellValue(item.ID.ToString());
rowtemp.CreateCell(1).SetCellValue(item.Title == null ? "" : item.Title.ToString());
rowtemp.CreateCell(2).SetCellValue(item.Content == null ? "" : item.Content.ToString());
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", HttpUtility.UrlEncode("导出", Encoding.UTF8).ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
public ActionResult Download(string downloadfile)
{
string[] titleList = new string[] { "顏色ID", "顏色", "三原色的 R", "三原色的 G", "三原色的 B" };
var list = db.Colors.ToList();//獲取資料方法
//建立工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//建立sheet
ISheet sheet1 = workbook.CreateSheet("sheet1");
//給sheet1新增第一行的頭部標題
IRow headerrow = sheet1.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < titleList.Length; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = headStyle;
cell.SetCellValue(titleList[i]);
}
//將資料逐步寫入sheet1各個行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].ColorID);
rowtemp.CreateCell(1).SetCellValue(Convert.ToString(list[i].ColorName));
rowtemp.CreateCell(2).SetCellValue(Convert.ToInt32(list[i].R));
rowtemp.CreateCell(3).SetCellValue(Convert.ToInt32(list[i].G));
rowtemp.CreateCell(4).SetCellValue(Convert.ToInt32(list[i].B));
}
// 寫入到客戶端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
return(this.File(ms.ToArray(), "application/vnd.ms-excel", "顏色資料表.xlsx"));
}
public IActionResult ExportExcel(string keyword = "")
{
DataAccess.TASM_CUSTOMERManager manager = new DataAccess.TASM_CUSTOMERManager();
List <TASM_CUSTOMER> list = new List <TASM_CUSTOMER>();
manager.ListByWhere(keyword, ref list);
HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作簿Excel
ISheet sheet1 = excelBook.CreateSheet("项目履历表");
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("客户名称");
row1.CreateCell(1).SetCellValue("手机");
row1.CreateCell(2).SetCellValue("邮箱");
row1.CreateCell(3).SetCellValue("地址");
row1.CreateCell(4).SetCellValue("创建时间");
for (int i = 0; i < list.Count(); i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(list[i].NAME);
rowTemp.CreateCell(1).SetCellValue(list[i].PHONE);
rowTemp.CreateCell(2).SetCellValue(list[i].EMIAL);
rowTemp.CreateCell(3).SetCellValue(list[i].ADRESS);
rowTemp.CreateCell(4).SetCellValue(list[i].CREATETIME.ToString());
}
var fileName = "客户信息表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";//文件名
//将Excel表格转化为流，输出
MemoryStream bookStream = new MemoryStream();
excelBook.Write(bookStream);
bookStream.Seek(0, SeekOrigin.Begin);
return(File(bookStream, "application/vnd.ms-excel", fileName));
}
public ActionResult ExcelOut()
{
var users = _context.Users.ToList();
////创建工作簿Excel
HSSFWorkbook book = new HSSFWorkbook();
//为工作簿创建工作表并命名
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("用户信息");
//创建第一行
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//创建其他列并赋值(相当于表格的表头)
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("性别");
row1.CreateCell(2).SetCellValue("班级");
row1.CreateCell(3).SetCellValue("地址");
row1.CreateCell(4).SetCellValue("邮箱");
//循环输出
for (int i = 0; i < users.Count(); i++)
{
//创建行
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(users[i].Name);
if (users[i].Grader == "true")
{
rowTemp.CreateCell(1).SetCellValue("男");
}
else
{
rowTemp.CreateCell(1).SetCellValue("女");
}
rowTemp.CreateCell(2).SetCellValue(users[i].Phone);
rowTemp.CreateCell(3).SetCellValue(users[i].Address);
rowTemp.CreateCell(4).SetCellValue(users[i].Email);
}
//文件名
var fileName = "用户信息报表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
//将Excel表格转化为流，输出
MemoryStream bookStream = new MemoryStream(); //创建文件流
book.Write(bookStream); //文件写入流（向流中写入字节序列）
bookStream.Seek(0, SeekOrigin.Begin); //输出之前调用Seek，把0位置指定为开始位置
return(File(bookStream, "application/vnd.ms-excel", fileName)); //最后以文件形式返回
}
/// <summary>
/// Excel 导出
/// Author:liuqijun 2015-4-13
/// </summary>
/// <param name="dt">数据源表格</param>
/// <param name="sheetName">Excel sheetName</param>
/// <param name="outStream">输出流</param>
public static void Export(DataTable dt, string sheetName, Stream outStream)
{
try
{
if (dt == null)
{
return;
}
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet;
if (String.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet("sheet1");
}
else
{
sheet = workbook.CreateSheet(sheetName);
}
//填充列名
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//填充内容
for (int i = 0; i < dt.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//保存
workbook.Write(outStream);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public FileResult Export(Pagination rq, AdminCredential User)
{
#region 报表导出
var report = service.Get(rq.vid).Data;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 10; //字体大小
font.FontName = "宋体"; //和excel里面的字体对应
font.Boldweight = short.MaxValue; //字体加粗
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
style.SetFont(font); //将字体样式赋给样式对象
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 430;
var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
int i = 0;
foreach (var item in FieldList)
{
ICell cell = row1.CreateCell(i);
cell.SetCellValue(item.Title);
cell.CellStyle = style;
i++;
}
rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
DataTable dt = service.GetExportReportData(rq, User);
//将数据逐步写入sheet1各个行
for (int z = 0; z < dt.Rows.Count; z++)
{
var row = dt.Rows[z];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
int c = 0;
foreach (var item in FieldList)
{
rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));
#endregion
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}