void ConvertToDataTable()
{
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
SetData(dt);
}
private void GetSalaryList(ISheet mainSheet)
{
FileStream salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
XSSFWorkbook salaryWorkbook = new XSSFWorkbook(salaryFileStream);
XSSFSheet salarySheet = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");
for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
{
XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
if (sRow != null && tRow != null)
{
for (int j = 0; j < 2; j++)
{
XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
if (sCell == null)
{
break;
}
string cellValue = sCell.ToString();
XSSFCell tCell = (XSSFCell)tRow.CreateCell(j);
//CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);
XSSFCellStyle style = (XSSFCellStyle)sCell.CellStyle;
XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
XSSFColor color = null;
if (style.FillForegroundColorColor != null)
{
byte[] pa = style.FillForegroundColorColor.RGB;
string key = pa[0] + "," + pa[1] + "," + pa[2];
if (dictionary.ContainsKey(key))
{
style1.FillForegroundColor = dictionary[key];
}
else
{
Console.WriteLine("找不到该颜色!" + key);
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
}
else
{
Console.WriteLine("找不到该颜色!");
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
//byte[] pa1 = style.FillBackgroundColorColor.RGB;
//style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
style1.FillPattern = sCell.CellStyle.FillPattern;
//style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
tCell.CellStyle = style1;
tCell.SetCellValue(cellValue);
}
}
}
salaryFileStream.Close();
salaryWorkbook.Close();
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2007ToString(string path)
{
XSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
/*
* ②：将文档保存到指定路径
*/
string destFileName = @"D:\test.xlsx";
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
sb.Append("</table>");
return(sb.ToString());
}
/// <summary>
/// 文件为xlsx 2007
/// </summary>
/// <param name="row"></param>
/// <param name="dr"></param>
/// <param name="i"></param>
private static void GetCellValue(XSSFRow row, DataRow dr, int i)
{
XSSFCell cell = row.GetCell(i) as XSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
dr[i] = null;
break;
case CellType.Boolean:
dr[i] = cell.BooleanCellValue;
break;
case CellType.Numeric:
////This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
if (DateTime.Compare(cell.DateCellValue, DateTime.Parse("1900-01-01")) > 0)
{
dr[i] = cell.DateCellValue;
}
else
{
dr[i] = cell.ToString();
}
//if (HSSFDateUtil.IsCellDateFormatted(cell))
//{
// dr[i] = cell.DateCellValue;
//}
//if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
//{
// dr[i] = cell.NumericCellValue;
//}
break;
case CellType.String:
dr[i] = cell.StringCellValue;
break;
case CellType.Error:
dr[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
default:
dr[i] = cell.NumericCellValue;
break;
}
}
}
//处理excel2007
private static void FillDataRowByHSSFRow(XSSFRow row, XSSFFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
XSSFCell cell = row.GetCell(j) as XSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = DBNull.Value;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cell = evaluator.EvaluateInCell(cell) as XSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
}
/// <summary>读取excel2007
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
private static DataTable Import2007(string strFileName)
{
//2013/10/22 夏梁峰 add begin
//修改使用NPOI读取excel
//需求编号：OTS_SZDX_01_R00027_D00001
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
DataTable dt = new DataTable();
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
//2013/10/22 夏梁峰 add end
//2013/10/22 夏梁峰 删除
//string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
//OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM [Sheet1$]";
//myConn.Open();
//OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//DataSet myDataSet = new DataSet();
//myCommand.Fill(myDataSet, "[Sheet1$]");
//myConn.Close();
//return myDataSet.Tables[0];
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable Read2007ToTable(string path)
{
XSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
bool firstr = true;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
#region 第一行，初始化dt
if (firstr)
{
for (int j = 0; j < row.LastCellNum; j++)
{
dt.Columns.Add("column" + j);
}
firstr = false;
}
#endregion
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
DataRow dr = dt.NewRow();
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
XSSFWorkbook XSSFworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
XSSFworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)XSSFworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
// <summary>
/// Excel某sheet中内容导入到DataTable中
/// 区分xsl和xslx分别处理
/// </summary>
/// <param name="filePath">Excel文件路径,含文件全名</param>
/// <param name="sheetName">此Excel中sheet名</param>
/// <returns></returns>
public static DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
{
DataTable dt = new DataTable();
if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
{//.xls
#region .xls文件处理:HSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0)
{
continue; //The firt row is title,no need import
}
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
else
{//.xlsx
#region .xlsx文件处理:XSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0)
{
continue; //The firt row is title,no need import
}
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
return(dt);
}
/// <summary>读取excel
/// 根据索引读取Sheet表数据，默认读取第一个sheet
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetIndex">sheet表的索引，从0开始</param>
/// <returns>数据集</returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
}
return(dt);
}
/// <summary>读取excel
/// 根据索引读取Sheet表数据，默认读取第一个sheet
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetIndex">sheet表的索引，从0开始</param>
/// <returns>数据集</returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0, ProgressBar prb = null)
{
DataTable dt = new DataTable();
try
{
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
if (cell != null)
{
var columnName = cell.ToString();
foreach (var column in dt.Columns)
{
if (column.ToString() == cell.ToString())
{
columnName = columnName + j;
break;
}
}
dt.Columns.Add(columnName, typeof(string));
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
int pro = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(i / 100)));
if (i == pro)
{
prb.Value += 1;
}
HSSFRow row = (HSSFRow)sheet.GetRow(i);
if (row == null)
{
continue;
}
if (IsEmptyRow(row, null))//一整行都是空值过滤掉
{
continue;
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
if (cell != null)
{
var columnName = cell.ToString();
foreach (var column in dt.Columns)
{
if (column.ToString() == cell.ToString())
{
columnName = columnName + j;
break;
}
}
dt.Columns.Add(columnName, typeof(string));
}
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
if (row == null)
{
continue;
}
if (IsEmptyRow(null, row))//一整行都是空值过滤掉
{
continue;
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(dt);
}
public static void CreateInventoryReport(string template, string savepath, List <string> datas)
{
XSSFWorkbook hssfworkbook;
var fpath = template;// HttpContext.Current.Server.MapPath(template);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int irow = 0;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
//customercode,customername,logourl,materialcode materialname,materialmodel,materialunit,formonth beginamount,begincost,inamount,incost,
//outamount,outcost,endamount,endcost,beginlocalamount,beginlocalcost,endlocalamount,endlocalcost
dr = cell.ToString();
string strrow = datas.FirstOrDefault(p => p.Contains("|M001|"));
string[] array = strrow.Split('|');
if (dr.Contains("$title$"))
{
cell.SetCellValue(dr.Replace("$title$", array[0]));
}
if (dr.Contains("$number$"))
{
cell.SetCellValue(dr.Replace("$number$", array[1]));
}
//if (dr.Contains("$customername$")) cell.SetCellValue(dr.Replace("$customername$", customername));
//if (dr.Contains("$starttime$")) cell.SetCellValue(dr.Replace("$starttime$", starttime));
//if (dr.Contains("$completetime$")) cell.SetCellValue(dr.Replace("$completetime$", completetime));
//if (dr.Contains("$content$")) cell.SetCellValue(dr.Replace("$content$", content));
//if (dr.Contains("$checkperson$")) cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
//if (dr.Contains("$checktime$")) cell.SetCellValue(dr.Replace("$checktime$", checktime));
//if (dr.Contains("$checkcontent$")) cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
}
}
}
#region 另存为excel
var destFileName = savepath;// HttpContext.Current.Server.MapPath(savepath);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
#endregion
#region excel转化成pdf
#endregion
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static void CreateServiceregulatory(string template, string savepath, string title,
string customer, string starttime, string serviceperson, string completetime, string content,
string checktime, string checkperson, string checkcontent
)
{
XSSFWorkbook hssfworkbook;
var fpath = HttpContext.Current.Server.MapPath(template);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int irow = 0;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
if (dr.Contains("$title$"))
{
cell.SetCellValue(dr.Replace("$title$", title));
}
if (dr.Contains("$customer$"))
{
cell.SetCellValue(dr.Replace("$customer$", customer));
}
if (dr.Contains("$serviceperson$"))
{
cell.SetCellValue(dr.Replace("$serviceperson$", serviceperson));
}
if (dr.Contains("$starttime$"))
{
cell.SetCellValue(dr.Replace("$starttime$", starttime));
}
if (dr.Contains("$completetime$"))
{
cell.SetCellValue(dr.Replace("$completetime$", completetime));
}
if (dr.Contains("$content$"))
{
cell.SetCellValue(dr.Replace("$content$", content));
}
if (dr.Contains("$checkperson$"))
{
cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
}
if (dr.Contains("$checktime$"))
{
cell.SetCellValue(dr.Replace("$checktime$", checktime));
}
if (dr.Contains("$checkcontent$"))
{
cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
}
}
}
}
#region 另存为excel
var destFileName = HttpContext.Current.Server.MapPath(savepath);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
#endregion
#region excel转化成pdf
#endregion
}