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);
}
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();
}
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++;
}
}
}
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();
}
/// <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);
}
public void TestNotCreateEmptyCells()
{
IWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = new SheetBuilder(wb, testData).Build();
Assert.AreEqual(sheet.PhysicalNumberOfRows, 3);
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
NPOI.SS.UserModel.ICell firstCell = firstRow.GetCell(0);
Assert.AreEqual(firstCell.CellType, CellType.NUMERIC);
Assert.AreEqual(1.0, firstCell.NumericCellValue, 0.00001);
NPOI.SS.UserModel.IRow secondRow = sheet.GetRow(1);
Assert.IsNotNull(secondRow.GetCell(0));
Assert.IsNull(secondRow.GetCell(2));
NPOI.SS.UserModel.IRow thirdRow = sheet.GetRow(2);
Assert.AreEqual(CellType.STRING, thirdRow.GetCell(0).CellType);
String cellValue = thirdRow.GetCell(0).StringCellValue;
Assert.AreEqual(testData[2][0].ToString(), cellValue);
Assert.AreEqual(CellType.FORMULA, thirdRow.GetCell(2).CellType);
Assert.AreEqual("A1+B2", thirdRow.GetCell(2).CellFormula);
}
/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int rowCount = 0;
int sheetCount = 1;
NPOI.SS.UserModel.ISheet newsheet = null;
//循环数据源导出数据集
newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
#region 样式声明
//格式化显示
NPOI.SS.UserModel.ICellStyle cellStyle_DateTime = excelWorkbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = excelWorkbook.CreateDataFormat();
cellStyle_DateTime.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
#endregion
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出10000条数据 创建新的工作簿
if (rowCount == 5000)
{
rowCount = 1;
sheetCount++;
newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
}
NPOI.SS.UserModel.IRow newRow = newsheet.CreateRow(rowCount);
InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, cellStyle_DateTime);
}
}
/// <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>
/// 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);
//写入到客户端
}
/// <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>
/// 批量导出本校第一批派位学生
/// </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);
}
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 string TM_DAOCHU(string data)
{
string token = AppClass.GetSession("token").ToString();
int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID"));
DaoRuMsg msg = new DaoRuMsg();
try
{
MES_TM_TMINFO_LIST[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <MES_TM_TMINFO_LIST[]>(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++)
{
NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1);
row.CreateCell(0).SetCellValue(model[i].TM);
row.CreateCell(1).SetCellValue(model[i].WLH);
row.CreateCell(2).SetCellValue(model[i].WLMS);
row.CreateCell(3).SetCellValue(model[i].PC);
row.CreateCell(4).SetCellValue(model[i].NOBILL);
row.CreateCell(5).SetCellValue(model[i].NOBILLMX);
row.CreateCell(6).SetCellValue(Convert.ToDouble(model[i].DCSLBS.ToString()));
row.CreateCell(7).SetCellValue(Convert.ToDouble(model[i].DCSLMBSL.ToString()));
row.CreateCell(8).SetCellValue(Convert.ToDouble(model[i].ALLBOXCOUNT.ToString()));
row.CreateCell(9).SetCellValue(model[i].GC);
row.CreateCell(10).SetCellValue(model[i].KCDDNAME);
row.CreateCell(11).SetCellValue(model[i].WLPZND);
row.CreateCell(12).SetCellValue(model[i].WLPZBH);
row.CreateCell(13).SetCellValue(model[i].WLPZHXMH);
row.CreateCell(14).SetCellValue(Convert.ToDouble(model[i].RESDUESL.ToString()));
row.CreateCell(15).SetCellValue(Convert.ToDouble(model[i].SL.ToString()));
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));
}
}
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);
}
protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "客户编号", CellType.String, cellStyle);
CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
CreateCell(row, 2, "类别", CellType.String, cellStyle);
CreateCell(row, 3, "综合等级", CellType.String, cellStyle);
CreateCell(row, 4, "需求量等级", CellType.String, cellStyle);
CreateCell(row, 5, "活跃程度", CellType.String, cellStyle);
CreateCell(row, 6, "忠诚度", CellType.String, cellStyle);
CreateCell(row, 7, "主要产品", CellType.String, cellStyle);
CreateCell(row, 8, "业务员", CellType.String, cellStyle);
CreateCell(row, 9, "联系人", CellType.String, cellStyle);
CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle);
CreateCell(row, 11, "最近周预估", CellType.String, cellStyle);
CreateCell(row, 12, "最近月预估", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.CompanyEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.fullname);
CreateCell(row, 2, model.type);
CreateCell(row, 3, model.generallevel);
CreateCell(row, 4, model.requiredlevel);
CreateCell(row, 5, model.managestandard);
CreateCell(row, 6, model.activelevel);
CreateCell(row, 7, model.products);
CreateCell(row, 8, model.salesman);
CreateCell(row, 9, model.linkman);
CreateCell(row, 10, model.currentlink);
CreateCell(row, 11, model.currentweekestimate);
CreateCell(row, 12, model.currentmonthestimate);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
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();
}
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);
}
protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "通话记录表", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "记录单号", CellType.String, cellStyle);
CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
CreateCell(row, 2, "联系人", CellType.String, cellStyle);
CreateCell(row, 3, "移动电话", CellType.String, cellStyle);
CreateCell(row, 4, "固定电话", CellType.String, cellStyle);
CreateCell(row, 5, "客户等级", CellType.String, cellStyle);
CreateCell(row, 6, "日期", CellType.String, cellStyle);
CreateCell(row, 7, "沟通内容", CellType.String, cellStyle);
CreateCell(row, 8, "品质要求", CellType.String, cellStyle);
CreateCell(row, 9, "主要产品", CellType.String, cellStyle);
CreateCell(row, 10, "估计周用量", CellType.String, cellStyle);
CreateCell(row, 11, "估计月用量", CellType.String, cellStyle);
CreateCell(row, 12, "地址", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.CallRecordsEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.customer);
CreateCell(row, 2, model.linkman);
CreateCell(row, 3, model.mobile);
CreateCell(row, 4, model.telephone);
CreateCell(row, 5, model.customerlevel);
CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd"));
CreateCell(row, 7, model.communicatecontent);
CreateCell(row, 8, model.requiredquantity);
CreateCell(row, 9, model.products);
CreateCell(row, 10, model.weekestimate);
CreateCell(row, 11, model.monthestimate);
CreateCell(row, 12, model.address);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
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);
}
}
private static void SetJZDRowValue(NPOI.SS.UserModel.IRow row, JZD jzd)
{
IPoint point = jzd.Feature.Shape as IPoint;
row.GetCell(1).SetCellValue("J" + jzd.JZDH);
row.GetCell(2).SetCellValue(point.X);
row.GetCell(3).SetCellValue(point.Y);
}
public FileResult ShunFengExcel()
{
try
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ExamUserInfoModel models = new ExamUserInfoModel();
models.GetUserInfo(null);
//获取list数据
var tlst = models.ListUserInfo;
//给sheet1添加第一行的头部标题
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("职位");
row1.CreateCell(5).SetCellValue("本职等对应技能等级");
row1.CreateCell(6).SetCellValue("已经考取技能等级");
row1.CreateCell(7).SetCellValue("最近一次理论考试成绩");
row1.CreateCell(8).SetCellValue("最近一次通过理论时间");
row1.CreateCell(9).SetCellValue("最近一次实践成绩");
row1.CreateCell(10).SetCellValue("最近一次实践考核通过时间");
row1.CreateCell(11).SetCellValue("最高可考技能等级");
row1.CreateCell(12).SetCellValue("可申请技能等级");
row1.CreateCell(13).SetCellValue("最近一次绩效成绩要求");
//将数据逐步写入sheet1各个行
for (int i = 0; i < tlst.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode); //工号
rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName); //姓名
rowtemp.CreateCell(2).SetCellValue(tlst[i].EntryDate.ToString()); //入职日期
rowtemp.CreateCell(3).SetCellValue(tlst[i].RankName); //职等
rowtemp.CreateCell(4).SetCellValue(tlst[i].PostName); //职位
rowtemp.CreateCell(5).SetCellValue(tlst[i].SkillLevel); //本职等对应技能等级
rowtemp.CreateCell(6).SetCellValue(tlst[i].CurrentSkillLevel); //已经考取技能等级
rowtemp.CreateCell(7).SetCellValue(tlst[i].ExamScore); //最近一次理论考试成绩
rowtemp.CreateCell(8).SetCellValue(tlst[i].LastExamTime.ToString()); //最近一次通过理论时间
rowtemp.CreateCell(9).SetCellValue(tlst[i].TheoreticalAchievement.ToString()); //最近一次实践成绩
rowtemp.CreateCell(10).SetCellValue(tlst[i].PracticeTime.ToString()); //最近一次实践考核通过时间
rowtemp.CreateCell(11).SetCellValue(tlst[i].HighestTestSkill); //最高可考技能等级
rowtemp.CreateCell(12).SetCellValue(tlst[i].ApplicationLevel); //可申请技能等级
rowtemp.CreateCell(13).SetCellValue(tlst[i].Achievement); //最近一次绩效成绩要求
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "人员信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 将Excel的行转化为IEnumerable
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public static IEnumerable <ICell> GetRowIEnumerable(NPOI.SS.UserModel.IRow row)
{
var itor = row.Cells.GetEnumerator(); //.GetCellEnumerator();
while (itor.MoveNext())
{
yield return((ICell)itor.Current);
}
}
public FileResult ExportExcel(string pagetype, string name, string batchNumber, int StorageID = -1, int customerID = -1)
{
int count = InventoryService.GetInventoryCount(name, batchNumber, StorageID, customerID);
PagerInfo pager = new PagerInfo();
pager.PageIndex = 1;
pager.PageSize = count;
pager.SumCount = count;
//获取list数据
List <InventoryEntity> list = InventoryService.GetInventoryInfoByRule(name, batchNumber, StorageID, customerID, pager);
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
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("规格型号");
row1.CreateCell(5).SetCellValue("单位");
row1.CreateCell(6).SetCellValue("批次号");
row1.CreateCell(7).SetCellValue("生产日期");
row1.CreateCell(8).SetCellValue("保质期");
row1.CreateCell(9).SetCellValue("数量");
row1.CreateCell(10).SetCellValue("可用库存数量");
row1.CreateCell(11).SetCellValue("待出库数量");
row1.CreateCell(12).SetCellValue("重量");
row1.CreateCell(13).SetCellValue("出入库日期");
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].customer != null ? list[i].customer.CustomerName : "");
rowtemp.CreateCell(1).SetCellValue(list[i].storages != null ? list[i].storages.StorageName : "");
rowtemp.CreateCell(2).SetCellValue(list[i].goods != null ? list[i].goods.GoodsNo : "");
rowtemp.CreateCell(3).SetCellValue(list[i].goods != null ? list[i].goods.GoodsName : "");
rowtemp.CreateCell(4).SetCellValue(list[i].goods != null ? list[i].goods.GoodsModel : "");
rowtemp.CreateCell(5).SetCellValue(list[i].goods != null ? list[i].goods.Units : "");
rowtemp.CreateCell(6).SetCellValue(list[i].BatchNumber);
rowtemp.CreateCell(7).SetCellValue(list[i].ProductDate.ToShortDateString());
rowtemp.CreateCell(8).SetCellValue(list[i].goods != null ? list[i].goods.exDate + list[i].goods.exUnits : "");
rowtemp.CreateCell(9).SetCellValue(list[i].Quantity);
rowtemp.CreateCell(10).SetCellValue(list[i].CanUseQuantity);
rowtemp.CreateCell(11).SetCellValue(list[i].WaitQuantity);
rowtemp.CreateCell(12).SetCellValue(list[i].goods != null ? list[i].goods.Weight : "");
rowtemp.CreateCell(13).SetCellValue(list[i].InventoryDate.ToShortDateString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", getFileNames(list, customerID, StorageID)));
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="importPath">导入文件路径</param>
/// <param name="headerIndex">表头所在行索引 （兼容第一行为大标题的情况）</param>
public static DataSet ImportExcel(string importPath, int headerIndex = 0)
{
DataSet ds = new DataSet();
DataTable dt = null;
FileStream fs = new FileStream(importPath, FileMode.Open, FileAccess.Read);
IWorkbook book;
if (Path.GetExtension(importPath) == "xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
}
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(headerIndex); //从第0行开始取 列头
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = headerIndex + 1; i <= sheet.LastRowNum; i++) //从第1行开始取数据
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
var cell = sheet.GetRow(i).GetCell(j);
cell.SetCellType(CellType.String);
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
return(ds);
}
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));
}
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
}
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
}
/// <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);
}
/// <summary>
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
/// </summary>
/// <param name="rowCounter">The 0 based row number</param>
/// <param name="sheet">The sheet that the row is part of.</param>
/// <returns>The row indicated by the rowCounter</returns>
public static NPOI.SS.UserModel.IRow GetRow(int rowCounter, HSSFSheet sheet)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return(row);
}