public async Task <ActionResult> ImportAsync()
{
IFormFile file = Request.Form.Files[0];
string folderName = "UploadExcel";
string webRootPath = hostEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
var errorDictionary = new Dictionary <int, string>();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
var newProduct = new ProductInputModel();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
string currentRow = string.Empty;
if (row.GetCell(j) != null)
{
currentRow = row.GetCell(j).ToString().TrimEnd();
}
switch (j)
{
case 0:
if (currentRow != "")
{
newProduct.Name = currentRow;
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 1:
if (currentRow != "")
{
newProduct.ShortName = currentRow;
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 2:
if (this.numbersChecker.WholeNumberCheck(currentRow))
{
newProduct.BrandexId = int.Parse(currentRow);
}
else
{
errorDictionary[i] = currentRow;
}
break;
case 3:
if (currentRow != "")
{
newProduct.PhoenixId = int.Parse(currentRow);
}
break;
case 4:
if (currentRow != "")
{
newProduct.PharmnetId = int.Parse(currentRow);
}
break;
case 5:
if (currentRow != "")
{
newProduct.StingId = int.Parse(currentRow);
}
break;
case 6:
if (currentRow != "")
{
newProduct.SopharmaId = currentRow;
}
break;
case 7:
if (numbersChecker.NegativeNumberIncludedCheck(currentRow))
{
newProduct.Price = double.Parse(currentRow);
}
else
{
errorDictionary[i] = currentRow;
}
break;
}
}
await this.productsService.CreateProduct(newProduct);
}
}
}
var productsErrorModel = new CustomErrorDictionaryOutputModel
{
Errors = errorDictionary
};
return(this.View(productsErrorModel));
}
public void TestGetFormattedCellValueHSSFCell()
{
// Valid date formats -- cell values should be date formatted & not "555.555"
IRow row = wb.GetSheetAt(0).GetRow(0);
IEnumerator it = row.GetEnumerator();
log("==== VALID DATE FORMATS ====");
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
String fmtval = formatter.FormatCellValue(cell);
log(fmtval);
// should not be equal to "555.555"
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
Assert.IsTrue(!"555.555".Equals(fmtval));
String fmt = cell.CellStyle.GetDataFormatString();
//assert the correct month form, as in the original Excel format
String monthPtrn = fmt.IndexOf("mmmm") != -1 ? "MMMM" : "MMM";
// this line is intended to compute how "July" would look like in the current locale
String jul = new SimpleDateFormat(monthPtrn).Format(new DateTime(2010, 7, 15), CultureInfo.CurrentCulture);
// special case for MMMMM = 1st letter of month name
if (fmt.IndexOf("mmmmm") > -1)
{
jul = jul.Substring(0, 1);
}
// check we found july properly
Assert.IsTrue(fmtval.IndexOf(jul) > -1, "Format came out incorrect - " + fmt);
}
row = wb.GetSheetAt(0).GetRow(1);
it = row.GetEnumerator();
log("==== VALID TIME FORMATS ====");
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
String fmt = cell.CellStyle.GetDataFormatString();
String fmtval = formatter.FormatCellValue(cell);
log(fmtval);
// should not be equal to "555.47431"
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
Assert.IsTrue(!"555.47431".Equals(fmtval));
// check we found the time properly
Assert.IsTrue(fmtval.IndexOf("11:23") > -1, "Format came out incorrect - " + fmt);
}
// Test number formats
row = wb.GetSheetAt(0).GetRow(1);
it = row.GetEnumerator();
log("\n==== VALID NUMBER FORMATS ====");
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
log(formatter.FormatCellValue(cell));
// should not be equal to "1234567890.12345"
Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell)));
}
// Test bad number formats
row = wb.GetSheetAt(0).GetRow(3);
it = row.GetEnumerator();
log("\n==== INVALID NUMBER FORMATS ====");
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
log(formatter.FormatCellValue(cell));
// should be equal to "1234567890.12345"
// in some locales the the decimal delimiter is a comma, not a dot
string decimalSeparator = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator;
Assert.AreEqual("1234567890" + decimalSeparator + "12345", formatter.FormatCellValue(cell));
}
// Test Zip+4 format
row = wb.GetSheetAt(0).GetRow(4);
ICell cell1 = row.GetCell(0);
log("\n==== ZIP FORMAT ====");
log(formatter.FormatCellValue(cell1));
Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell1));
// Test phone number format
row = wb.GetSheetAt(0).GetRow(5);
cell1 = row.GetCell(0);
log("\n==== PHONE FORMAT ====");
log(formatter.FormatCellValue(cell1));
Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell1));
// Test SSN format
row = wb.GetSheetAt(0).GetRow(6);
cell1 = row.GetCell(0);
log("\n==== SSN FORMAT ====");
log(formatter.FormatCellValue(cell1));
Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell1));
// null Test-- null cell should result in empty String
Assert.AreEqual(formatter.FormatCellValue(null), "");
// null Test-- null cell should result in empty String
Assert.AreEqual(formatter.FormatCellValue(null), "");
}
/// <summary>
/// 读取2003及以前版本.xls
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable Read2003ToTable(string path)
{
HSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
/*
* ②：将文档保存到指定路径
*/
string destFileName = @"D:\test.xls";
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
DataTable dt = new DataTable(); for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
bool firstr = true; while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)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++)
{
HSSFCell cell = (HSSFCell)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>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="isFirstRowColumn"></param>
/// <param name="columnTemplate"></param>
/// <param name="requireColumns"></param>
/// <param name="maxRows"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn, Dictionary <string, string[]> columnTemplate = null, string[] requireColumns = null, int?maxRows = null)
{
ISheet sheet = null;
DataTable data = new DataTable();
IWorkbook workbook = null;
int startRow = 0;
try
{
using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
try
{
workbook = new XSSFWorkbook(fs);
}
catch
{
workbook = new HSSFWorkbook(fs);
}
}
if (sheetName != null)
{
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
}
else
{
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
}
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
string cellValue = cell?.StringCellValue?.Trim();
if (!string.IsNullOrWhiteSpace(cellValue))//列名正确性验证
{
if (columnTemplate != null && !columnTemplate.First().Value.Contains(cellValue))
{
throw new Exception($"{columnTemplate.First().Key}不存在列名：{cellValue}！正确列名为：{string.Join(",", columnTemplate.First().Value)}");
}
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
if (maxRows != null)
{
if (rowCount > maxRows)
{
throw new Exception($"请拆分文件，一次最多支持{maxRows}条数据");
}
}
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.Cells.Count == 0 || row.FirstCellNum == -1 || row.Cells.All(d => d.CellType == CellType.Blank))
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
var cellvalue = row.GetCell(j);
if (cellvalue == null || (cellvalue.ToString().Trim() == "0"))
{
if (requireColumns != null && requireColumns.Contains(data.Columns[j].ColumnName))
{
//throw new Exception($"第{i}行，第{j}列,【{data.Columns[j].ColumnName}】不能为空或0，必须填写！");
}
}
if (cellvalue != null)
{
dataRow[j] = cellvalue.ToString().Trim();
}
else
{
dataRow[j] = ""; //string.Empty;
}
}
data.Rows.Add(dataRow);
}
}
workbook?.Close();
return(data);
}
catch (Exception ex)
{
workbook?.Close();
throw new Exception(ex.Message);
}
}
public string ExportExcel(StoreCondition input, string path)
{
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
sqlParamters.Add(new SqlParameter("@Start", input.Start ?? ""));
sqlParamters.Add(new SqlParameter("@End", input.End ?? ""));
var dt = Repository.ExecuteQuery <TakeCheckOut>("EXEC sp_GetExportTakeCheck @name,@Start,@End", sqlParamters.ToArray());
//创建EXCEL工作薄
HSSFWorkbook workBook = null;
using (FileStream fs = File.Open(path + "/takelist.xls", FileMode.Open, System.IO.FileAccess.Read, FileShare.Read))
{
workBook = new HSSFWorkbook(fs);
fs.Close();
}
//创建sheet文件表
Sheet sheet = workBook.GetSheetAt(0);//workBook..CreateSheet("提现列表");
int r = 0;
Dictionary <string, object> dict = new Dictionary <string, object>();
//创建表头
if (dt.Count() > 0)
{
Expression <Func <TakeCheck> > properties = (() => new TakeCheck()
{
Id = 0,
StoreName = "",
TakeOut = 0,
Account = "",
Bank = "",
BankNo = "",
CreateTime = DateTime.Now,
ExportCount = 0,
ConfirmTime = DateTime.Now
});
dict = properties.GetPropertyWithValue();
//Cell cell = header.CreateCell(r);
// cell.SetCellValue("头部");
//foreach (var item in dict.Keys)
//{
// string colunname = GetColumnName(item);
// if (colunname != "")
// {
// Cell cell = header.CreateCell(r);
// cell.SetCellValue(colunname);
// r++;
// }
//}
}
else
{
Row header = sheet.CreateRow(2);
Cell cell = header.CreateCell(0);
cell.SetCellValue("数据为空");
}
#region 设置字体
Font font = workBook.CreateFont(); //创建字体样式
font.Color = HSSFColor.RED.index; //设置字体颜色
CellStyle style = workBook.CreateCellStyle(); //创建单元格样式
style.SetFont(font); //设置单元格样式中的字体样式
#endregion
Row row;
Dictionary <string, string> di;
//数据
for (var i = 0; i < dt.Count(); i++)
{
row = sheet.CreateRow(i + 1);
r = 0;
TakeCheckOut model = dt.ElementAt(i);
di = ExpressionHelper.GetProperties <TakeCheckOut>(model);
foreach (var item in dict.Keys)
{
var cell = row.CreateCell(r);
object obj = di[item];
if (obj != null)
{
cell.SetCellValue(obj.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
if (item == "ExportCount")
{
var v = 0;
if (int.TryParse(obj.ToString(), out v) && v > 0)
{
cell.CellStyle = style;//为单元格设置显示样式
}
}
if (item == "ConfirmTime")
{
cell.CellStyle = style;
}
r++;
}
}
//转为字节数组
var stream = new MemoryStream();
workBook.Write(stream);
path += System.Guid.NewGuid().GetHashCode().ToString("x") + ".xls";
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
byte[] data = stream.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
stream.Close();
stream.Dispose();
if (File.Exists(path))
{
Update(dt);
return(path);
}
else
{
return(string.Empty);
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcel(DataTable dt, string sheetName, bool isColumnWritten, bool Append = true)
{
try
{
HSSFWorkbook Workbook = NPOIOpenExcel(FileName);//打开工作薄
ISheet sheet = null;
if (sheetName != null)
{
sheet = Workbook.GetSheet(sheetName);
}
if (sheet == null)
{
sheet = Workbook.GetSheetAt(0);
}
if (sheet == null)
{
return(0);
}
int nStartRow = sheet.LastRowNum + 1;
if (!Append)
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
sheet.RemoveRow(row);
}
}
nStartRow = 1;
}
//是否写入第一行
if (isColumnWritten)
{
IRow FirstRow = sheet.CreateRow(0);
int K = 0;
foreach (var it in dt.Columns)
{
FirstRow.CreateCell(K++).SetCellValue(it.ToString());
}
}
//不加第一行
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
IRow row = sheet.CreateRow(nStartRow + i);
for (int j = 0; j < dt.Columns.Count; j++) //写一行的数据
{
row.CreateCell(j).SetCellValue(dr[j].ToString());
}
}
WriteToFile(Workbook, FileName);
}
catch (Exception ex)
{
throw new Exception($"将DataTable写入到Excel文件出错:{ex.Message}");
}
return(1);
}
protected Sheet GetSheet()
{
return(workbook.GetSheetAt(0));
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4)
{
continue;
}
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4)
{
continue;
}
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.Sheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetCellEnumerator();
while (cIt.MoveNext())
{
Cell cell = (HSSFCell)cIt.Current;
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}
private void ProcessSheet5(HSSFWorkbook workbook, int fromYear, int toYear)
{
ISheet sheet = workbook.GetSheetAt(5);
var startDatePeriod = new DateTime(2016, 4, 1);
var endDatePeriod = new DateTime(toYear, 3, DateTime.DaysInMonth(toYear, 3));
var query = from state in stateHistories
where state.EffectiveDate >= startDatePeriod && state.EffectiveDate <= endDatePeriod
select state;
var countATL = query.Where(s => s.StateCode == MutationCodes.ATL).Count();
var countATIOT = query.Where(s => s.StateCode == MutationCodes.ATIOT).Count();
var countATP2 = query.Where(s => s.StateCode == MutationCodes.ATP2).Count();
sheet.GetRow(10).GetCell(2).SetCellValue(countATL);
sheet.GetRow(11).GetCell(2).SetCellValue(countATIOT);
sheet.GetRow(12).GetCell(2).SetCellValue(countATP2);
var countAPA = members.Where(s => s.WhenFlagApa.HasValue).Count();
sheet.GetRow(17).GetCell(2).SetCellValue(countAPA);
var countATIS = query.Where(s => s.StateCode == MutationCodes.ATIS).Count();
var countATPS = query.Where(s => s.StateCode == MutationCodes.ATPS).Count();
var countATP1 = query.Where(s => s.StateCode == MutationCodes.ATP1).Count();
sheet.GetRow(23).GetCell(2).SetCellValue(countATIS);
sheet.GetRow(24).GetCell(2).SetCellValue(countATPS);
sheet.GetRow(25).GetCell(2).SetCellValue(countATP1);
var countExDKH123 = query.Where(s => new[] { MutationCodes.ExDKH1, MutationCodes.ExDKH2, MutationCodes.ExDKH3 }.Contains(s.StateCode)).Count();
var countExDKH4 = query.Where(s => s.StateCode == MutationCodes.ExDKH4).Count();
sheet.GetRow(31).GetCell(2).SetCellValue(countExDKH123);
sheet.GetRow(32).GetCell(2).SetCellValue(countExDKH4);
var countATD1 = query.Where(s => s.StateCode == MutationCodes.ATD1).Count();
var countATD2 = query.Where(s => s.StateCode == MutationCodes.ATD2).Count();
sheet.GetRow(38).GetCell(2).SetCellValue(countATD1);
sheet.GetRow(39).GetCell(2).SetCellValue(countATD2);
var countAKK1 = query.Where(s => s.StateCode == MutationCodes.AKK1).Count();
var countAKK2 = query.Where(s => s.StateCode == MutationCodes.AKK2).Count();
var countAKK3 = query.Where(s => s.StateCode == MutationCodes.AKK3).Count();
sheet.GetRow(47).GetCell(2).SetCellValue(countAKK1);
sheet.GetRow(48).GetCell(2).SetCellValue(countAKK2);
sheet.GetRow(49).GetCell(2).SetCellValue(countAKK3);
var countDKH1 = query.Where(s => s.StateCode == MutationCodes.DKH1).Count();
var countDKH2 = query.Where(s => s.StateCode == MutationCodes.DKH2).Count();
var countDKH3 = query.Where(s => s.StateCode == MutationCodes.DKH3).Count();
var countDKH4 = query.Where(s => s.StateCode == MutationCodes.DKH4).Count();
sheet.GetRow(56).GetCell(2).SetCellValue(countDKH1);
sheet.GetRow(57).GetCell(2).SetCellValue(countDKH2);
sheet.GetRow(58).GetCell(2).SetCellValue(countDKH3);
sheet.GetRow(59).GetCell(2).SetCellValue(countDKH4);
var countAKM1 = query.Where(s => s.StateCode == MutationCodes.AKM1).Count();
var countAKM2 = query.Where(s => s.StateCode == MutationCodes.AKM2).Count();
sheet.GetRow(66).GetCell(2).SetCellValue(countAKM1);
sheet.GetRow(67).GetCell(2).SetCellValue(countAKM2);
}
private void ProcessSheet3(HSSFWorkbook workbook, int fromYear, int toYear)
{
fromYear = 2016;
DateTime startDatePeriod = GetStartDatePeriod(fromYear);
DateTime endDatePeriod = GetEndDatePeriod(toYear);
ISheet sheet = workbook.GetSheetAt(3);
int rowIndex = 0;
sheet.GetRow(1).GetCell(17).SetCellValue(DateTime.Today);
foreach (Member member in members)
{
IRow currentRow = sheet.GetRow(5 + rowIndex);
// MemberNo
currentRow.GetCell(1).SetCellValue(member.MemberNo);
// Nama
currentRow.GetCell(2).SetCellValue(member.Name);
// Alamat
currentRow.GetCell(3).SetCellValue(member.Address);
// No. Telp
currentRow.GetCell(6).SetCellValue(member.HomePhone);
// No. HP
currentRow.GetCell(7).SetCellValue(member.CellPhone1);
// Wilayah
if (member.RegionId.HasValue)
{
string regionCode = member.Region.Code;
string wilayah = regionCode.Any(chr => chr == '-') ? member.Region.Code.Split('-')[0] : regionCode;
int wilayahInt;
if (Int32.TryParse(wilayah, out wilayahInt))
{
currentRow.GetCell(8).SetCellValue(wilayahInt.ToRoman());
}
}
// Gender
currentRow.GetCell(9).SetCellValue(member.Gender == "L" ? "P" : "W");
// Gol Darah
if (!String.IsNullOrEmpty(member.BloodType?.Trim()))
{
string rhesus = String.IsNullOrEmpty(member.Rhesus) ? "+" : member.Rhesus;
currentRow.GetCell(10).SetCellValue(String.Format("{0}{1}", member.BloodType, rhesus));
}
// Status Anggota
currentRow.GetCell(11).SetCellValue(member.ChrismationDate.HasValue ? "S" : "B");
// Umur tidak perlu dihitung karena sudah ada formulanya di Excel
//if(!member.DeceasedDate.HasValue)
// currentRow.GetCell(12).SetCellValue(member.BirthDate.HasValue ? (DateTime.Today.Year - member.BirthDate.Value.Year).ToString() : "");
// Pendidikan
string pendidikan = member.EducationGrade?.Name;
if (!String.IsNullOrEmpty(pendidikan))
{
currentRow.GetCell(13).SetCellValue(pendidikan);
}
// Pekerjaan
int? jobId = member.JobId;
string pekerjaan = String.Empty;
if (jobId.HasValue)
{
Job job = context.Jobs.SingleOrDefault(j => j.Id == jobId.Value);
if (job != null)
{
pekerjaan = job.Name ?? String.Empty;
}
currentRow.GetCell(14).SetCellValue(pekerjaan);
}
// Kelompok Etnis
string etnis = GetKlasisMappingValue(member.EthnicId.GetValueOrDefault(), DBAJMappingEnum.Ethnic);
if (!String.IsNullOrEmpty(etnis))
{
currentRow.GetCell(15).SetCellValue(etnis);
}
// Lahir
if (member.BirthDate.HasValue)
{
currentRow.GetCell(16).SetCellValue(member.BirthDate.Value);
}
// Baptis Dewasa / Anak
if (!String.IsNullOrEmpty(member.ChrismationType))
{
if (member.ChrismationType == "B" && member.ChrismationDate.HasValue)
{
currentRow.GetCell(17).SetCellValue(member.ChrismationDate.Value);
}
}
else
{
if (member.ChildhoodBaptizedDate.HasValue)
{
currentRow.GetCell(17).SetCellValue(member.ChildhoodBaptizedDate.Value);
}
}
// Sidi
if (!String.IsNullOrEmpty(member.ChrismationType) && member.ChrismationType == "S" && member.ChrismationDate.HasValue)
{
currentRow.GetCell(18).SetCellValue(member.ChrismationDate.Value);
}
// Atestasi Masuk
if (member.JoinDate.HasValue)
{
currentRow.GetCell(19).SetCellValue(member.JoinDate.Value);
}
// Atestasi Keluar
if (member.ResignDate.HasValue)
{
currentRow.GetCell(20).SetCellValue(member.ResignDate.Value);
}
// Meninggal dunia
if (member.DeceasedDate.HasValue)
{
currentRow.GetCell(21).SetCellValue(member.DeceasedDate.Value);
}
IEnumerable <MemberStateHistoryModel> validStateHistories =
(from state in stateHistories
where state.MemberId == member.Id && state.EffectiveDate >= startDatePeriod && state.EffectiveDate <= endDatePeriod
select state).ToList();
if (validStateHistories != null)
{
// Tanggal DKH
MemberStateHistoryModel dkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("DKH"));
if (dkhState != null)
{
currentRow.GetCell(22).SetCellValue(dkhState.EffectiveDate);
}
// Ex DKH
MemberStateHistoryModel exDkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH"));
if (exDkhState != null)
{
currentRow.GetCell(23).SetCellValue(exDkhState.EffectiveDate);
}
// Ex DKH4
MemberStateHistoryModel exDkh4State = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH-4"));
if (exDkh4State != null)
{
currentRow.GetCell(24).SetCellValue(exDkh4State.EffectiveDate);
}
// Status DKH dan lainnya
int cellIndex = 25;
foreach (MemberStateHistoryModel stateHistory in validStateHistories)
{
currentRow.GetCell(cellIndex).SetCellValue(stateHistory.StateCode);
cellIndex++;
// insufficient column in the Excel template
if (cellIndex > 27)
{
break;
}
}
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones and ones that aren't used.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] isUsed = new bool[newPos.Length];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
isUsed[i] = false;
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Loop over all the cells in the file, and identify any user defined
// styles aren't actually being used (don't touch built-in ones)
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
foreach (IRow row in s)
{
foreach (ICell cellI in row)
{
HSSFCell cell = (HSSFCell)cellI;
short oldXf = cell.CellValueRecord.XFIndex;
isUsed[oldXf] = true;
}
}
}
// Mark any that aren't used as needing zapping
for (int i = 21; i < isUsed.Length; i++)
{
if (!isUsed[i])
{
// Un-used style, can be removed
zapRecords[i] = true;
newPos[i] = 0;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
// removing by index, because removing by object may delete
// styles we did not intend to (the ones that _were_ duplicated and not the duplicates)
int max = newPos.Length;
int removed = 0; // to adjust index after deletion
for (int i = 21; i < max; i++)
{
if (zapRecords[i + removed])
{
workbook.Workbook.RemoveExFormatRecord(i);
i--;
max--;
removed++;
}
}
// Finally, update the cells to point at their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
//IEnumerator rIt = s.GetRowEnumerator();
//while (rIt.MoveNext())
foreach (IRow row in s)
{
//HSSFRow row = (HSSFRow)rIt.Current;
//IEnumerator cIt = row.GetEnumerator();
//while (cIt.MoveNext())
foreach (ICell cell in row)
{
//ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
/// <summary>
/// 将excel转换为datable
/// </summary>
/// <param name="fileStream">excel文件流</param>
/// <param name="skip">忽略处理列数</param>
/// <returns></returns>
public static DataTable GetDataTable(Stream fileStream, int skip = 0)
{
DataTable dt = new DataTable();
try
{
if (fileStream == null)
{
return(dt);
}
IWorkbook workbook = new HSSFWorkbook(fileStream);
ISheet sheet = workbook.GetSheetAt(0);
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return(dt); //空Excel文件
}
int coluNum = firstRow.LastCellNum; //列数
//根据excel的列数定义列
for (int colInx = 0; colInx < coluNum; colInx++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + colInx).ToString());
}
int startRowInx = sheet.FirstRowNum + skip;
for (int rowInx = startRowInx; rowInx <= sheet.LastRowNum; rowInx++)
{
IRow sheetRow = sheet.GetRow(rowInx);
if (sheetRow == null || sheetRow.Cells.Count <= 3)
{
continue; //当excel行列数小于等于3则忽略
}
DataRow dataRow = dt.NewRow();
for (int colInx = 0; colInx < coluNum; colInx++)
{
ICell cell = sheetRow.GetCell(colInx);
if (cell == null)
{
continue;
}
if (cell.CellType == CellType.NUMERIC)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
dataRow[colInx] = cell.DateCellValue;
}
else//其他数字类型
{
dataRow[colInx] = cell.NumericCellValue;
}
}
else
{
dataRow[colInx] = cell.ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
catch (Exception ex)
{
//日志记录点
throw ex;
}
finally
{
fileStream.Close();
fileStream.Dispose();
}
}
private int ProcessTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(workbook);
int maxRows = sheet.LastRowNum + 1;
int result = Result.NO_EVALUATIONS_FOUND; // so far
String currentGroupComment = null;
for (int rowIndex = SS.START_TEST_CASES_ROW_INDEX; rowIndex < maxRows; rowIndex++)
{
IRow r = sheet.GetRow(rowIndex);
String newMarkerValue = GetMarkerColumnValue(r);
if (r == null)
{
continue;
}
if (SS.TEST_CASES_END_MARKER.Equals(newMarkerValue, StringComparisonShim.InvariantCultureIgnoreCase))
{
// normal exit point
return(result);
}
if (SS.SKIP_CURRENT_TEST_CASE_MARKER.Equals(newMarkerValue, StringComparisonShim.InvariantCultureIgnoreCase))
{
// currently disabled test case row
continue;
}
if (newMarkerValue != null)
{
currentGroupComment = newMarkerValue;
}
ICell c = r.GetCell(SS.COLUMN_INDEX_EVALUATION);
if (c == null || c.CellType != CellType.Formula)
{
continue;
}
ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
String rowComment = GetRowCommentColumnValue(r);
String msgPrefix = formatTestCaseDetails(this.Filename, sheetName, r.RowNum, c, currentGroupComment, rowComment);
try
{
CellValue actualValue = Evaluator.Evaluate(c);
ConfirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
_evaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (RuntimeException e)
{
_evaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
catch (AssertionException e)
{
_evaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
throw new Exception("Missing end marker '" + SS.TEST_CASES_END_MARKER
+ "' on sheet '" + sheetName + "'");
}
private bool ImportData(ShippingMethod method)
{
bool result = false;
int i = 1;
try
{
HSSFWorkbook workbook = new HSSFWorkbook(fileUpload.UploadedFiles[0].InputStream, true);
ISheet worksheet = workbook.GetSheetAt(0);
if (worksheet == null)
{
return(false);
}
int iFinished = 0;
bool shippingByGeoZone = (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed ||
method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal ||
method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight
);
List <ShippingTableRate> lstTableRates = new List <ShippingTableRate>();
if (shippingMethodId > 0)
{
lstTableRates = ShippingTableRate.GetByMethod(method.ShippingMethodId);
}
for (i = 1; i <= worksheet.LastRowNum; i++)
{
IRow dataRow = worksheet.GetRow(i);
if (dataRow != null)
{
string excelGeoZoneGuid = GetValueFromExcel(dataRow.GetCell(0)).Trim();
string excelShippingFee = GetValueFromExcel(dataRow.GetCell(2)).Trim();
string excelFromValue = GetValueFromExcel(dataRow.GetCell(3)).Trim();
string excelOverXValue = GetValueFromExcel(dataRow.GetCell(4)).Trim();
bool isValid = true;
if (string.IsNullOrEmpty(excelShippingFee))
{
isValid = false;
}
if (shippingByGeoZone)
{
if (excelGeoZoneGuid.Length != 36)
{
isValid = false;
}
if ((method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight) && string.IsNullOrEmpty(excelFromValue))
{
isValid = false;
}
}
else if (string.IsNullOrEmpty(excelFromValue))
{
isValid = false;
}
if (!isValid)
{
iFinished += 1;
if (iFinished >= 2)
{
break;
}
continue;
}
iFinished = 0;
var lstShippingFee = excelShippingFee.SplitOnChar('+');
var lstFromValue = excelFromValue.SplitOnChar('+');
Guid geoZoneGuid = Guid.Empty;
decimal shippingFee = Convert.ToDecimal(lstShippingFee[0]);
decimal fromValue = decimal.Zero;
decimal additionalFee = decimal.Zero;
decimal additionalValue = decimal.Zero;
if (shippingByGeoZone)
{
geoZoneGuid = new Guid(excelGeoZoneGuid);
if ((method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight))
{
fromValue = Convert.ToDecimal(lstFromValue[0]);
}
}
else
{
fromValue = Convert.ToDecimal(lstFromValue[0]);
}
if (lstShippingFee.Count == 2 &&
lstFromValue.Count == 2 &&
(method.ShippingProvider == (int)ShippingMethodProvider.ByWeight || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight))
{
additionalFee = Convert.ToDecimal(lstShippingFee[1]);
additionalValue = Convert.ToDecimal(lstFromValue[1]);
}
int shippingTableRateId = -1;
foreach (ShippingTableRate tblRate in lstTableRates)
{
if (shippingByGeoZone)
{
if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed)
{
if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid)
{
tblRate.MarkAsDeleted = false;
shippingTableRateId = tblRate.ShippingTableRateId;
break;
}
}
else if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal)
{
if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid && fromValue == tblRate.FromValue)
{
tblRate.MarkAsDeleted = false;
shippingTableRateId = tblRate.ShippingTableRateId;
break;
}
}
else if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight)
{
if (shippingFee == tblRate.ShippingFee && geoZoneGuid == tblRate.GeoZoneGuid && fromValue == tblRate.FromValue)
{
tblRate.MarkAsDeleted = false;
shippingTableRateId = tblRate.ShippingTableRateId;
break;
}
}
}
else
{
if (shippingFee == tblRate.ShippingFee && fromValue == tblRate.FromValue)
{
tblRate.MarkAsDeleted = false;
shippingTableRateId = tblRate.ShippingTableRateId;
break;
}
}
}
ShippingTableRate tableRate = null;
if (shippingTableRateId > 0)
{
tableRate = new ShippingTableRate(shippingTableRateId);
}
else
{
tableRate = new ShippingTableRate();
tableRate.ShippingMethodId = method.ShippingMethodId;
}
if (shippingByGeoZone)
{
tableRate.GeoZoneGuid = geoZoneGuid;
if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndOrderTotal || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight)
{
tableRate.FromValue = fromValue;
}
}
else
{
tableRate.FromValue = fromValue;
}
tableRate.FreeShippingOverXValue = 0;
if (method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndFixed || method.ShippingProvider == (int)ShippingMethodProvider.ByGeoZoneAndWeight)
{
if (!string.IsNullOrEmpty(excelOverXValue))
{
tableRate.FreeShippingOverXValue = Convert.ToDecimal(excelOverXValue);
}
}
tableRate.ShippingFee = shippingFee;
tableRate.AdditionalFee = additionalFee;
tableRate.AdditionalValue = additionalValue;
tableRate.Save();
result = true;
}
}
foreach (ShippingTableRate tblRate in lstTableRates)
{
if (tblRate.MarkAsDeleted)
{
ShippingTableRate.Delete(tblRate.ShippingTableRateId);
}
}
}
catch (Exception ex)
{
result = false;
message.ErrorMessage = ex.Message;
log.Error(ex.Message);
}
return(result);
}
/// <summary>
/// 使用第三方插件NPOI读取Excel内容到DataTable,默认读取Excel文件的第一个Sheet
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public DataTable GetDataFromExcel(string filePath)
{
ISheet sheet = null;;
if (filePath.ToLower().EndsWith("xlsx"))
{
XSSFWorkbook xssFWorkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
xssFWorkbook = new XSSFWorkbook(file);
}
sheet = xssFWorkbook.GetSheetAt(0);
}
else if (filePath.ToLower().EndsWith("xls"))
{
HSSFWorkbook hssfWorkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfWorkbook = new HSSFWorkbook(file);
}
sheet = hssfWorkbook.GetSheetAt(0);
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
int t = 0;
while (rows.MoveNext())
{
IRow row = rows.Current as IRow;
int cellCount = row.LastCellNum;
if (cellCount == -1)
{
continue;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (t == 0)
{
DataColumn column = null;
if (cell == null)
{
column = new DataColumn(string.Empty, typeof(string));
}
else
{
column = new DataColumn(cell.ToString().Trim(), typeof(string));
}
dt.Columns.Add(column);
}
else
{
if (i > dt.Columns.Count - 1)
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString().Trim();
}
}
}
if (t > 0)
{
dt.Rows.Add(dr);
}
t++;
}
return(dt);
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
for (int i = 21; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveExFormatRecord(
xfrs[i]
);
}
}
// Finally, update the cells to point at
// their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetCellEnumerator();
while (cIt.MoveNext())
{
Cell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
NPOI.SS.UserModel.CellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
/// <summary>
/// 将excel中的数据集转成datatable集合
/// </summary>
/// <param name="excelFileStream">excel文件流</param>
/// <param name="sheetIndex">起始标签页码：从0开始</param>
/// <returns></returns>
public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, out string strMsg)
{
try
{
strMsg = string.Empty;
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
HSSFSheet sheet = workbook.GetSheetAt(sheetIndex);
DataTable table = new DataTable();
#region 构造datatable的列
for (int i = sheet.GetRow(sheet.FirstRowNum).FirstCellNum; i < sheet.GetRow(sheet.FirstRowNum).LastCellNum; i++)
{
string title = sheet.GetRow(sheet.FirstRowNum).GetCell(i).StringCellValue;
DataColumn col = new DataColumn(title.Trim());
table.Columns.Add(col);
}
#endregion 构造datatable的列
System.Text.RegularExpressions.Regex re = new System.Text.RegularExpressions.Regex(@"\n");
//sheet.LastRowNum：标签页中的行数
for (int i = (sheet.FirstRowNum); i <= sheet.LastRowNum; i++)
{
#region 构造datatable
DataRow dr = table.NewRow();
dr.BeginEdit();
HSSFRow row = sheet.GetRow(i);
//row.LastCellNum：一行中的单元格数据
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
HSSFCell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
#region 判断单元格类型
switch (cell.CellType)
{
case HSSFCellType.BLANK:
dr[j] = "";
break;
case HSSFCellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case HSSFCellType.NUMERIC:
dr[j] = re.Replace(cell.ToString(), "").Trim();
break;
case HSSFCellType.STRING:
dr[j] = (re.Replace(cell.StringCellValue, "").Replace("．", ".")).Trim();
break;
case HSSFCellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case HSSFCellType.FORMULA:
dr[j] = cell.NumericCellValue;
break;
default:
dr[j] = cell.CellFormula;
break;
}
#endregion 判断单元格类型
}
dr.EndEdit();
table.Rows.Add(dr);
#endregion 构造datatable
}
excelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
catch (Exception ex)
{
strMsg = ex.Message;
return(null);
}
}
public bool ImportExcel(Stream stream, out string msg)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
List <TakeCheck> list = new List <TakeCheck>();
using (Sheet sheet = hssfworkbook.GetSheetAt(0))
{
TakeCheck takecheck;
int r = 0;
int v = 0;
//总条目
int rowCount = sheet.LastRowNum;
Expression <Func <TakeCheck> > properties = (() => new TakeCheck()
{
Id = 0,
StoreName = "",
Account = "",
TakeOut = 0,
Bank = "",
BankNo = ""
});
var dict = properties.GetPropertyWithValue();
Row row;
Dictionary <string, string> di = null;
for (int i = sheet.FirstRowNum + 2; i <= rowCount; i++)
{
row = sheet.GetRow(i);
if (row != null)
{
r = 0;
foreach (var item in dict.Keys)
{
Cell cell = row.GetCell(r);
if (cell != null)
{
if (item == "Id")
{
if (int.TryParse(cell.ToString(), out v))
{
takecheck = Repository.Get(v);
if (takecheck == null)
{
msg = "第" + i + "记录不存在";
return(false);
}
if (takecheck.ConfirmTime.HasValue)
{
continue;
}
list.Add(takecheck);
di = ExpressionHelper.GetProperties <TakeCheck>(takecheck);
}
else
{
msg = "第" + i + "行序号有误";
return(false);
}
}
else
{
if (cell.ToString() != di[item])
{
msg = "第" + i + "行数据有误";
return(false);
}
}
}
r++;
}
}
}
}
if (list.Count > 0)
{
using (var tran = new TransactionScope())
{
foreach (var item in list)
{
UnitWork.RegisterDirty(item, () => new TakeCheck {
ConfirmTime = DateTime.Now
});
}
UnitWork.Commit();
tran.Complete();
}
}
msg = "ok";
return(true);
}
public ErrorObj GetErrorJson()
{
var mse = new ErrorObj();
mse.Form = new Dictionary <string, string>();
var err = ErrorListVM?.EntityList?.Where(x => x.Index == 0).FirstOrDefault()?.Message;
if (string.IsNullOrEmpty(err))
{
var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault();
hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo);
var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
List <ExcelPropety> excelPropetys = new List <ExcelPropety>();
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template);
excelPropetys.Add(ep);
}
int columnCount = excelPropetys.Count;
//int excelPropetyCount = excelPropetys.Count;
var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault();
if (dynamicColumn != null)
{
columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
var errorStyle = hssfworkbook.CreateCellStyle();
IFont f = hssfworkbook.CreateFont();
f.Color = HSSFColor.Red.Index;
errorStyle.SetFont(f);
errorStyle.IsLocked = true;
foreach (var e in ErrorListVM?.EntityList)
{
if (e.Index > 0)
{
var c = sheet.GetRow((int)(e.Index - 1)).CreateCell(columnCount);
c.CellStyle = errorStyle;
c.SetCellValue(e.Message);
}
}
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Position = 0;
FileAttachmentVM vm = new FileAttachmentVM();
vm.CopyContext(this);
vm.Entity.FileName = "Error-" + fa.FileName;
vm.Entity.Length = ms.Length;
vm.Entity.UploadTime = DateTime.Now;
vm.Entity.SaveFileMode = ConfigInfo.FileUploadOptions.SaveFileMode;
vm = FileHelper.GetFileByteForUpload(vm, ms, ConfigInfo, vm.Entity.FileName, null, null);
vm.Entity.IsTemprory = true;
if ((!string.IsNullOrEmpty(vm.Entity.Path) && (vm.Entity.SaveFileMode == SaveFileModeEnum.Local || vm.Entity.SaveFileMode == SaveFileModeEnum.DFS)) || (vm.Entity.FileData != null && vm.Entity.SaveFileMode == SaveFileModeEnum.Database))
{
vm.DoAdd();
}
ms.Close();
ms.Dispose();
err = "导入时发生错误";
mse.Form.Add("Entity.Import", err);
mse.Form.Add("Entity.ErrorFileId", vm.Entity.ID.ToString());
}
else
{
mse.Form.Add("Entity.Import", err);
}
return(mse);
}
public IActionResult ImportExport(IFormFile files)
{
ArrayList outArr = new ArrayList();
ArrayList stockSymbol = new ArrayList();
IFormFile file = Request.Form.Files[0];
if (file != null)
{
string folderName = "Upload";
string webRootPath = _hostingEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
StringBuilder sb = new StringBuilder();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
sb.Append("<table class='table table-hover'><tr>");
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
outArr.Add(cell.ToString()); // Add to the array
sb.Append("<th>" + cell.ToString() + "</th>");
}
sb.Append("</tr>");
sb.AppendLine("<tr>");
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
outArr.Add(row.GetCell(j).ToString());
sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
//Check if it's a unique stock Symbol (if so add to stockSymbol arrayList)
if (j == 1 && !stockSymbol.Contains(row.GetCell(j).ToString()))
{
stockSymbol.Add(row.GetCell(j).ToString());
}
}
}
sb.AppendLine("</tr>");
}
sb.Append("</table>");
stream.Close(); // Close the reading stream
}
}
Debug.WriteLine(outArr.ToArray());
DataSaveWrite.WriteDataToFile(outArr, "stockInfo"); // Write it to a file
DataSaveWrite.WriteDataToFile(stockSymbol, "stockList");
return(this.Content(sb.ToString()));
}
return(new EmptyResult());
}
/// <summary>
/// 读取模版中的数据
/// </summary>
private void DoMapList()
{
try
{
Template.InitExcelData();
Template.InitCustomFormat();
TemplateData = new List <T>();
hssfworkbook = new HSSFWorkbook();
if (UploadFileId == null)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请上传模板文件"
});
return;
}
var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault();
hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo);
if (ValidityTemplateType && hssfworkbook.GetSheetAt(1).GetRow(0).Cells[2].ToString() != typeof(T).Name)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "错误的模板"
});
return;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
//所有ExcelPropety属性
List <ExcelPropety> excelPropetys = new List <ExcelPropety>();
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template);
excelPropetys.Add(ep);
}
#region 验证模版正确性 add by dufei
//取得列数
int columnCount = excelPropetys.Count;
//int excelPropetyCount = excelPropetys.Count;
var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault();
if (dynamicColumn != null)
{
columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1;
//excelPropetyCount = excelPropetyCount + dynamicColumn.DynamicColumns.Count - 1;
}
int pIndex = 0;
var cells = sheet.GetRow(0).Cells;
if (columnCount != cells.Count)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
return;
}
else
{
for (int i = 0; i < cells.Count; i++)
{
if (excelPropetys[pIndex].DataType != ColumnDataType.Dynamic)
{
if (cells[i].ToString().Trim('*') != excelPropetys[pIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
return;
}
pIndex++;
}
else
{
var listDynamicColumns = excelPropetys[i].DynamicColumns;
int dcCount = listDynamicColumns.Count;
for (int dclIndex = 0; dclIndex < dcCount; dclIndex++)
{
if (cells[i].ToString().Trim('*') != listDynamicColumns[dclIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
break;
}
i = i + 1;
}
i = i - 1;
pIndex++;
}
}
}
#endregion
int rowIndex = 2;
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (IsEmptyRow(row, columnCount))
{
return;
}
T result = new T();
int propetyIndex = 0;
for (int i = 0; i < columnCount; i++)
{
ExcelPropety excelPropety = CopyExcelPropety(excelPropetys[propetyIndex]); //excelPropetys[propetyIndex];
var pts = propetys[propetyIndex];
string value = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
if (excelPropety.DataType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
//验证数据类型并添加错误信息
excelPropety.DynamicColumns[dynamicColIndex].ValueValidity(row.GetCell(i + dynamicColIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(), ErrorListVM.EntityList, rowIndex);
}
i = i + dynamicColCount - 1;
}
else
{
excelPropety.ValueValidity(value, ErrorListVM.EntityList, rowIndex);
}
if (ErrorListVM.EntityList.Count == 0)
{
pts.SetValue(result, excelPropety);
}
propetyIndex++;
}
result.ExcelIndex = rowIndex;
TemplateData.Add(result);
rowIndex++;
}
return;
}
catch
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
//ErrorListVM.ErrorList.Add(new ErrorMessage { Message = ex.Message });
}
return;
}
public int ExcelToDataTable(ref DataTable dt, string sheetName)
{
try
{
if (dt == null)
{
return(0);
}
dt.Columns.Clear();
dt.Rows.Clear();
dt.Clear(); //先清空表
HSSFWorkbook Workbook = NPOIOpenExcel(FileName); //打开工作薄
ISheet sheet = null;
if (sheetName != null)
{
sheet = Workbook.GetSheet(sheetName);
}
if (sheet == null)
{
sheet = Workbook.GetSheetAt(0);
}
if (sheet == null)
{
return(0);
}
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return(0);
}
int nColCount = firstRow.LastCellNum;
int nRowCount = sheet.LastRowNum + 1;
for (int i = 0; i < nColCount; i++)
{
string strValue = firstRow.GetCell(i).ToString();
if (strValue != null && strValue.Trim() != "")
{
dt.Columns.Add(strValue);
}
}
for (int i = 1; i < nRowCount; i++)
{
if (sheet.GetRow(i) == null || sheet.GetRow(i).GetCell(0) == null || sheet.GetRow(i).GetCell(0).ToString().Trim() == "") //排除null
{
continue;
}
DataRow dr = dt.NewRow();
for (int j = 0; j < nColCount; j++)
{
string strField = firstRow.GetCell(j).ToString();
if (sheet.GetRow(i).GetCell(j) == null) //排除null
{
continue;
}
dr[strField] = sheet.GetRow(i).GetCell(j).ToString();
}
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
throw new Exception($"读取Excel文件到DataTable出错:{ex.Message}");
}
return(1);
}
protected void btn_Import_Click_Click(object sender, EventArgs e)
{
List <string> listc = new List <string>();
List <string> listm = new List <string>();
string FilePath = @"E:\利润及利润分配表\";
if (!Directory.Exists(FilePath))
{
Directory.CreateDirectory(FilePath);
}
//将文件上传到服务器
HttpPostedFile UserHPF = FileUpload1.PostedFile;
try
{
string fileContentType = UserHPF.ContentType;// 获取客户端发送的文件的 MIME 内容类型
if (fileContentType == "application/vnd.ms-excel")
{
if (UserHPF.ContentLength > 0)
{
UserHPF.SaveAs(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName));//将上传的文件存放在指定的文件夹中
}
}
else
{
Response.Write("<script>alert('文件类型不符合要求，请您核对后重新上传！');</script>"); return;
}
}
catch
{
Response.Write("<script>alert('文件上传过程中出现错误！');</script>"); return;
}
using (FileStream fs = File.OpenRead(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName)))
{
//根据文件流创建一个workbook
IWorkbook wk = new HSSFWorkbook(fs);
//获取第一个工作表
ISheet sheet = wk.GetSheetAt(0);
//循环读取每一行数据，由于execel有列名以及序号，从1开始
string sqlc = "";
string sqlm = "";
string ncs = "本期数";
string qms = "本年累计数";
IRow row1 = sheet.GetRow(44);
ICell cell0 = row1.GetCell(0);
string rqbhc = cell0.StringCellValue.ToString().Trim();
string rqbhm = cell0.StringCellValue.ToString().Trim();//得到修改人员编码
string sqlTextchk = "select * from TBFM_LRFP where RQBH='" + cell0.StringCellValue.ToString().Trim() + "'";
System.Data.DataTable dtchk = DBCallCommon.GetDTUsingSqlText(sqlTextchk);
if (dtchk.Rows.Count > 0)
{
Response.Write("<script>alert('日期编号已存在！');</script>"); return;
}
sqlc = "'" + rqbhc + "','" + ncs + "'";
sqlm = "'" + rqbhm + "','" + qms + "'";
IRow row4 = sheet.GetRow(47);
ICell cell24 = row4.GetCell(2);
ICell cell34 = row4.GetCell(3);
sqlc += ",'" + cell24.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell34.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row5 = sheet.GetRow(48);
ICell cell25 = row5.GetCell(2);
ICell cell35 = row5.GetCell(3);
sqlc += ",'" + cell25.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell35.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row6 = sheet.GetRow(49);
ICell cell26 = row6.GetCell(2);
ICell cell36 = row6.GetCell(3);
sqlc += ",'" + cell26.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell36.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row7 = sheet.GetRow(50);
ICell cell27 = row7.GetCell(2);
ICell cell37 = row7.GetCell(3);
sqlc += ",'" + cell27.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell37.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row8 = sheet.GetRow(51);
ICell cell28 = row8.GetCell(2);
ICell cell38 = row8.GetCell(3);
sqlc += ",'" + cell28.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell38.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row9 = sheet.GetRow(52);
ICell cell29 = row9.GetCell(2);
ICell cell39 = row9.GetCell(3);
sqlc += ",'" + cell29.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell39.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row10 = sheet.GetRow(53);
ICell cell210 = row10.GetCell(2);
ICell cell310 = row10.GetCell(3);
sqlc += ",'" + cell210.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell310.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row11 = sheet.GetRow(54);
ICell cell211 = row11.GetCell(2);
ICell cell311 = row11.GetCell(3);
sqlc += ",'" + cell211.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell311.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row12 = sheet.GetRow(55);
ICell cell212 = row12.GetCell(2);
ICell cell312 = row12.GetCell(3);
sqlc += ",'" + cell212.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell312.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row13 = sheet.GetRow(56);
ICell cell213 = row13.GetCell(2);
ICell cell313 = row13.GetCell(3);
sqlc += ",'" + cell213.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell313.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row14 = sheet.GetRow(57);
ICell cell214 = row14.GetCell(2);
ICell cell314 = row14.GetCell(3);
sqlc += ",'" + cell214.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell314.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row15 = sheet.GetRow(58);
ICell cell215 = row15.GetCell(2);
ICell cell315 = row15.GetCell(3);
sqlc += ",'" + cell215.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell315.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row16 = sheet.GetRow(58);
ICell cell216 = row16.GetCell(2);
ICell cell316 = row16.GetCell(3);
sqlc += ",'" + cell216.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell316.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row17 = sheet.GetRow(60);
ICell cell217 = row17.GetCell(2);
ICell cell317 = row17.GetCell(3);
sqlc += ",'" + cell217.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell317.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row18 = sheet.GetRow(61);
ICell cell218 = row18.GetCell(2);
ICell cell318 = row18.GetCell(3);
sqlc += ",'" + cell218.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell318.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row19 = sheet.GetRow(62);
ICell cell219 = row19.GetCell(2);
ICell cell319 = row19.GetCell(3);
sqlc += ",'" + cell219.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell319.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row20 = sheet.GetRow(63);
ICell cell220 = row20.GetCell(2);
ICell cell320 = row20.GetCell(3);
sqlc += ",'" + cell220.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell320.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row21 = sheet.GetRow(64);
ICell cell221 = row21.GetCell(2);
ICell cell321 = row21.GetCell(3);
sqlc += ",'" + cell221.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell321.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row22 = sheet.GetRow(65);
ICell cell222 = row22.GetCell(2);
ICell cell322 = row22.GetCell(3);
sqlc += ",'" + cell222.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell322.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row23 = sheet.GetRow(66);
ICell cell223 = row23.GetCell(2);
ICell cell323 = row23.GetCell(3);
sqlc += ",'" + cell223.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell323.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row24 = sheet.GetRow(67);
ICell cell224 = row24.GetCell(2);
ICell cell324 = row24.GetCell(3);
sqlc += ",'" + cell224.NumericCellValue.ToString("0.00").Trim() + "'";
sqlm += ",'" + cell324.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row27 = sheet.GetRow(70);
ICell cell327 = row27.GetCell(3);
sqlm += ",'" + cell327.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row28 = sheet.GetRow(71);
ICell cell328 = row28.GetCell(3);
sqlm += ",'" + cell328.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row29 = sheet.GetRow(72);
ICell cell329 = row29.GetCell(3);
sqlm += ",'" + cell329.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row30 = sheet.GetRow(73);
ICell cell230 = row30.GetCell(2);
ICell cell330 = row30.GetCell(3);
sqlm += ",'" + cell330.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row31 = sheet.GetRow(74);
ICell cell331 = row31.GetCell(3);
sqlm += ",'" + cell331.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row32 = sheet.GetRow(75);
ICell cell332 = row32.GetCell(3);
sqlm += ",'" + cell332.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row33 = sheet.GetRow(76);
ICell cell333 = row33.GetCell(3);
sqlm += ",'" + cell333.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row34 = sheet.GetRow(77);
ICell cell334 = row34.GetCell(3);
sqlm += ",'" + cell334.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row35 = sheet.GetRow(78);
ICell cell235 = row35.GetCell(2);
ICell cell335 = row35.GetCell(3);
sqlm += ",'" + cell335.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row36 = sheet.GetRow(79);
ICell cell336 = row36.GetCell(3);
sqlm += ",'" + cell336.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row37 = sheet.GetRow(80);
ICell cell237 = row37.GetCell(2);
ICell cell337 = row37.GetCell(3);
sqlm += ",'" + cell337.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row38 = sheet.GetRow(81);
ICell cell338 = row38.GetCell(3);
sqlm += ",'" + cell338.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row39 = sheet.GetRow(82);
ICell cell239 = row39.GetCell(2);
ICell cell339 = row39.GetCell(3);
sqlm += ",'" + cell339.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row40 = sheet.GetRow(83);
ICell cell340 = row40.GetCell(3);
sqlm += ",'" + cell340.NumericCellValue.ToString("0.00").Trim() + "'";
IRow row41 = sheet.GetRow(84);
ICell cell341 = row41.GetCell(3);
sqlm += ",'" + cell341.NumericCellValue.ToString("0.00").Trim() + "'";
#region 存入数据库
string sqlTxtc = string.Format("insert into TBFM_LRFP({0}) values({1})", "RQBH,LRFP_TYPE,LRFP_YYSR,LRFP_YYSR_ZYSR,LRFP_YYSR_QTSR,LRFP_YYSR_JYCB,LRFP_YYSR_ZYCB,LRFP_YYSR_QTCB,LRFP_YYSR_SJFJ,LRFP_YYSR_XSFY,LRFP_YYSR_GLFY,LRFP_YYSR_CWFY,LRFP_YYSR_JZSS,LRFP_YYSR_JZBD,LRFP_YYSR_TZSY,LRFP_YYSR_LYHY,LRFP_YYLR,LRFP_YYLR_YWSR,LRFP_YYLR_YWZC,LRFP_YYLR_FLDSS,LRFP_LRZE,LRFP_LRZE_SDSF,LRFP_JLR", sqlc);
string sqlTxtm = string.Format("insert into TBFM_LRFP({0}) values({1})", "RQBH,LRFP_TYPE,LRFP_YYSR,LRFP_YYSR_ZYSR,LRFP_YYSR_QTSR,LRFP_YYSR_JYCB,LRFP_YYSR_ZYCB,LRFP_YYSR_QTCB,LRFP_YYSR_SJFJ,LRFP_YYSR_XSFY,LRFP_YYSR_GLFY,LRFP_YYSR_CWFY,LRFP_YYSR_JZSS,LRFP_YYSR_JZBD,LRFP_YYSR_TZSY,LRFP_YYSR_LYHY,LRFP_YYLR,LRFP_YYLR_YWSR,LRFP_YYLR_YWZC,LRFP_YYLR_FLDSS,LRFP_LRZE,LRFP_LRZE_SDSF,LRFP_JLR,LRFP_NCWFP,LRFP_QTZR,LRFP_KGFP,LRFP_KGFP_FDYYGJ,LRFP_KGFP_FDGY,LRFP_KGFP_JLFL,LRFP_KGFP_CBJJ,LRFP_KGFP_QYFZ,LRFP_KGFP_LRGH,LRFP_KGTZFP,LRFP_KGTZFP_YFYXG,LRFP_KGTZFP_RYYY,LRFP_KGTZFP_YFPTG,LRFP_KGTZFP_ZZZB,LRFP_WFPLR", sqlm);
listc.Add(sqlTxtc);
listm.Add(sqlTxtm);
#endregion
}
DBCallCommon.ExecuteTrans(listc);
DBCallCommon.ExecuteTrans(listm);
foreach (string fileName in Directory.GetFiles(FilePath))//清空该文件夹下的文件
{
string newName = fileName.Substring(fileName.LastIndexOf("\\") + 1);
System.IO.File.Delete(FilePath + "\\" + newName);//删除文件下储存的文件
}
bindGrid();
Response.Redirect(Request.Url.ToString());
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName, string sheetName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = null;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = hssfworkbook.GetSheet(sheetName) as HSSFSheet;
}
else
{
sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dataRow = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.NUMERIC)
{
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else if (row.GetCell(j).CellType == CellType.FORMULA)
{
dataRow[j] = row.GetCell(j).NumericCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
private void ExportDataItem(string sqltext, string pid)
{
string filename = "利润分配表.xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("利润分配表.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);//创建workbook对象
ISheet sheet0 = wk.GetSheetAt(0);
string sqltextc = sqltext + "LRFP_TYPE='本期数'";
string sqltextm = sqltext + "LRFP_TYPE='本年累计数'";
System.Data.DataTable dtc = DBCallCommon.GetDTUsingSqlText(sqltextc);
System.Data.DataTable dtm = DBCallCommon.GetDTUsingSqlText(sqltextm);
DataRow drc = dtc.Rows[0];
DataRow drm = dtm.Rows[0];
IRow row1 = sheet0.GetRow(1);//创建行
row1.GetCell(0).SetCellValue(drc["RQBH"].ToString());
IRow row4 = sheet0.GetRow(4);//创建行
row4.GetCell(2).SetCellValue(drc["LRFP_YYSR"].ToString());
row4.GetCell(3).SetCellValue(drm["LRFP_YYSR"].ToString());
IRow row5 = sheet0.GetRow(5);
row5.GetCell(2).SetCellValue(drc["LRFP_YYSR_ZYSR"].ToString());
row5.GetCell(3).SetCellValue(drm["LRFP_YYSR_ZYSR"].ToString());
IRow row6 = sheet0.GetRow(6);
row6.GetCell(2).SetCellValue(drc["LRFP_YYSR_QTSR"].ToString());
row6.GetCell(3).SetCellValue(drm["LRFP_YYSR_QTSR"].ToString());
IRow row7 = sheet0.GetRow(7);
row7.GetCell(2).SetCellValue(drc["LRFP_YYSR_JYCB"].ToString());
row7.GetCell(3).SetCellValue(drm["LRFP_YYSR_JYCB"].ToString());
IRow row8 = sheet0.GetRow(8);
row8.GetCell(2).SetCellValue(drc["LRFP_YYSR_ZYCB"].ToString());
row8.GetCell(3).SetCellValue(drm["LRFP_YYSR_ZYCB"].ToString());
IRow row9 = sheet0.GetRow(9);
row9.GetCell(2).SetCellValue(drc["LRFP_YYSR_QTCB"].ToString());
row9.GetCell(3).SetCellValue(drm["LRFP_YYSR_QTCB"].ToString());
IRow row10 = sheet0.GetRow(10);
row10.GetCell(2).SetCellValue(drc["LRFP_YYSR_SJFJ"].ToString());
row10.GetCell(3).SetCellValue(drm["LRFP_YYSR_SJFJ"].ToString());
IRow row11 = sheet0.GetRow(11);
row11.GetCell(2).SetCellValue(drc["LRFP_YYSR_XSFY"].ToString());
row11.GetCell(3).SetCellValue(drm["LRFP_YYSR_XSFY"].ToString());
IRow row12 = sheet0.GetRow(12);
row12.GetCell(2).SetCellValue(drc["LRFP_YYSR_XSFY"].ToString());
row12.GetCell(3).SetCellValue(drm["LRFP_YYSR_XSFY"].ToString());
IRow row13 = sheet0.GetRow(13);
row13.GetCell(2).SetCellValue(drc["LRFP_YYSR_CWFY"].ToString());
row13.GetCell(3).SetCellValue(drm["LRFP_YYSR_CWFY"].ToString());
IRow row14 = sheet0.GetRow(14);
row14.GetCell(2).SetCellValue(drc["LRFP_YYSR_JZSS"].ToString());
row14.GetCell(3).SetCellValue(drm["LRFP_YYSR_JZSS"].ToString());
IRow row15 = sheet0.GetRow(15);
row15.GetCell(2).SetCellValue(drc["LRFP_YYSR_JZBD"].ToString());
row15.GetCell(3).SetCellValue(drm["LRFP_YYSR_JZBD"].ToString());
IRow row16 = sheet0.GetRow(16);
row16.GetCell(2).SetCellValue(drc["LRFP_YYSR_TZSY"].ToString());
row16.GetCell(3).SetCellValue(drm["LRFP_YYSR_TZSY"].ToString());
IRow row17 = sheet0.GetRow(17);
row17.GetCell(2).SetCellValue(drc["LRFP_YYSR_LYHY"].ToString());
row17.GetCell(3).SetCellValue(drm["LRFP_YYSR_LYHY"].ToString());
IRow row18 = sheet0.GetRow(18);
row18.GetCell(2).SetCellValue(drc["LRFP_YYLR"].ToString());
row18.GetCell(3).SetCellValue(drm["LRFP_YYLR"].ToString());
IRow row19 = sheet0.GetRow(19);
row19.GetCell(2).SetCellValue(drc["LRFP_YYLR_YWSR"].ToString());
row19.GetCell(3).SetCellValue(drm["LRFP_YYLR_YWSR"].ToString());
IRow row20 = sheet0.GetRow(20);
row20.GetCell(2).SetCellValue(drc["LRFP_YYLR_YWZC"].ToString());
row20.GetCell(3).SetCellValue(drm["LRFP_YYLR_YWZC"].ToString());
IRow row21 = sheet0.GetRow(21);
row21.GetCell(2).SetCellValue(drc["LRFP_YYLR_FLDSS"].ToString());
row21.GetCell(3).SetCellValue(drm["LRFP_YYLR_FLDSS"].ToString());
IRow row22 = sheet0.GetRow(22);
row22.GetCell(2).SetCellValue(drc["LRFP_LRZE"].ToString());
row22.GetCell(3).SetCellValue(drm["LRFP_LRZE"].ToString());
IRow row23 = sheet0.GetRow(23);
row23.GetCell(2).SetCellValue(drc["LRFP_LRZE_SDSF"].ToString());
row23.GetCell(3).SetCellValue(drm["LRFP_LRZE_SDSF"].ToString());
IRow row24 = sheet0.GetRow(24);
row24.GetCell(2).SetCellValue(drc["LRFP_JLR"].ToString());
row24.GetCell(3).SetCellValue(drm["LRFP_JLR"].ToString());
IRow row27 = sheet0.GetRow(27);
row27.GetCell(3).SetCellValue(drm["LRFP_NCWFP"].ToString());
IRow row28 = sheet0.GetRow(28);
row28.GetCell(3).SetCellValue(drm["LRFP_QTZR"].ToString());
IRow row29 = sheet0.GetRow(29);
row29.GetCell(3).SetCellValue(drm["LRFP_KGFP"].ToString());
IRow row30 = sheet0.GetRow(30);
row30.GetCell(3).SetCellValue(drm["LRFP_KGFP_FDYYGJ"].ToString());
IRow row31 = sheet0.GetRow(31);
row31.GetCell(3).SetCellValue(drm["LRFP_KGFP_FDGY"].ToString());
IRow row32 = sheet0.GetRow(32);
row32.GetCell(3).SetCellValue(drm["LRFP_KGFP_JLFL"].ToString());
IRow row33 = sheet0.GetRow(33);
row33.GetCell(3).SetCellValue(drm["LRFP_KGFP_CBJJ"].ToString());
IRow row34 = sheet0.GetRow(34);
row34.GetCell(3).SetCellValue(drm["LRFP_KGFP_QYFZ"].ToString());
IRow row35 = sheet0.GetRow(35);
row35.GetCell(3).SetCellValue(drm["LRFP_KGFP_LRGH"].ToString());
IRow row36 = sheet0.GetRow(36);
row36.GetCell(3).SetCellValue(drm["LRFP_KGTZFP"].ToString());
IRow row37 = sheet0.GetRow(37);
row37.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_YFYXG"].ToString());
IRow row38 = sheet0.GetRow(38);
row38.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_RYYY"].ToString());
IRow row39 = sheet0.GetRow(39);
row39.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_YFPTG"].ToString());
IRow row40 = sheet0.GetRow(40);
row40.GetCell(3).SetCellValue(drm["LRFP_KGTZFP_ZZZB"].ToString());
IRow row41 = sheet0.GetRow(41);
row41.GetCell(3).SetCellValue(drm["LRFP_WFPLR"].ToString());
sheet0.ForceFormulaRecalculation = true;
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
private void btn_ExportCustflowReport_Click(object sender, EventArgs e)
{
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
if (folderBrowserDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string selectedPath = folderBrowserDialog.SelectedPath;
string text = selectedPath + str_file_location + str_file_name + _strFromDate.Replace("-", "") + "-" + _strToDate.Replace("-", "") + str_file_type;
FileInfo file = new FileInfo(text);
int num = 2;
if (!File.Exists(text))
{
wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
for (int i = 0; i < dataGridView2.RowCount + num; i++)
{
IRow row = sh.CreateRow(i);
for (int j = 0; j < dataGridView2.ColumnCount; j++)
{
row.CreateCell(j);
}
}
using (FileStream @out = new FileStream(text, FileMode.Create, FileAccess.Write))
{
wb.Write(@out);
}
}
if (!IsFileLocked(file))
{
using (FileStream s = new FileStream(text, FileMode.Open, FileAccess.Read))
{
wb = new HSSFWorkbook(s);
for (int k = 0; k < wb.Count; k++)
{
lst_Sheet.Add(wb.GetSheetAt(k).SheetName);
}
}
}
if (!IsFileLocked(file))
{
sh = (HSSFSheet)wb.GetSheet(lst_Sheet[0]);
int l = 0;
string[] array = new string[9]
{
"日期:",
_strFromDate + "~" + _strToDate,
"特定會員:",
_member_name,
"會員類型:",
_member_type,
"會員狀態:",
_member_status,
""
};
string[] array2 = new string[9]
{
"購買會員",
"銷售總額（原始）",
"現金收款",
"賒帳金額",
"找零",
"退款金額",
"總消費次",
"銷售品項",
"銷售數量"
};
for (; l < num; l++)
{
for (int m = 0; m < dataGridView2.ColumnCount; m++)
{
if (sh.GetRow(l).GetCell(m) == null)
{
sh.GetRow(l).CreateCell(m);
}
if (l == 0)
{
sh.GetRow(l).GetCell(m).SetCellValue(array[m]);
}
if (l == 1)
{
sh.GetRow(l).GetCell(m).SetCellValue(array2[m]);
}
}
}
for (int n = 0; n < dataGridView2.RowCount; n++)
{
if (sh.GetRow(l) == null)
{
sh.CreateRow(l);
}
for (int num2 = 0; num2 < dataGridView2.ColumnCount; num2++)
{
if (sh.GetRow(l).GetCell(num2) == null)
{
sh.GetRow(l).CreateCell(num2);
}
if (dataGridView2[num2, n].Value != null)
{
sh.GetRow(l).GetCell(num2).SetCellValue(dataGridView2[num2, n].Value.ToString());
}
}
l++;
}
using (FileStream out2 = new FileStream(text, FileMode.Open, FileAccess.Write))
{
wb.Write(out2);
AutoClosingMessageBox.Show("匯出報表於" + text);
}
}
else
{
AutoClosingMessageBox.Show(text + "檔案使用中，請確認檔案是在未開啟的狀態下");
}
}
public JsonResult ImportExcel()
{
HttpPostedFileBase file = Request.Files["bookPageExcel"];
if (file == null)
{
return(Json(new { isSuccess = false, message = "请选择上传的Excel文件" }));
}
else
{
if (file.FileName.Split('.')[1] == "xls" || file.FileName.Split('.')[1] == "xlsx")
{
//对文件的格式判断，此处省略
List <FanHobbyInfo> fanHobbyInfos = new List <FanHobbyInfo>();
Stream inputStream = file.InputStream;
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputStream);
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 2); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
FanHobbyInfo model = new FanHobbyInfo();
if (row != null)
{
model.SerialNumber = row.GetCell(0) != null?GetCellValue(row.GetCell(0)) : string.Empty;
model.MainReadTypeName1 = row.GetCell(1) != null?GetCellValue(row.GetCell(1)) : string.Empty;
model.MainReadTypeName2 = row.GetCell(2) != null?GetCellValue(row.GetCell(2)) : string.Empty;
model.FoodTypeName = row.GetCell(3) != null?GetCellValue(row.GetCell(3)) : string.Empty;
model.FruitsTypeName = row.GetCell(4) != null?GetCellValue(row.GetCell(4)) : string.Empty;
model.SweetTypeName = row.GetCell(5) != null?GetCellValue(row.GetCell(5)) : string.Empty;
model.WXNickName = row.GetCell(6) != null?GetCellValue(row.GetCell(6)) : string.Empty;
model.WXId = row.GetCell(7) != null?GetCellValue(row.GetCell(7)) : string.Empty;
model.Phone = row.GetCell(8) != null?GetCellValue(row.GetCell(8)) : string.Empty;
model.Age = row.GetCell(9) != null?GetCellValue(row.GetCell(9)) : string.Empty;
model.Profession = row.GetCell(10) != null?GetCellValue(row.GetCell(10)) : string.Empty;
model.Education = row.GetCell(11) != null?GetCellValue(row.GetCell(11)) : string.Empty;
//model.WXType = row.GetCell(12) != null ? GetCellValue(row.GetCell(12)) : string.Empty;
model.Remark = row.GetCell(12) != null?GetCellValue(row.GetCell(12)) : string.Empty;
model.CreateUser = row.GetCell(13) != null?GetCellValue(row.GetCell(13)) : string.Empty;
model.CreateTime = row.GetCell(14) != null?GetCellValue(row.GetCell(14)) : string.Empty;
model.UpdateTime = row.GetCell(15) != null?GetCellValue(row.GetCell(15)) : string.Empty;
model.Device = row.GetCell(16) != null?GetCellValue(row.GetCell(16)) : string.Empty;
model.System = row.GetCell(17) != null?GetCellValue(row.GetCell(17)) : string.Empty;
model.Browser = row.GetCell(18) != null?GetCellValue(row.GetCell(18)) : string.Empty;
model.IP = row.GetCell(19) != null?GetCellValue(row.GetCell(19)) : string.Empty;
}
fanHobbyInfos.Add(model);
}
var errorMsg = "";
var errorCount = 0; var conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (IDbConnection con = new SqlConnection(conStr))
{
foreach (var book in fanHobbyInfos)
{
try
{
DynamicParameters dp = new DynamicParameters();
dp.Add("@SerialNumber", book.SerialNumber.Trim(), DbType.String);
//dp.Add("@WXType", book.WXType.Trim(), DbType.String);
dp.Add("@MainReadTypeName1", book.MainReadTypeName1.Trim(), DbType.String);
dp.Add("@MainReadTypeName2", book.MainReadTypeName2.Trim(), DbType.String);
dp.Add("@FoodTypeName", book.FoodTypeName.Trim(), DbType.String);
dp.Add("@FruitsTypeName", book.FruitsTypeName.Trim(), DbType.String);
dp.Add("@SweetTypeName", book.SweetTypeName.Trim(), DbType.String);
dp.Add("@WXNickName", book.WXNickName.Trim(), DbType.String);
dp.Add("@WXId", book.WXId.Trim(), DbType.String);
dp.Add("@Phone", book.Phone.Trim(), DbType.String);
dp.Add("@Age", book.Age.Trim(), DbType.String);
dp.Add("@Profession", book.Profession.Trim(), DbType.String);
dp.Add("@Education", book.Education.Trim(), DbType.String);
dp.Add("@Remark", book.Remark.Trim(), DbType.String);
dp.Add("@CreateUser", book.CreateUser.Trim(), DbType.String);
dp.Add("@CreateTime", DateTime.Now, DbType.DateTime);
dp.Add("@UpdateTime", book.UpdateTime != "" ? Convert.ToDateTime(book.UpdateTime.Trim()) : DateTime.Now, DbType.DateTime);
dp.Add("@Device", book.Device.Trim(), DbType.String);
dp.Add("@System", book.System.Trim(), DbType.String);
dp.Add("@Browser", book.Browser.Trim(), DbType.String);
dp.Add("@IP", book.IP.Trim(), DbType.String);
dp.Add("@error", dbType: DbType.Int32, direction: ParameterDirection.Output, size: 1000);
dp.Add("@errorMsg", dbType: DbType.String, direction: ParameterDirection.Output, size: 1000);
con.Execute("BuildBookPage", dp, null, null, CommandType.StoredProcedure);
if (!string.IsNullOrEmpty(dp.Get <string>("@errorMsg")))
{
errorMsg += dp.Get <string>("@errorMsg") + "<br />";
}
//errorCount = errorCount + dp.Get<int>("@error");
}
catch (Exception e)
{
errorMsg = "导入失败";
errorCount++;
}
}
}
return(Json(new { isSuccess = (errorCount == 0), message = errorMsg }));
}
else
{
return(Json(new { isSuccess = false, message = "只能上传excel格式的文件" }));
}
}
}
public EvaluationSheet GetSheet(int sheetIndex)
{
return(new HSSFEvaluationSheet((HSSFSheet)_uBook.GetSheetAt(sheetIndex)));
}
public DataTable ExcelImport(string strFileName)
{
DataTable dt = new DataTable();
try
{
ISheet sheet = null;
using (System.IO.MemoryStream memStream = new System.IO.MemoryStream())
{
memStream.Position = 0;
WebClient webClient = new WebClient();
var obj = webClient.DownloadData(strFileName);//fileName 是远程url地址，可以url直接下载
Stream stream = new MemoryStream(obj);
if (strFileName.IndexOf("/raw") > 0)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(stream);
sheet = xssfworkbook.GetSheetAt(0);
}
else if (strFileName.IndexOf(".xlsx") == -1) //2003
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
sheet = hssfworkbook.GetSheetAt(0);
}
else//2007
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(stream);
sheet = xssfworkbook.GetSheetAt(0);
}
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++) //需求：列名的重复,则删除重复的列
{
ICell cell = headerRow.GetCell(j);
var name = cell.ToString();
name = Regex.Replace(name, "[ \\[ \\] \\^ \\*×(^)$%~!@#$…&%￥√+=<>《》!！??？:：•`·、。，；,.;\"‘’“”-]", " ");
dt.Columns.Add(name); //添加这列名
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Boolean:
dataRow[j] = row.GetCell(j);
break;
case CellType.Error:
break;
case CellType.Formula:
dataRow[j] = row.GetCell(j).CellFormula;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j);
}
break;
case CellType.String:
dataRow[j] = row.GetCell(j).StringCellValue;
break;
case CellType.Unknown:
break;
default:
dataRow[j] = row.GetCell(j);
break;
}
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
catch (Exception ex)
{
_logger.LogError("ExcelImport ex.Message:" + ex.Message + Environment.NewLine + "ex.StackTrace" + ex.StackTrace);
return(dt);
}
}
/// <summary>
/// 读取excel
/// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1；
/// headerRow.LastCellNum属性获取的是Excel中该行的列数
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetNum">工作表索引，以0开始</param>
/// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param>
/// <param name="endRowNum">结束行号</param>
/// <param name="startColNum">起始列号</param>
/// <param name="endColNum">结束列号</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
int sheetCount = hssfworkbook.NumberOfSheets;
sheetNum = sheetNum < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum;
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum);
HSSFRow headerRow = null;
#region 行列号范围验证
startColNum = startColNum < 0 ? 0 : startColNum;
startRowNum = startRowNum < 1 ? 1 : startRowNum;
headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1);
endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum;
endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum;
#endregion
//添加列
for (int j = startColNum - 1; j < endColNum; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
//添加行
for (int i = startRowNum; i <= endRowNum - 1; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = startColNum - 1; j < endColNum; j++)
{
if (row.GetCell(j) != null)
dataRow[j - startColNum + 1] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 从Excel2003取数据并记录到List集合里
/// </summary>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
/// <param name="filePath">保存文件绝对路径</param>
/// <param name="errorMsg">错误信息</param>
/// <returns>转换好的List对象集合</returns>
private static List <T> Excel2003ToEntityList <T>(Dictionary <string, string> cellHeard, string filePath, out StringBuilder errorMsg) where T : new()
{
errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
List <T> enlist = new List <T>(); // 转换后的集合
List <string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
try
{
using (FileStream fs = File.OpenRead(filePath))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页
for (int i = 1; i <= sheet.LastRowNum; i++) // 从1开始，第0行为单元头
{
// 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
if (sheet.GetRow(i) == null)
{
break;
}
T en = new T();
string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
for (int j = 0; j < keys.Count; j++)
{
// 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
if (keys[j].IndexOf(".") >= 0)
{
// 2.1解析子类属性
string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
if (subClassInfo != null)
{
// 2.1.1 获取子类的实例
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
// 2.1.2 根据属性名称获取子类里的属性信息
System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
if (properotyInfo != null)
{
try
{
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
}
catch (Exception e)
{
if (errStr.Length == 0)
{
errStr = "第" + i + "行数据转换异常：";
}
errStr += cellHeard[keys[j]] + "列；";
}
}
}
}
else
{
// 3.给指定的属性赋值
System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
if (properotyInfo != null)
{
try
{
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
}
catch (Exception e)
{
if (errStr.Length == 0)
{
errStr = "第" + i + "行数据转换异常：";
}
errStr += cellHeard[keys[j]] + "列；";
}
}
}
}
// 若有错误信息，就添加到错误信息里
if (errStr.Length > 0)
{
errorMsg.AppendLine(errStr);
}
enlist.Add(en);
}
}
return(enlist);
}
catch (Exception ex)
{
throw ex;
}
}
