public void TestOneSeriePlot()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, plotData).Build();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Bottom);
IChartAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Left);
IScatterChartData<string, double> scatterChartData =
chart.GetChartDataFactory().CreateScatterChartData<string, double>();
IChartDataSource<String> xs = DataSources.FromStringCellRange(sheet, CellRangeAddress.ValueOf("A1:J1"));
IChartDataSource<double> ys = DataSources.FromNumericCellRange(sheet, CellRangeAddress.ValueOf("A2:J2"));
IScatterChartSerie<string, double> serie = scatterChartData.AddSerie(xs, ys);
Assert.IsNotNull(serie);
Assert.AreEqual(1, scatterChartData.GetSeries().Count);
Assert.IsTrue(scatterChartData.GetSeries().Contains(serie));
chart.Plot(scatterChartData, bottomAxis, leftAxis);
}
public void TestFormulaCache()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, plotData).Build();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
IChartAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.LEFT);
IScatterChartData scatterChartData =
chart.GetChartDataFactory().CreateScatterChartData();
DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A1:E1"));
DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A2:E2"));
IScatterChartSerie serie = scatterChartData.AddSerie(xMarker, yMarker);
chart.Plot(scatterChartData, bottomAxis, leftAxis);
XSSFScatterChartData.Serie xssfScatterSerie =
(XSSFScatterChartData.Serie)serie;
XSSFNumberCache yCache = xssfScatterSerie.LastCalculatedYCache;
Assert.AreEqual(5, yCache.PointCount);
Assert.AreEqual(4.0, yCache.GetValueAt(3), 0.00001);
Assert.AreEqual(16.0, yCache.GetValueAt(5), 0.00001);
}
public TestXSSFChartAxis()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
}
public void TestLegendPositionAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
Assert.AreEqual(LegendPosition.TopRight, legend.Position);
}
public void Test_setOverlay_defaultChartLegend_expectOverlayInitialValueSetToFalse()
{
// Arrange
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
// Act
// Assert
Assert.IsFalse(legend.IsOverlay);
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
axis.SetCrossBetween(AxisCrossBetween.MidpointCategory);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MidpointCategory);
axis.Crosses=(AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
axis.SetCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MIDPOINT_CATEGORY);
axis.SetCrosses(AxisCrosses.AUTO_ZERO);
Assert.AreEqual(axis.GetCrosses(), AxisCrosses.AUTO_ZERO);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestCore()
{
OPCPackage pkg = PackageHelper.Open(
_ssSamples.OpenResourceAsStream("ExcelWithAttachments.xlsm")
);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
POIXMLPropertiesTextExtractor ext = new POIXMLPropertiesTextExtractor(wb);
ext.GetText();
// Now check
String text = ext.GetText();
String cText = ext.GetCorePropertiesText();
Assert.IsTrue(text.Contains("LastModifiedBy = Yury Batrakov"));
Assert.IsTrue(cText.Contains("LastModifiedBy = Yury Batrakov"));
}
public void TestExtended()
{
OPCPackage pkg = OPCPackage.Open(
_ssSamples.OpenResourceAsStream("ExcelWithAttachments.xlsm")
);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
POIXMLPropertiesTextExtractor ext = new POIXMLPropertiesTextExtractor(wb);
ext.GetText();
// Now check
String text = ext.GetText();
String eText = ext.GetExtendedPropertiesText();
Assert.IsTrue(text.Contains("Application = Microsoft Excel"));
Assert.IsTrue(text.Contains("Company = Mera"));
Assert.IsTrue(eText.Contains("Application = Microsoft Excel"));
Assert.IsTrue(eText.Contains("Company = Mera"));
}
public void TestGetFromMainExtractor()
{
OPCPackage pkg = PackageHelper.Open(_ssSamples.OpenResourceAsStream("ExcelWithAttachments.xlsm"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
XSSFExcelExtractor ext = new XSSFExcelExtractor(wb);
POIXMLPropertiesTextExtractor textExt = ext.GetMetadataTextExtractor();
// Check basics
assertNotNull(textExt);
Assert.IsTrue(textExt.GetText().Length > 0);
// Check some of the content
String text = textExt.GetText();
String cText = textExt.GetCorePropertiesText();
Assert.IsTrue(text.Contains("LastModifiedBy = Yury Batrakov"));
Assert.IsTrue(cText.Contains("LastModifiedBy = Yury Batrakov"));
textExt.Close();
ext.Close();
}
private bool ReadExcel(string filePath, char type, Action headAction, Action <char, IRow> rowAction)
{
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
XSSFWorkbook workbook = new XSSFWorkbook(fs);
//只读取第一张表
ISheet sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
Debug.LogError("ReadExcel Error Sheet:" + filePath);
return(false);
}
m_sheetName = sheet.SheetName;
//第一行为表头，必定是最大列数
IRow nameRow = sheet.GetRow(0);
int cellCount = nameRow.LastCellNum;
//表头
IRow headRow = sheet.GetRow(1);
//导出类型
IRow typeRow = sheet.GetRow(2);
//导出服务器客户端
IRow exportRow = sheet.GetRow(3);
ICell cell = exportRow.GetCell(0);
if (cell == null || cell.StringCellValue == null)
{
Debug.LogError("导出配置错误:" + filePath);
return(false);
}
if (cell.StringCellValue != "A" && !cell.StringCellValue.Contains(type))
{
Debug.LogWarning("跳过导出:" + filePath);
return(false);
}
//Debug.Log("导出:" + filePath);
string[] exportSettings = new string[cellCount];
for (int i = 0; i < cellCount; i++)
{
cell = exportRow.GetCell(i);
if (cell == null)
{
exportSettings[i] = string.Empty;
continue;
}
exportSettings[i] = cell.StringCellValue;
}
m_defineIndex = -1;
m_rawTypes = new string[cellCount];
m_cellTypes = new string[cellCount];
//第一列为id，只支持int，string
cell = typeRow.GetCell(0);
string value = cell.StringCellValue;
if (value[0] == CsvConfig.skipFlag)
{
m_rawTypes[0] = value;
m_cellTypes[0] = value.Substring(1);
}
else
{
m_rawTypes[0] = CsvConfig.skipFlag + value;
m_cellTypes[0] = value;
}
for (int i = 1; i < cellCount; i++)
{
cell = typeRow.GetCell(i);
if (cell == null)
{
continue;
}
value = cell.StringCellValue;
m_rawTypes[i] = value;
int pos = value.LastIndexOf(CsvConfig.classSeparator);
if (pos > 0)
{
m_cellTypes[i] = value.Substring(pos + 1);
}
else
{
m_cellTypes[i] = value;
}
}
m_headers = new CsvHeader[cellCount];
for (int i = 0; i < cellCount; i++)
{
if (string.IsNullOrEmpty(exportSettings[i]))
{
m_headers[i] = CsvHeader.Pop(string.Empty);
continue;
}
if (exportSettings[i] != "A" && !exportSettings[i].Contains(type))
{
m_headers[i] = CsvHeader.Pop(string.Empty);
continue;
}
cell = headRow.GetCell(i);
if (cell == null)
{
m_headers[i] = CsvHeader.Pop(string.Empty);
continue;
}
var cellType = m_cellTypes[i];
if (cellType == "define")
{
m_defineIndex = i;
m_defineName = cell.StringCellValue;
m_headers[i] = CsvHeader.Pop(string.Empty);
continue;
}
m_headers[i] = CsvHeader.Pop(cell.StringCellValue);
if (m_headers[i].type == eFieldType.Array && cellType.Length > 2 &&
cellType.EndsWith("[]", StringComparison.OrdinalIgnoreCase) &&
m_headers[i].name == m_headers[i].baseName)
{
//去除结尾的[]
m_cellTypes[i] = cellType.Substring(0, cellType.Length - 2);
}
}
int slot;
List <string> nodeSlots = new List <string>(1);
List <CsvNode> nodes = new List <CsvNode>(1);
for (int i = 0; i < m_headers.Length; i++)
{
CsvHeader header = m_headers[i];
if (header.type == eFieldType.Primitive)
{
header.SetSlot(-1);
continue;
}
slot = nodeSlots.IndexOf(header.baseName);
if (slot < 0)
{
nodeSlots.Add(header.baseName);
header.SetSlot(nodeSlots.Count - 1);
var node = CsvNode.Pop(header.baseName, header.type);
var subTypes = m_rawTypes[i].Split(CsvConfig.arrayChars, CsvConfig.classSeparator);
node.cellType = subTypes[0];
nodes.Add(node);
}
else
{
header.SetSlot(slot);
}
}
if (nodeSlots.Count > 0)
{
m_nodes = nodes.ToArray();
}
else
{
m_nodes = new CsvNode[0];
}
headAction();
m_rawIds.Clear();
int startIndex = 4;// sheet.FirstRowNum;
int lastIndex = sheet.LastRowNum;
for (int index = startIndex; index <= lastIndex; index++)
{
m_curIndex = index;
IRow row = sheet.GetRow(index);
if (row == null)
{
continue;
}
//跳过id为空，或者#号开头的行
cell = row.GetCell(0);
if (cell == null)
{
continue;
}
var obj = getCellValue(cell);
if (obj == null)
{
continue;
}
string id = obj.ToString();
if (string.IsNullOrEmpty(id) || id[0] == CsvConfig.skipFlag)
{
continue;
}
if (m_rawIds.IndexOf(id) >= 0)
{
Debug.LogError(m_filePath + " 重复id, index：" + index + " id:" + id);
continue;
}
m_rawIds.Add(id);
try {
rowAction(type, row);
}
catch (Exception e) {
Debug.LogError(m_filePath + " 转换错误, index：" + index + " info:" + obj + " \n" + e);
}
}
}
return(true);
}
/// <summary>
/// Crea un archivo excel acuerdo a los filtros especificados
/// </summary>
/// <param name="cab"></param>
/// <param name="det"></param>
/// <param name="filterParam"></param>
/// <returns></returns>
public string getDescargarConsulta(HistorialCargaArchivo_LinCab cab, NOMINA nomina, HistorialCargaArchivo_LinDet det, object[] filterParam)
{
var helperStyle = new Helpers.excelStyle();
try
{
var nombreArchivo = "Archivo " + filterParam[0].ToString() + " " + DateTime.Now.ToString("yyyyMMdd");
var rutaTemporal = @HttpContext.Current.Server.MapPath("~/Temp/Descargas/" + nombreArchivo + ".xlsx");
int total;
var tipoLinea = filterParam[0].ToString() == "NOMINA" ? "*" : "D";
//new Utils.DeleteFile().deleteFile(HttpContext.Current.Server.MapPath(@"~/Utils/xlsxs/"));
XSSFWorkbook book = new XSSFWorkbook();
var contratoSis = new nContratoSis().listContratoByID(new CONTRATO_SYS()
{
IDE_CONTRATO = cab.IDE_CONTRATO
});
var reglaArchivo = new ReglaArchivo()
{
Archivo = filterParam[0].ToString(), TipoLinea = tipoLinea, NUM_CONT_LIC = Convert.ToInt32(contratoSis.NRO_CONTRATO), vigente = 1
};
var listReglaArchivo = new nReglaArchivo().getListReglaArchivo(reglaArchivo, 0, 1000, "IdReglaArchivo ASC", out total);
if (reglaArchivo.Archivo.Equals("0"))
{
listReglaArchivo = listReglaArchivo.GroupBy(x => new { x.NombreCampo, x.TituloColumna })
.Select(y => new ReglaArchivo()
{
NombreCampo = y.Key.NombreCampo, TituloColumna = y.Key.TituloColumna
}).ToList();
}
//crear el libro
var sheet = book.CreateSheet(nombreArchivo);
var rowCabecera = sheet.CreateRow(1);
var headerStyle = helperStyle.setFontText(12, true, book);
var bodyStyle = helperStyle.setFontText(11, false, book);
//construir cabecera
ICell cellCabecera;
for (int i = 0; i < listReglaArchivo.Count; i++)
{
cellCabecera = rowCabecera.CreateCell(i + 1);
cellCabecera.SetCellValue(listReglaArchivo[i].TituloColumna);
cellCabecera.CellStyle = headerStyle;
}
//consultar datos segun los filtros especificados
if (filterParam[0].ToString() == "NOMINA")
{
var listNomina = new nNomina().listNominaConsulta(nomina, filterParam, 0, 100000, out total);
for (int i = 0; i < listNomina.Count; i++)
{
IRow rowBody = sheet.CreateRow(i + 2);
ICell cellBody;
for (int c = 0; c < listReglaArchivo.Count; c++)
{
cellBody = rowBody.CreateCell(c + 1);
var property = listNomina[i].GetType().GetProperty(listReglaArchivo[c].NombreCampo.ToString().Trim(), BindingFlags.Public | BindingFlags.Instance);
cellBody.SetCellValue(property.GetValue(listNomina[i], null) == null ? "" : property.GetValue(listNomina[i], null).ToString());
cellBody.CellStyle = bodyStyle;
}
}
}
else
{
var listHistoriaLinDet = new dPagoCargado().listArchivoCargado(cab, det, filterParam, 0, 100000, "TipoLinea ASC", out total);
for (int i = 0; i < listHistoriaLinDet.Count; i++)
{
IRow rowBody = sheet.CreateRow(i + 2);
ICell cellBody;
for (int c = 0; c < listReglaArchivo.Count; c++)
{
cellBody = rowBody.CreateCell(c + 1);
var property = listHistoriaLinDet[i].GetType().GetProperty(listReglaArchivo[c].NombreCampo.ToString().Trim(), BindingFlags.Public | BindingFlags.Instance);
cellBody.SetCellValue(property.GetValue(listHistoriaLinDet[i], null) == null ? "" : property.GetValue(listHistoriaLinDet[i], null).ToString());
cellBody.CellStyle = bodyStyle;
}
}
}
if (File.Exists(rutaTemporal))
{
File.Delete(rutaTemporal);
}
//guardar el archivo creado en memoria
using (var file = new FileStream(rutaTemporal, FileMode.Create, FileAccess.ReadWrite))
{
book.Write(file);
file.Close();
book.Close();
}
return(rutaTemporal);
}
catch (System.Exception ex)
{
throw;
}
}
private Tuple <List <RebateConfigModel>, string> ConvertExcelToList(HttpFileCollectionBase files)
{
var result = new List <RebateConfigModel>();
var msg = string.Empty;
var stream = files[0].InputStream;
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
var workBook = new XSSFWorkbook(new MemoryStream(buffer));
var sheet = workBook.GetSheetAt(0);
#region
Func <ICell, string> getStringValue = cell =>
{
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
return(DateUtil.IsCellDateFormatted(cell) ?
cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff") :
cell.NumericCellValue.ToString());
}
return(cell?.StringCellValue ?? string.Empty);
}
return(null);
};
#endregion
var titleRow = sheet.GetRow(sheet.FirstRowNum);
var index = titleRow.FirstCellNum;
if (titleRow.GetCell(index++)?.StringCellValue == "途虎订单号" &&
titleRow.GetCell(index++)?.StringCellValue == "联系方式" &&
titleRow.GetCell(index++)?.StringCellValue == "微信号" &&
titleRow.GetCell(index++)?.StringCellValue == "微信昵称" &&
titleRow.GetCell(index++)?.StringCellValue == "客户姓名" &&
titleRow.GetCell(index++)?.StringCellValue == "车牌号" &&
titleRow.GetCell(index++)?.StringCellValue == "来源" &&
titleRow.GetCell(index++)?.StringCellValue == "发帖链接" &&
titleRow.GetCell(index++)?.StringCellValue == "审核状态" &&
titleRow.GetCell(index++)?.StringCellValue == "返现状态" &&
titleRow.GetCell(index++)?.StringCellValue == "备注" &&
titleRow.GetCell(index++)?.StringCellValue == "对应编号" &&
titleRow.GetCell(index++)?.StringCellValue == "发帖时间")
{
for (var rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
var cellIndex = row.FirstCellNum;
var item = new RebateConfigModel {
};
try
{
item.OrderId = int.Parse(getStringValue(row.GetCell(cellIndex++)));
item.UserPhone = getStringValue(row.GetCell(cellIndex++));
item.WXId = getStringValue(row.GetCell(cellIndex++));
item.WXName = getStringValue(row.GetCell(cellIndex++));
item.UserName = getStringValue(row.GetCell(cellIndex++));
item.CarNumber = getStringValue(row.GetCell(cellIndex++));
item.Source = getStringValue(row.GetCell(cellIndex++));
item.ContentUrl = getStringValue(row.GetCell(cellIndex++));
cellIndex++;
cellIndex++;
cellIndex++;
item.PrincipalPerson = getStringValue(row.GetCell(cellIndex++));
item.CreateTime = Convert.ToDateTime(getStringValue(row.GetCell(cellIndex++)));
result.Add(item);
}
catch (Exception ex)
{
}
}
}
else
{
msg = "导入模板有误";
}
return(Tuple.Create(result, msg));
}
/// <summary>
/// 导入第一个sheet的excel数据，转成DataTable
/// </summary>
/// <param name="fileUpload"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(HttpPostedFileBase fileUpload)
{
DataTable dt = new DataTable();
int startRow = 0;
try
{
if (fileUpload != null)
{
string extension = Path.GetExtension(fileUpload.FileName);
string filename = Path.GetFullPath(fileUpload.FileName);
IWorkbook workbook = null;
HttpPostedFile file = System.Web.HttpContext.Current.Request.Files[0];
MemoryStream mem = new MemoryStream();
mem.SetLength((int)file.ContentLength);
file.InputStream.Read(mem.GetBuffer(), 0, (int)file.ContentLength);
if (extension == ".xlsx")
{
workbook = new XSSFWorkbook(mem);
}
else if (extension == ".xls")
{
workbook = new HSSFWorkbook(mem);
}
else
{
throw new Exception("文件格式错误，只允许导入.xls、.xlsx文件");
}
// 非托管
mem.Close();
mem.Dispose();
ISheet sheet = workbook.GetSheetAt(0);
IRow firstRow = sheet.GetRow(0);
// 一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
//最后一行的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
//没有数据的行默认是null
if (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);
}
return(dt);
}
else
{
return(null);
}
}
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
catch (Exception ex)
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
{
// TODO: log
return(null);
}
}
/// <summary>
/// 类似于ExcelToDataTable将包含多个表的Excel转换为DataSet
/// </summary>
/// <param name="fileUpload"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(HttpPostedFileBase fileUpload)
{
try
{
if (fileUpload != null)
{
string extension = Path.GetExtension(fileUpload.FileName);
string filename = Path.GetFullPath(fileUpload.FileName);
IWorkbook workbook = null;
HttpPostedFile file = System.Web.HttpContext.Current.Request.Files[0];
MemoryStream mem = new MemoryStream();
mem.SetLength((int)file.ContentLength);
file.InputStream.Read(mem.GetBuffer(), 0, (int)file.ContentLength);
if (extension == ".xlsx")
{
workbook = new XSSFWorkbook(mem);
}
else if (extension == ".xls")
{
workbook = new HSSFWorkbook(mem);
}
else
{
throw new Exception("文件格式错误，只允许导入.xls、.xlsx文件");
}
mem.Close();
mem.Dispose();
DataSet ds = new DataSet();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = new DataTable(sheet.SheetName);
//寻找头列的位置
IRow headerRow = sheet.GetRow(sheet.FirstRowNum);
for (int j = 0; j < headerRow.LastCellNum; j++)
{
dt.Columns.Add(headerRow.Cells[j].StringCellValue);
}
for (int z = (sheet.FirstRowNum + 1); z <= sheet.LastRowNum; z++)
{
IRow row = sheet.GetRow(z);
DataRow dr = dt.NewRow();
if (row != null)
{
//有可能出现溢出错误 改为表头的数量
for (int m = row.FirstCellNum; m < dt.Columns.Count; m++)
{
if (row.GetCell(m) != null)
{
dr[m] = row.GetCell(m).ToString();
}
else
{
dr[m] = "";
}
}
dt.Rows.Add(dr);
}
else
{
continue;
}
}
ds.Tables.Add(dt);
}
return(ds);
}
else
{
return(null);
}
}
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
catch (Exception ex)
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
{
return(null);
}
}
public FileResult Download()
{
List <khachhang> likh = new List <khachhang>();
likh = db.khachhang.ToList();
XSSFWorkbook wb = new XSSFWorkbook();
// Tạo ra 1 sheet
ISheet sheet = wb.CreateSheet();
// Bắt đầu ghi lên sheet
// Tạo row
// Ghi tên cột ở row 1
var row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("Mã khách hàng");
row1.CreateCell(1).SetCellValue("Tên đăng nhập");
row1.CreateCell(2).SetCellValue("Mật khẩu");
row1.CreateCell(3).SetCellValue("Họ tên");
row1.CreateCell(4).SetCellValue("Số điện thoại");
row1.CreateCell(5).SetCellValue("Địa chỉ");
row1.CreateCell(6).SetCellValue("Email");
row1.CreateCell(7).SetCellValue("Quyền");
// bắt đầu duyệt mảng và ghi tiếp tục
int rowIndex = 2;
foreach (var item in likh)
{
// tao row mới
var newRow = sheet.CreateRow(rowIndex);
// set giá trị
newRow.CreateCell(0).SetCellValue(item.makhachhang);
newRow.CreateCell(1).SetCellValue(item.tendangnhap);
newRow.CreateCell(2).SetCellValue(item.matkhau);
newRow.CreateCell(3).SetCellValue(item.hoten);
newRow.CreateCell(4).SetCellValue(item.sodienthoai);
newRow.CreateCell(5).SetCellValue(item.diachi);
newRow.CreateCell(6).SetCellValue(item.email);
newRow.CreateCell(7).SetCellValue((double)item.quyen);
// tăng index
rowIndex++;
}
// xong hết thì save file lại
String filename = "D:\\FIleDoAn\\khachhang.xlsx";
FileInfo fi = new FileInfo(filename);
// kiểm tra tệp
if (fi.Exists)
{
fi.Delete();
}
FileStream fs = new FileStream(filename, FileMode.CreateNew);
wb.Write(fs);
byte[] fileBytes = System.IO.File.ReadAllBytes(filename);
string fileName = "khachhang.xlsx";
return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName));
}
public async Task <IActionResult> Upload(List <IFormFile> _file)
{
try
{
bool accessNotAllowed =
String.Equals(Request.Headers.First(x => x.Key == "Authorization").Value, "unauth");
if (accessNotAllowed)
{
return(Ok(new { success = false, mess = "Not authenticated" }));
}
IFormFile file = Request.Form.Files[0];
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'><tr>");
//for (int j = 0; j < cellCount; j++)
//{
// NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
// if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
// sb.Append("<th>" + cell.ToString() + "</th>");
//}
//sb.Append("</tr>");
//sb.AppendLine("<tr>");
using (DataContext dbContext = _context)
{
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
Student student = new Student();
IRow row = sheet.GetRow(i);
if (row == null || row.Cells.All(d => d.CellType == CellType.Blank))
{
throw new Exception(string.Format("Null row presnet in excel at row number , {0} ", i.ToString()));
}
;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.Cells.First(x => x.RowIndex == i && x.ColumnIndex == j);
switch (j)
{
case 0:
student.Hallticket = cell.StringCellValue;
break;
case 1:
student.Yearofjoin = Convert.ToInt32(cell.NumericCellValue);
break;
case 2:
student.Dept = cell.StringCellValue;
break;
case 3:
student.Section = cell.NumericCellValue.ToString();
break;
}
}
_context.Students.Add(student);
//sb.AppendLine("</tr>");
}
_context.SaveChanges();
}
//sb.Append("</table>");
}
}
}
catch (Exception ex)
{
return(Ok(new { success = false, mess = ex.Message }));
}
return(Ok(new { success = true, mess = "Successfully loaded data" }));
}
public static MemoryStream ExportToExcel(IQueryable data, List <ExcelColumnLayout> columnLayouts, Type columnsDataProviderContext, IValidationDictionary validationDictionary = null, bool useExcel2007AndAbove = false, bool isTree = false, int excelOutputLevel = 7)
{
var rowType = data.GetType().GetGenericArguments()[0];
var o = Activator.CreateInstance(rowType);
//var metadata = ModelMetadataProviders.Current.GetMetadataForType(() => o, rowType);
//Create new Excel workbook
//NOTE : XSSFWorkbook is for xslx format but its buggy in this version (2.1.3).
IWorkbook workbook;
if (useExcel2007AndAbove)
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
//Create new Excel sheet
var sheet = workbook.CreateSheet();
//Create a header row
var headerRow = sheet.CreateRow(0);
var propertiesDataProviders = new Dictionary <string, ExcelColumnDataProvider>();
int idx = 0;
int columnCount = 0;
var rowsFont = workbook.CreateFont();
rowsFont.Boldweight = (short)FontBoldWeight.Normal;
rowsFont.FontHeightInPoints = 12;
Dictionary <Type, ColumnDataRetreiverDelegate> columnsDataProviders = null;
if (columnsDataProviderContext != null)
{
object context;
try
{
context = Activator.CreateInstance(columnsDataProviderContext);
}
catch (Exception exp)
{
throw new Exception("Greewf : Error in creating '" + columnsDataProviderContext.Name + "' Type. Details : " + exp.Message);
}
if (context is IExcelColumnsDataProviderContext == false)
{
throw new Exception("Greewf : The passed type for 'columnsDataProviderContext' parameter of 'ExcelOutput' attribute should inherit from 'IColumnsDataProviderContext'");
}
columnsDataProviders = (context as IExcelColumnsDataProviderContext).GetColumnsDataProviders();
}
// header style
var headerCellStyle = workbook.CreateCellStyle();
var headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerFont.FontHeightInPoints = 12;
headerCellStyle.SetFont(headerFont);
headerCellStyle.FillBackgroundColor = HSSFColor.DarkBlue.Grey80Percent.Index;
headerCellStyle.VerticalAlignment = VerticalAlignment.Center;
headerRow.Height = 40 * 20;
foreach (var item in columnLayouts)
{
if (string.IsNullOrWhiteSpace(item.Id))
{
continue;
}
columnCount++;
var cell = headerRow.CreateCell(idx++);
cell.SetCellValue(item.Title);
cell.CellStyle = headerCellStyle;
if (propertiesDataProviders.ContainsKey(item.Id + item.Title))
{
throw new Exception("Greewf : The combination of column header('" + item.Id + "' and '" + item.Title + "') is not unique. ExcelOutput needs this combination to be unique ");
}
else
{
var colDataProvider = new ExcelColumnDataProvider();
colDataProvider.PropertyInfo = rowType.GetProperty(item.Id);
if (colDataProvider.PropertyInfo != null &&//PropertyInfo is null for unrelated columns (it presents in client only)
(colDataProvider.PropertyInfo.PropertyType.IsArray ||
(colDataProvider.PropertyInfo.PropertyType.IsGenericType && colDataProvider.PropertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(IEnumerable <>))))
{
if (columnsDataProviders == null)
{
throw new Exception("Greewf : You should pass 'ColumnDataProviders' parameter for 'ExcelOutput' attribute when you have some array columns.");
}
var colDataRetreiver = columnsDataProviders.FirstOrDefault(f => f.Key == colDataProvider.PropertyInfo.PropertyType);
if (colDataRetreiver.Key == null)
{
throw new Exception("Greewf : 'ColumnDataProviders' paramater (of 'ExcelOutput' attribute) doesn't have any relative function for array of type : " + colDataProvider.PropertyInfo.PropertyType.ToString());
}
else
{
colDataProvider.ColumnDataRetreiver = colDataRetreiver.Value;
}
}
else if (columnsDataProviders != null && colDataProvider.PropertyInfo != null)//simple column type providers if any
{
var provider = columnsDataProviders.FirstOrDefault(f => f.Key == colDataProvider.PropertyInfo.PropertyType);
if (provider.Key != null)
{
colDataProvider.ColumnDataRetreiver = provider.Value;
}
}
propertiesDataProviders.Add(item.Id + item.Title, colDataProvider);
}
}
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 1, 0, 1);
var lstIgnoreColumnAutoSize = new List <int>();
// extra width cell style
var extraWidthCellStyle = workbook.CreateCellStyle();
extraWidthCellStyle.WrapText = true;
int rowNumber = useExcel2007AndAbove ? 0 : 1;
ArrayList finalData = new ArrayList();
if (isTree)
{
finalData = FlatTreeData(data, excelOutputLevel, validationDictionary);
List <object> results = finalData.Cast <object>()
.ToList();
data = results.AsQueryable();
}
foreach (var rowData in data)
{
rowNumber++;
if (validationDictionary != null && rowNumber == 65537 && workbook is HSSFWorkbook) //excel 2003 does not supports rows more than 65534
{
validationDictionary.AddError("", "امکان ارسال بیش از 65535 ردیف به اکسل نمی باشد "); //TODO:we should change it to
return(null);
}
var row = sheet.CreateRow(rowNumber);
idx = 0;
foreach (var item in columnLayouts)
{
if (string.IsNullOrWhiteSpace(item.Id))
{
continue;
}
var colDataProvider = propertiesDataProviders[item.Id + item.Title];
var propertyValue = colDataProvider.PropertyInfo == null ? null : colDataProvider.PropertyInfo.GetValue(rowData, null);
ICell cell = null;
string value = null;
//if (colDataProvider.PropertyInfo == null)//for columns that are unrelated to server query. indeed they are only present in client
//{
// cell = row.CreateCell(idx++, CellType.String);
// value = "";
// cell.SetCellValue(value);
//}
if (colDataProvider.ColumnDataRetreiver != null)
{
colDataProvider.ColumnDataRetreiver(item, rowData, ref propertyValue);
}
if (propertyValue is DateTime || propertyValue is DateTime?)
{
cell = row.CreateCell(idx++, CellType.String);
if (propertyValue != null)
{
var dt = (DateTime)propertyValue;
if (dt == dt.Date)
{
value = Global.DisplayDate(dt);
}
else
{
value = Global.DisplayDateTime(dt);
}
}
cell.SetCellValue(value ?? "");
}
else if (propertyValue is bool || propertyValue is bool?)
{
cell = row.CreateCell(idx++, CellType.Boolean);
value = (bool?)propertyValue == true ? "بلی" : (bool?)propertyValue == false ? "خیر" : "";
cell.SetCellValue(value);
}
else if (propertyValue is int ||
propertyValue is int? ||
propertyValue is decimal ||
propertyValue is decimal? ||
propertyValue is double ||
propertyValue is double? ||
propertyValue is short ||
propertyValue is short? ||
propertyValue is float ||
propertyValue is float?)
{
cell = row.CreateCell(idx++, CellType.Numeric);
if (propertyValue != null)
{
cell.SetCellValue(Convert.ToDouble(propertyValue));
}
}
else
{
cell = row.CreateCell(idx++, CellType.String);
value = propertyValue == null ? "" : propertyValue.ToString();
cell.SetCellValue(value);
}
//NOTE : I don't know what the problem is but all the cell style are indeed ONE instance object!!!
cell.CellStyle.SetFont(rowsFont);
if ((value ?? "").Length > 200)
{
cell.CellStyle = extraWidthCellStyle;
lstIgnoreColumnAutoSize.Add(idx - 1);
}
}
}
//row.SetAttribute(new OpenXmlAttribute("outlineLevel", string.Empty, "1"));
for (int i = 0; i < columnCount; i++)
{
if (!lstIgnoreColumnAutoSize.Contains(i))
{
sheet.AutoSizeColumn(i);
}
else
{
sheet.SetColumnWidth(i, 150 * 256);
}
}
//sheet.IsRightToLeft = true;
sheet.IsRightToLeft = true;
sheet.PrintSetup.LeftToRight = false;
GroupData(finalData, sheet, isTree);
//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
output.Close();
return(output);
}
private IEnumerable <PersonalityShortQuestion> ReadShortQuestionsFromFile(string sheetName, XSSFWorkbook dysacWorkbook)
{
var listToReturn = new List <PersonalityShortQuestion>();
var sheet = dysacWorkbook.GetSheet(sheetName);
var titleIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "QuestionText").ColumnIndex;
var impactIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "IsNegative").ColumnIndex;
var traitIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Trait").ColumnIndex;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var title = row.GetCell(titleIndex).StringCellValue;
var impact = row.GetCell(impactIndex).StringCellValue;
var trait = row.GetCell(traitIndex).StringCellValue;
listToReturn.Add(new PersonalityShortQuestion {
Title = title, Impact = impact, Trait = trait
});
}
return(listToReturn);
}
public string GenerarArchivo(List <FiltroSeleccionado> Filtros, int ClienteId, int ObjetoId, int UsuarioConsultaId)
{
const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
var random = new Random();
string token = new string(Enumerable.Repeat(chars, 30).Select(s => s[random.Next(s.Length)]).ToArray());
string path = AppDomain.CurrentDomain.BaseDirectory;
string filename = token + ".csv";
string newfilepath = Path.Combine(path, "Temp", filename);
try
{
TableChart ch = (TableChart)GetDataObjeto(Filtros, ClienteId, ObjetoId, -1, UsuarioConsultaId, 0);
XSSFWorkbook wbExcel = new XSSFWorkbook();
XSSFSheet eSheet = (XSSFSheet)wbExcel.CreateSheet("Tabla");
XSSFRow rTitulos = (XSSFRow)eSheet.CreateRow(0);
XSSFRow rCurrent;
XSSFCell cCurrent;
XSSFCellStyle csEstilo;
XSSFFont fFuente;
XSSFColor cColor;
int iCell = 0;
int iRow = 1;
if (ch.Tipo != TipoChart.ClassicTable)
{
foreach (var c in ch.Columns)
{
cCurrent = (XSSFCell)rTitulos.CreateCell(iCell);
cCurrent.SetCellValue(c.title.ToString());
iCell++;
}
}
else
{
foreach (var c in ch.Valores[0])
{
cCurrent = (XSSFCell)rTitulos.CreateCell(iCell);
cCurrent.SetCellValue(c.Key.ToString());
iCell++;
}
}
foreach (var v in ch.Valores)
{
iCell = 0;
rCurrent = (XSSFRow)eSheet.CreateRow(iRow++);
foreach (var vc in v)
{
cCurrent = (XSSFCell)rCurrent.CreateCell(iCell);
if (vc.Value.ToString().Length != 0)
{
if (vc.Value.ToString().Substring(0, 1) == "●")
{
csEstilo = (XSSFCellStyle)wbExcel.CreateCellStyle();
fFuente = (XSSFFont)wbExcel.CreateFont();
cColor = new XSSFColor(ColorTranslator.FromHtml(vc.Value.ToString().Replace("●", "")));
fFuente.IsBold = true;
fFuente.SetColor(cColor);
fFuente.FontHeightInPoints = 16;
csEstilo.SetFont(fFuente);
csEstilo.Alignment = HorizontalAlignment.Center;
cCurrent.CellStyle = csEstilo;
cCurrent.SetCellValue(vc.Value.ToString().Substring(0, 1));
}
else
{
cCurrent.SetCellValue(vc.Value.ToString());
}
}
iCell++;
}
}
filename = token + ".xlsx";
FileStream File = new FileStream(Path.Combine(path, "Temp", filename), FileMode.Create, FileAccess.Write);
wbExcel.Write(File);
File.Close();
return(token);
}
catch (System.IO.IOException ex)
{
throw ex;
}
catch (Exception e)
{
throw e;
}
}
public string ExportacionCAP(string HtmlDoc, bool Soar)
{
var random = new Random();
string token = new string(Enumerable.Repeat("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 30).Select(s => s[random.Next(s.Length)]).ToArray());
XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet xs = (XSSFSheet)xwb.CreateSheet("CAP");
XSSFRow xcr = (XSSFRow)xs.CreateRow(0);
XSSFCell xcc;
XSSFCellStyle cstl;
XSSFFont cfnt;
XSSFColor ccol;
int iCell = 0;
int iRow = 1;
HtmlDocument ht = new HtmlDocument();
ht.LoadHtml(HtmlDoc);
HtmlNodeCollection Tables = ht.DocumentNode.SelectNodes("//body/table");
HtmlNodeCollection HeaderRows;
HtmlNodeCollection Rows;
HtmlNodeCollection ValueRows;
for (int t = 0; t < Tables.Count; t++)
{
if ((Soar && t == 0) || !Soar)
{
iCell = 0;
if (Tables.IndexOf(Tables[t]) != 0)
{
iRow += Soar?1:2;
}
xcr = (XSSFRow)xs.CreateRow(iRow - 1);
xcc = (XSSFCell)xcr.CreateCell(iCell);
HeaderRows = Tables[t].SelectNodes("./thead/tr/th");
foreach (HtmlNode TableHeader in HeaderRows)
{
xcc = (XSSFCell)xcr.CreateCell(iCell);
string[] arr = TableHeader.Attributes["style"].Value.ToString().Split(';');
int[] BackRgb = new int[3];
string[] BackRgbStr;
int[] FrontRgb = new int[3];
string[] FrontRgbStr;
foreach (string style in arr)
{
if (style.Trim().StartsWith("background-color:"))
{
BackRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < BackRgbStr.Length; i++)
{
BackRgb[i] = int.Parse(BackRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("color:"))
{
FrontRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < FrontRgbStr.Length; i++)
{
FrontRgb[i] = int.Parse(FrontRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
for (int i = 0; i < 3; i++)
{
if (BackRgb[i] == 0)
{
BackRgb[i] = 255;
}
}
cstl = (XSSFCellStyle)xwb.CreateCellStyle();
cfnt = (XSSFFont)xwb.CreateFont();
ccol = (!Soar? new XSSFColor(Color.FromArgb(BackRgb[0], BackRgb[1], BackRgb[2])): new XSSFColor((iCell != 0 && iCell != 1?Color.FromArgb(253, 234, 218): Color.FromArgb(255, 255, 255))));
cstl.SetFillForegroundColor(ccol);
cstl.FillPattern = FillPattern.SolidForeground;
ccol = (!Soar? new XSSFColor(Color.FromArgb(FrontRgb[0], FrontRgb[1], FrontRgb[2])): new XSSFColor(Color.FromArgb(0, 0, 255)));
cfnt.SetColor(ccol);
cfnt.IsBold = true;
cfnt.FontHeightInPoints = (short)(!Soar?14:10);
cstl.SetFont(cfnt);
if (Soar)
{
cstl.BorderBottom = BorderStyle.Thin;
}
if (!Soar)
{
cstl.Alignment = HorizontalAlignment.Center;
}
xcc.CellStyle = cstl;
xs.AutoSizeColumn(xcc.ColumnIndex);
}
xcc.SetCellValue(Soar ? TableHeader.InnerHtml.Split('(')[0] : TableHeader.InnerHtml);
iCell++;
}
}
Rows = Tables[t].SelectNodes("./tbody/tr");
iCell = 0;
foreach (HtmlNode Row in Rows)
{
xcr = (XSSFRow)xs.CreateRow(iRow++);
ValueRows = Row.SelectNodes("./td");
iCell = 0;
foreach (HtmlNode RowData in ValueRows)
{
string[] arr = RowData.Attributes["style"].Value.ToString().Split(';');
int[] BackRgb = new int[3];
string[] BackRgbStr;
int[] FrontRgb = new int[3];
string[] FrontRgbStr;
bool bRightBorder = false;
xcc = (XSSFCell)xcr.CreateCell(iCell++);
foreach (string style in arr)
{
if (style.Trim().StartsWith("background-color:"))
{
BackRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < BackRgbStr.Length; i++)
{
BackRgb[i] = int.Parse(BackRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("color:"))
{
FrontRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < FrontRgbStr.Length; i++)
{
FrontRgb[i] = int.Parse(FrontRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("border-right: 1px dashed black"))
{
bRightBorder = true;
}
for (int i = 0; i < 3; i++)
{
if (BackRgb[i] == 0)
{
BackRgb[i] = 255;
}
}
cstl = (XSSFCellStyle)xwb.CreateCellStyle();
cfnt = (XSSFFont)xwb.CreateFont();
ccol = new XSSFColor(Color.FromArgb(BackRgb[0], BackRgb[1], BackRgb[2]));
cstl.SetFillForegroundColor(ccol);
cstl.FillPattern = FillPattern.SolidForeground;
if (bRightBorder)
{
cstl.BorderRight = BorderStyle.DashDot;
}
ccol = new XSSFColor(Color.FromArgb(FrontRgb[0], FrontRgb[1], FrontRgb[2]));
cfnt.SetColor(ccol);
cfnt.FontHeightInPoints = (short)(!Soar ? 12 : 11);
cstl.SetFont(cfnt);
if (!Soar)
{
cstl.Alignment = HorizontalAlignment.Center;
}
xcc.CellStyle = cstl;
xs.AutoSizeColumn(xcc.ColumnIndex);
}
int val = int.MinValue;
bool bVal = int.TryParse(RowData.InnerHtml, out val);
if (bVal)
{
xcc.SetCellType(CellType.Numeric);
xcc.SetCellValue(Convert.ToInt32(RowData.InnerHtml));
}
else
{
xcc.SetCellValue(RowData.InnerHtml.ToUpper());
}
}
}
}
FileStream File = new FileStream(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Temp", token + ".xlsx"), FileMode.Create, FileAccess.Write);
xwb.Write(File);
File.Close();
return(token);
}
private DataTable ParseExcelFile(string fileName)
{
XSSFWorkbook wb;
XSSFSheet sh;
String Sheet_name;
using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(fs);
Sheet_name = wb.GetSheetAt(0).SheetName; //get first sheet name
}
DataTable DT = new DataTable();
DT.Rows.Clear();
DT.Columns.Clear();
// get sheet
sh = (XSSFSheet)wb.GetSheet(Sheet_name);
int i = 0;
while (sh.GetRow(i) != null)
{
// add neccessary columns
if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
DT.Columns.Add("", typeof(string));
}
}
// add row
DT.Rows.Add();
// write row value
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
// TODO: you can add more cell types capatibility, e. g. formula
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Numeric:
DT.Rows[i][j] = sh.GetRow(i).GetCell(j).NumericCellValue;
//dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.String:
DT.Rows[i][j] = sh.GetRow(i).GetCell(j).StringCellValue;
break;
}
}
}
i++;
}
return(DT);
}
/// <summary>
/// Excel导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <T> ImportFromExcel(string filePath)
{
string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar);
List <T> list = new List <T>();
HSSFWorkbook hssfWorkbook = null;
XSSFWorkbook xssWorkbook = null;
ISheet sheet = null;
using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read))
{
switch (Path.GetExtension(filePath))
{
case ".xls":
hssfWorkbook = new HSSFWorkbook(file);
sheet = hssfWorkbook.GetSheetAt(0);
break;
case ".xlsx":
xssWorkbook = new XSSFWorkbook(file);
sheet = xssWorkbook.GetSheetAt(0);
break;
default:
throw new Exception("不支持的文件格式");
}
}
IRow columnRow = sheet.GetRow(1); // 第二行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Single":
case "System.Nullable`1[System.Single]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
hssfWorkbook?.Close();
xssWorkbook?.Close();
return(list);
}
public async Task <JsonResult> ExportToExcel_TelemoveisCartoes([FromBody] List <TelemoveisCartoesView> Lista)
{
JObject dp = (JObject)Lista[0].ColunasEXCEL;
string sWebRootFolder = _generalConfig.FileUploadFolder + "TelemoveisCartoes\\" + "tmp\\";
string user = User.Identity.Name;
user = user.Replace("@", "_");
user = user.Replace(".", "_");
string sFileName = @"" + user + "_ExportEXCEL.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Telemóveis Cartões");
IRow row = excelSheet.CreateRow(0);
int Col = 0;
if (dp["numCartao"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Nº Cartão");
Col = Col + 1;
}
if (dp["tipoServico_Show"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Tipo Serviço");
Col = Col + 1;
}
if (dp["estado_Show"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Estado");
Col = Col + 1;
}
if (dp["contaSuch"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Conta SUCH");
Col = Col + 1;
}
if (dp["codRegiao"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Código Região");
Col = Col + 1;
}
if (dp["codAreaFuncional"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Código Área Funcional");
Col = Col + 1;
}
if (dp["codCentroResponsabilidade"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue("Código Centro Responsabilidade");
Col = Col + 1;
}
if (dp != null)
{
int count = 1;
foreach (TelemoveisCartoesView item in Lista)
{
Col = 0;
row = excelSheet.CreateRow(count);
if (dp["numCartao"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.NumCartao);
Col = Col + 1;
}
if (dp["tipoServico_Show"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.TipoServico_Show);
Col = Col + 1;
}
if (dp["estado_Show"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.Estado_Show);
Col = Col + 1;
}
if (dp["contaSuch"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.ContaSuch);
Col = Col + 1;
}
if (dp["codRegiao"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.CodRegiao);
Col = Col + 1;
}
if (dp["codAreaFuncional"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.CodAreaFuncional);
Col = Col + 1;
}
if (dp["codCentroResponsabilidade"]["hidden"].ToString() == "False")
{
row.CreateCell(Col).SetCellValue(item.CodCentroResponsabilidade);
Col = Col + 1;
}
count++;
}
}
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(Json(sFileName));
}
static void Main(string[] args)
{
Console.WriteLine("输入查找路径:");
var inputdir = Console.ReadLine() ?? "Resources";
var stringCount = 0;
var excelName = inputdir + "/" + inputdir.Substring(inputdir.LastIndexOf(Path.DirectorySeparatorChar) + 1) +
".xlsx";
XSSFWorkbook workbook = null;
FileStream excelStream = null;
if (File.Exists(excelName))
{
excelStream = new FileStream(excelName, FileMode.Open);
excelStream.Position = 0;
workbook = new XSSFWorkbook(excelStream);
excelStream.Close();
}
else
{
workbook = new XSSFWorkbook();
}
var sheet_jp = workbook.Sheet("jp"); //创建工作表
var sheet_jp_delta = workbook.Sheet("jp_delta"); //创建工作表
var sheet_old = workbook.Sheet("old");
// var sheet3 = workbook.Sheet("ini");
// CollectTranslateJsonToExcel("old-json.txt", sheet2,
// "/Users/men/ws/c2/Resources/vitamin/data/v2040/language_cn.ini", sheet3);
var textName = inputdir + "/" + inputdir.Substring(inputdir.LastIndexOf(Path.DirectorySeparatorChar) + 1) +
".txt";
var textStream = new StreamWriter(textName, false, Encoding.UTF8);
var fini = new StreamWriter(textName + ".ini", false, Encoding.UTF8);
var row = sheet_jp.Row(0);
row.Cell((int)ColumIdx.jp).SetCellValue("jp");
row.Cell(1).SetCellValue("trans");
row.Cell(2).SetCellValue("line");
row.Cell(3).SetCellValue("path");
row.Cell(4).SetCellValue("src");
row.Cell(5).SetCellValue("idx");
row = sheet_jp_delta.Row(0);
row.Cell((int)ColumIdx.jp).SetCellValue("jp");
row.Cell(1).SetCellValue("trans");
row.Cell(2).SetCellValue("line");
row.Cell(3).SetCellValue("path");
row.Cell(4).SetCellValue("src");
row.Cell(5).SetCellValue("idx");
foreach (var f in Directory.GetFiles(inputdir, "*.*", SearchOption.AllDirectories)
.Where(f => false
// || f.EndsWith(".xml")
|| f.EndsWith(".ccb")
// || f.EndsWith(".html")
// || f.EndsWith(".plist")
)
)
{
//var stream = new StreamReader(f);
var txt = File.ReadAllText(f);
// <string>xxx</string>
var matches3 = Regex.Matches(txt, ">[^>]*" + regular + "+[^<]*<", RegexOptions.Multiline);
foreach (var i in matches3)
{
++delta_lang_idx;
++stringCount;
row = sheet_jp.Row(stringCount);
var s0 = i.ToString()
.Replace(">", "")
.Replace("<", "")
.Replace("\r", "\\r")
.Replace("\n", "\\n")
;
// row.Cell(0).SetCellValue(s0); // jp
row.Cell(1).SetCellValue(s0); // zh
row.Cell(2).SetCellValue("");
row.Cell(3).SetCellValue(f.upath());
row.Cell(5).SetCellValue(string.Format("{0:000000}", (delta_lang_idx)));
var s = string.Format("{0:000000}#{1}#{2}#{3}\n", delta_lang_idx, f.upath(), 0, s0);
textStream.Write(s);
fini.WriteLine(string.Format("{0:000000}={1}", delta_lang_idx, s0));
Console.WriteLine("{0}:{1}:{2}:{3}", stringCount, 0, i, f.upath());
}
textStream.Flush();
} // foreach
Delta(sheet_jp, sheet_old, sheet_jp_delta);
textStream.Close();
if (File.Exists(excelName))
{
File.Delete(excelName);
}
excelStream = new FileStream(excelName, FileMode.OpenOrCreate);
excelStream.Position = 0;
workbook.Write(excelStream);
excelStream.Close();
fini.Close();
Console.WriteLine("按 Enter 退出");
Console.ReadLine();
}
internal void ImportTraits(Dictionary <string, string> jobCategoryDictionary, XSSFWorkbook dysacWorkbook, string timestamp)
{
var traits = ReadTraitsFromFile("Trait", dysacWorkbook);
_personalityTraitContentItemDictionary = traits.Select(z => z.Title).Select(jc => new { Id = _generator.GenerateUniqueId(), Title = jc }).ToDictionary(y => y.Title, y => y.Id);
PersonalityTraitContentItems = traits.Select(x => new PersonalityTraitContentItem(x.Title, timestamp, _personalityTraitContentItemDictionary[x.Title])
{
EponymousPart = new PersonalityTraitPart
{
Description = new TextField(x.Description),
JobCategories = contentPickerFactory.CreateContentPickerFromContent("JobCategory", jobCategoryDictionary.Where(z => x.JobCategories.Select(y => y.ToLowerInvariant()).Contains(z.Key.ToLowerInvariant())).Select(k => k.Key.Trim()))
}
}).ToList();
}
//上传人员信息
public JsonResult UploadUserFile()
{
var retModel = new JsonReturnModel();
try
{
if (Request.Files == null || Request.Files.Count == 0)
{
retModel.AddError("errorMessage", Common.GetLanguageValueByParam("请选择您要上传的附件！", "PRCommon", "PRItemType", Userinfo.language));
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
HttpPostedFileBase prfile = Request.Files[0];
string fileName = prfile.FileName.Substring(prfile.FileName.LastIndexOf("\\") + 1, prfile.FileName.Length - (prfile.FileName.LastIndexOf("\\")) - 1);
if (!fileName.ToLower().Contains(".xls") && !fileName.ToLower().Contains(".xlsx"))
{
retModel.AddError("errorMessage", "只能上传Excel文件！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
string filePath = ConfigurationManager.AppSettings["UploadPath"] + fileName;
prfile.SaveAs(filePath);
//获取数据库 所有的用户信息
List <USER> allUser = UserBll.GetAllUserInfo();
List <USER> list = new List <USER>();
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
IWorkbook workbook = null;
if (fileName.ToLower().Contains(".xlsx"))
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);
int rowNum = sheet.PhysicalNumberOfRows;
//获取整个组织架构
List <B_ORGANIZATIONALSTRUCTURE> dataList = OrganizationalStructureBll.GetOrganizationalStructureList();
for (int i = 0; i < rowNum; i++)
{
IRow row = sheet.GetRow(i);
if (i != 0)
{
USER model = new USER();
if (row.GetCell(0) != null)
{
model.B_JOBNUMBER = row.GetCell(0) != null?row.GetCell(0).ToString().Trim() : "";
model.B_CHINESENAME = row.GetCell(1) != null?row.GetCell(1).ToString().Trim() : "";
model.B_ENGLISHNAME = row.GetCell(2) != null?row.GetCell(2).ToString().Trim() : "";
model.B_CENTRE = row.GetCell(3) != null?row.GetCell(3).ToString().Trim() : "";
model.B_DEPARTMENT = row.GetCell(4) != null?row.GetCell(4).ToString().Trim() : "";
model.B_SENIORMANAGER = row.GetCell(5) != null?row.GetCell(5).ToString().Trim() : "";
model.B_DIRECTOR = row.GetCell(6) != null?row.GetCell(6).ToString().Trim() : "";
model.B_VP = row.GetCell(7) != null?row.GetCell(7).ToString().Trim() : "";
model.B_AFFILIATEDCOMPANY = row.GetCell(8) != null?row.GetCell(8).ToString().Trim() : "";
//根据用户名称判断用户是否存在
int count = allUser.Where(x => x.LOGIN_NAME.ToUpper() == model.B_ENGLISHNAME.ToUpper()).Count();
if (count == 0)
{
retModel.AddError("errorMessage", i + 1 + "行上传的用户不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
//判断中心是否存在
B_ORGANIZATIONALSTRUCTURE centreObj = dataList.Where(x => x.B_NODENAME == model.B_CENTRE && x.B_NODELEVEL == 2).FirstOrDefault();
if (centreObj == null)
{
retModel.AddError("errorMessage", i + 1 + "行上传的中心不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
//判断部门是否存在
List <B_ORGANIZATIONALSTRUCTURE> organizationalStructureList = new List <B_ORGANIZATIONALSTRUCTURE>();
if (!string.IsNullOrEmpty(model.B_DEPARTMENT))
{
OrganizationalStructureBll.GetChildByParent(inn, centreObj.B_NODECODE, organizationalStructureList, dataList);
int countDepartment = organizationalStructureList.Where(x => x.B_NODENAME == model.B_DEPARTMENT).Count();
if (countDepartment == 0)
{
retModel.AddError("errorMessage", i + 1 + "行上传的部门不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
}
//判断上传的高级经理是否存在
if (!string.IsNullOrEmpty(model.B_SENIORMANAGER))
{
var itemSeniorManager = allUser.Where(x => x.LOGIN_NAME.ToUpper() == model.B_SENIORMANAGER.ToUpper()).FirstOrDefault();
if (itemSeniorManager == null)
{
retModel.AddError("errorMessage", i + 1 + "行上传的高级经理不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
model.B_SENIORMANAGER = itemSeniorManager.FIRST_NAME;
}
//判断上传的总监是否存在
if (!string.IsNullOrEmpty(model.B_DIRECTOR))
{
var itemDirector = allUser.Where(x => x.LOGIN_NAME.ToUpper() == model.B_DIRECTOR.ToUpper()).FirstOrDefault();
if (itemDirector == null)
{
retModel.AddError("errorMessage", i + 1 + "行上传的总监不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
model.B_DIRECTOR = itemDirector.FIRST_NAME;
}
//判断上传的VP是否存在
if (!string.IsNullOrEmpty(model.B_VP))
{
var itemVP = allUser.Where(x => x.LOGIN_NAME.ToUpper() == model.B_VP.ToUpper()).FirstOrDefault();
if (itemVP == null)
{
retModel.AddError("errorMessage", i + 1 + "行上传的VP不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
model.B_VP = itemVP.FIRST_NAME;
}
//判断上传所属公司是否正确
if (!string.IsNullOrEmpty(model.B_AFFILIATEDCOMPANY))
{
List <string> arrList = model.B_AFFILIATEDCOMPANY.Split(';').Where(x => x != "").ToList();
foreach (var item in arrList)
{
if (item != "博郡" && item != "思致")
{
retModel.AddError("errorMessage", i + 1 + "行上传的所属公司不正确！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
}
}
list.Add(model);
}
}
}
Innovator adminInn = WorkFlowBll.GetAdminInnovator();
//修改数据库中的数据
if (list != null && list.Count > 0 && adminInn != null)
{
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
//string userName = item.B_ENGLISHNAME.ToUpper() + " " + "(" + item.B_CHINESENAME + ")";
USER userObj = allUser.Where(x => x.LOGIN_NAME.ToUpper() == item.B_ENGLISHNAME.ToUpper()).First();
var user = adminInn.newItem("User", "edit");
user.setAttribute("id", userObj.ID);
user.setProperty("b_jobnumber", item.B_JOBNUMBER);
user.setProperty("b_chinesename", item.B_CHINESENAME);
user.setProperty("b_englishname", item.B_ENGLISHNAME);
user.setProperty("b_centre", item.B_CENTRE);
user.setProperty("b_department", item.B_DEPARTMENT);
user.setProperty("b_seniormanager", item.B_SENIORMANAGER);
user.setProperty("b_director", item.B_DIRECTOR);
user.setProperty("b_vp", item.B_VP);
user.setProperty("b_affiliatedcompany", item.B_AFFILIATEDCOMPANY);
var result = user.apply();
}
}
}
}
catch (Exception ex)
{
retModel.AddError("errorMessage", ex.Message);
}
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
public async Task <IActionResult> GenerateBlankTemplateAsync()
{
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
var IntegerFields = new List <string> {
"*** Surveyor Number", "DeedVolume", "DeedPage", "AutomatedFileNumber"
};
var FileName = $"PLSO_Upload_Template {DateTime.Now.ToString("yy-MMdd")}-{DateTime.Now.Ticks}.xlsx";
try {
var Columns = await excelTemplateRepo.GetTemplateColumnsAsync();
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PLSO Record Import");
IsBold = CreateBoldStyle(workbook);
IsBlue = CreateBlueStyle(workbook);
IsValidation = CreateValidationStyle(workbook);
IsInteger = CreateIntegerStyle(workbook);
int RowIndex = 0;
IRow row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
sheet1.SetColumnWidth(col.ColumnIndex - 1, (col.ColumnWidth * 256));
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.DisplayName);
} // foreach of the columns on the Display Name row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.ExampleData);
Cell.CellStyle = IsBlue;
} // foreach of the columns on the Example Data row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue((col.IsRequired ? "REQUIRED: " : "") + col.Validation);
Cell.CellStyle = IsValidation;
Cell.CellStyle.WrapText = true;
} // foreach of the columns on the Validation row
for (var index = 1; index < 6; index++)
{
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
if (col.FieldName == "SurveyDate")
{
IDataFormat dataFormat = workbook.CreateDataFormat();
Cell.CellStyle.DataFormat = dataFormat.GetFormat("M/d/yyyy");
}
else if (IntegerFields.Contains(col.FieldName))
{
Cell.SetCellType(CellType.Numeric);
Cell.CellStyle = IsInteger;
}
else
{
Cell.SetCellType(CellType.String);
}
} // foreach of the columns on the Display Name row
} // for 5 rows add formatting and make the cell bold if required
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
workbook.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", FileName));
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">excel的文件名（完整路径）</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
{
IWorkbook workbook = null;
ISheet sheet = null;
FileStream fs = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
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);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
public void TestGetOrCreateColumn()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet 1");
ColumnHelper columnHelper = sheet.GetColumnHelper();
// Check POI 0 based, OOXML 1 based
CT_Col col = columnHelper.GetOrCreateColumn1Based(3, false);
Assert.IsNotNull(col);
Assert.IsNull(columnHelper.GetColumn(1, false));
Assert.IsNotNull(columnHelper.GetColumn(2, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(3, false));
Assert.IsNull(columnHelper.GetColumn(3, false));
CT_Col col2 = columnHelper.GetOrCreateColumn1Based(30, false);
Assert.IsNotNull(col2);
Assert.IsNull(columnHelper.GetColumn(28, false));
Assert.IsNotNull(columnHelper.GetColumn(29, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(30, false));
Assert.IsNull(columnHelper.GetColumn(30, false));
}
private void Button1_Click(object sender, EventArgs e)
{
QueueThread.Push(() =>
{
EnableControl(false);
ControlFactory.GetForm <Form1>().SetStatusMessage("The excel is started to create.");
DataGridView grid = ControlFactory.GetForm <Form1>().GetGridData();
QueueThread.InvokeControl(grid, () =>
{
grid.Enabled = false;
});
//Create workbook
IWorkbook book = new XSSFWorkbook();
//Create font
IFont boldFont = book.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle boldStyle = book.CreateCellStyle();
boldStyle.SetFont(boldFont);
boldStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
int rowidx = 0;
//Create sheet;
ISheet sheet = book.CreateSheet("TEST");
//Header
IRow row = sheet.CreateRow(rowidx++);
for (int j = 1; j < grid.ColumnCount; j++)
{
String data = grid.Columns[j].HeaderText;
ICell cell = row.CreateCell(j);
cell.SetCellValue(data);
cell.CellStyle = boldStyle;
}
//Contents
for (int i = 0; i < grid.RowCount; i++)
{
DataGridViewRow gridrow = grid.Rows[i];
DataGridViewCheckBoxCell checkcehll = gridrow.Cells[0] as DataGridViewCheckBoxCell;
if (checkcehll.Value == null || !((bool)checkcehll.Value))
{
continue;
}
row = sheet.CreateRow(rowidx++);
for (int j = 1; j < grid.ColumnCount; j++)
{
String data = grid[j, i].Value.ToString();
ICell cell = row.CreateCell(j);
cell.SetCellValue(data);
}
}
ControlFactory.GetForm <Form1>().SetStatusMessage("The excel is be creating.");
//Create excel
using (FileStream stream = new FileStream(Path.GetDirectoryName(Application.ExecutablePath) + "\\TEST.xlsx", FileMode.OpenOrCreate, FileAccess.Write))
{
book.Write(stream);
}
ControlFactory.GetForm <Form1>().SetStatusMessage("The excel is be compressing.");
//compress zip
using (ZipFile zip = new ZipFile())
{
for (int i = 0; i < 1; i++)
{
using (MemoryStream memory = new MemoryStream())
{
book.Write(memory);
byte[] data = memory.ToArray();
zip.AddEntry("Test" + i + ".xlsx", data);
}
}
zip.Save(Path.GetDirectoryName(Application.ExecutablePath) + "\\TEST.zip");
}
ControlFactory.GetForm <Form1>().HideStatusMessage();
QueueThread.InvokeControl(grid, () =>
{
grid.Enabled = true;
});
EnableControl(true);
});
}
public override void Read(Table table)
{
var xls = table.Attributes.GetAttribute <XLS>();
var filePath = xls.filePath;
var sheetName = xls.sheetName;
var titleRowIndex = xls.titleRow - 1;
var dataBeginRowIndex = xls.dataBeginRow - 1;
var dataSet = new List <object[]>();
var dataSetWidth = table.Fields.Count;
var indexKeys = new HashSet <string>();
foreach (var index in table.Attributes.GetAttributes <Index>())
{
foreach (var dataKey in index.IndexFields)
{
foreach (var field in table.Fields)
{
if (field.Name.Equals(dataKey))
{
indexKeys.Add(field.DataField);
}
}
}
}
var dataKeyList = new List <string>();
var dataKey2FieldSchema = new Dictionary <string, TableField>();
foreach (var field in table.Fields)
{
dataKeyList.Add(field.DataField);
dataKey2FieldSchema.Add(field.DataField, field);
}
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var ext = Path.GetExtension(filePath).ToLower();
IWorkbook workbook = null;
if (ext == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
else if (ext == ".xls")
{
workbook = new HSSFWorkbook(stream);
}
else
{
ReportXlsError("未知的文件类型!", filePath);
return;
}
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
ReportXlsError("文件未找到表:" + sheetName + "。", filePath, sheetName);
return;
}
var titleRow = sheet.GetRow(titleRowIndex);
if (titleRow == null)
{
ReportXlsError("标题行不存在，无法导出。", filePath, sheetName, titleRowIndex);
return;
}
var fieldName2CellIndex = new Dictionary <string, int>();
var cellIndex2FieldName = new Dictionary <int, string>();
//title
for (var i = 0; i <= titleRow.LastCellNum; i++)
{
var cell = titleRow.GetCell(i);
if (cell == null || cell.CellType == CellType.Blank)
{
continue;
}
if (cell.CellType == CellType.String)
{
if (fieldName2CellIndex.ContainsKey(cell.StringCellValue))
{
ReportXlsError("标题列名重复出现。", filePath, sheetName, titleRowIndex, i);
}
else
{
fieldName2CellIndex.Add(cell.StringCellValue, i);
cellIndex2FieldName.Add(i, cell.StringCellValue);
}
}
else
{
ReportXlsError("标题列内容不是字符格式。", filePath, sheetName, titleRowIndex, i);
}
}
foreach (var fieldName in dataKey2FieldSchema.Keys)
{
if (!fieldName2CellIndex.ContainsKey(fieldName))
{
ReportXlsError("标题行中未找到列 " + fieldName + "。", filePath, sheetName, titleRowIndex);
}
}
//data
var uniqueChecker = new Dictionary <int, Dictionary <object, List <int> > >();
for (var rowIndex = dataBeginRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
var colCount = 0;
for (var j = 0; j < dataKeyList.Count; j++)
{
if (fieldName2CellIndex.ContainsKey(dataKeyList[j]))
{
var cellData = row.GetCell(fieldName2CellIndex[dataKeyList[j]]);
if (cellData != null && cellData.CellType != CellType.Blank)
{
colCount++;
}
}
}
if (colCount == 0)
{
continue;
}
var dataSetRow = new object[dataSetWidth];
for (var j = 0; j < dataKeyList.Count; j++)
{
var linkName = dataKeyList[j];
if (!fieldName2CellIndex.ContainsKey(linkName))
{
continue;
}
var cellIndex = fieldName2CellIndex[linkName];
var cellData = row.GetCell(cellIndex);
var field = dataKey2FieldSchema[linkName];
var isUnique = field.Attributes.GetAttribute <Unique>() != null;
var isBaseType = BaseUtil.IsBaseType(field.Type) && field.TypeDefined == null;
string fieldError = null;
object fieldValue = null;
var errors = new List <string>();
if (field.IsArray)
{
fieldValue = GetArray(field.Attributes, field.Type, field.TypeDefined, cellData, errors);
}
else
{
if (field.TypeDefined is Model.Struct)
{
fieldValue = GetStruct(field.Attributes, field.TypeDefined as Struct, cellData, errors);
}
else if (field.TypeDefined is Model.Enum)
{
fieldValue = GetEnum(field.TypeDefined as Model.Enum, cellData, errors);
}
else if (isBaseType)
{
var isIndex = indexKeys.Contains(field.DataField);
var isNullable = field.Attributes.HasAttribute <Model.Attributes.Nullable>();
var defaultValue = field.DefaultValue;
if (cellData == null || cellData.CellType == CellType.Blank)
{
if (!isNullable || isUnique)
{
errors.Add(String.Format("内容不允许为空!"));
}
else if (isIndex)
{
errors.Add(String.Format("索引字段不允许为空!"));
}
else
{
fieldValue = defaultValue != null ? defaultValue : 0;
}
}
else if (BaseUtil.IsBaseType(field.Type))
{
fieldValue = GetScalar(field.Type, cellData, errors);
}
}
}
if (errors.Count > 0)
{
fieldError = string.Join("\n", errors);
}
if (fieldError != null)
{
ReportXlsError(fieldError, filePath, sheetName, rowIndex, cellIndex);
}
dataSetRow[j] = fieldValue;
if (isBaseType && isUnique && fieldValue != null)
{
if (!uniqueChecker.ContainsKey(cellIndex))
{
uniqueChecker.Add(cellIndex, new Dictionary <object, List <int> >());
}
if (!uniqueChecker[cellIndex].ContainsKey(fieldValue))
{
uniqueChecker[cellIndex].Add(fieldValue, new List <int>());
}
uniqueChecker[cellIndex][fieldValue].Add(rowIndex);
}
}
dataSet.Add(dataSetRow);
}
foreach (var cellIndex in uniqueChecker.Keys)
{
var formatedCellIndex = FormatXlsColumnName(cellIndex);
foreach (var val in uniqueChecker[cellIndex].Keys)
{
if (uniqueChecker[cellIndex][val].Count > 1)
{
var txts = new List <string>();
var firstRowIndex = uniqueChecker[cellIndex][val][0];
foreach (var rowIndex in uniqueChecker[cellIndex][val])
{
txts.Add((rowIndex + 1).ToString() + "行");
}
ReportXlsError(String.Format("{0} 列在 {1} 出现了相同的内容:\"{2}\"。", cellIndex2FieldName[cellIndex], string.Join(",", txts), val), filePath, sheetName);
}
}
}
}
}
public XSSFFormulaUtils(XSSFWorkbook wb)
{
_wb = wb;
_fpwb = XSSFEvaluationWorkbook.Create(_wb);
}
public static void WriteToExcel(string filePath, string[] ContentArr, string[] ADContent = null, string[] DAContent = null)
{
if (ContentArr == null || ContentArr.Count() == 0)
{
return;
}
if (ADContent != null && ADContent.Count() == 0)
{
return;
}
if (DAContent != null && DAContent.Count() == 0)
{
return;
}
//创建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的文件格式创建对应的类
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
}
ICellStyle style1 = wb.CreateCellStyle(); //样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true; //自动换行
ICellStyle style2 = wb.CreateCellStyle(); //样式
IFont font1 = wb.CreateFont(); //字体
font1.FontName = "楷体";
font1.Color = HSSFColor.Red.Index; //字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal; //字体加粗样式
style2.SetFont(font1); //样式里的字体设置具体的字体样式
//设置背景色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
ICellStyle dateStyle = wb.CreateCellStyle(); //样式
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置数据显示格式
IDataFormat dataFormatCustom = wb.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
//创建一个表单
ISheet sheet = wb.CreateSheet("总表");
//设置列宽
int[] columnWidth = { 20, 20, 20, 20, 20, 20, 20 };
for (int i = 0; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, 256 * columnWidth[i]);
}
//测试数据
int rowCount = ContentArr.Count(), columnCount = (ContentArr[0].Split(',')).Count();
IRow row;
ICell cell;
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = ContentArr[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
ISheet sheet2 = wb.CreateSheet("DA表");
int[] columnWidth1 = { 20, 20, 20, 20 };
for (int i = 0; i < columnWidth1.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet2.SetColumnWidth(i, 256 * columnWidth1[i]);
}
//测试数据
rowCount = DAContent.Count(); columnCount = (DAContent[0].Split(',')).Count();
for (int i = 0; i < rowCount; i++)
{
row = sheet2.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = DAContent[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
ISheet sheet3 = wb.CreateSheet("AD表");
int[] columnWidth2 = { 20, 20, 20, 20 };
for (int i = 0; i < columnWidth2.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet3.SetColumnWidth(i, 256 * columnWidth2[i]);
}
//测试数据
rowCount = ADContent.Count(); columnCount = (ADContent[0].Split(',')).Count();
for (int i = 0; i < rowCount; i++)
{
row = sheet3.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = ADContent[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Close();
}
catch (Exception e)
{
return;
}
}
/// <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>(); // 转换后的集合
var keys2 = cellHeard.Keys;
var keys = new List <string>();
foreach (var item in keys2)
{
keys.Add(item);
}
try
{
using (FileStream fs = File.OpenRead(filePath))
{
IWorkbook workbook = null;
ISheet sheet = null;
if (Regex.IsMatch(filePath, ".xls$")) // 2003
{
workbook = new HSSFWorkbook(fs);
sheet = (HSSFSheet)workbook.GetSheetAt(0);
}
else
{
workbook = new XSSFWorkbook(fs);
sheet = (XSSFSheet)workbook.GetSheetAt(0);
}
for (int i = 2; i <= sheet.LastRowNum; i++) // 从2开始，第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 - 1) + "行数据转换异常：";
}
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 - 1) + "行数据转换异常：";
}
errStr += cellHeard[keys[j]] + "列；";
}
}
}
}
// 若有错误信息，就添加到错误信息里
if (errStr.Length > 0)
{
errorMsg.AppendLine(errStr);
}
else
{
enlist.Add(en);
}
}
}
return(enlist);
}
catch (Exception ex)
{
throw ex;
}
}
public override bool Execute(Framework.Task.Task task, ref string errmsg)
{
bool result = true;
switch (task.Command)
{
case "export":
YwTxjsService epService2 = new YwTxjsService();
XSSFWorkbook book2 = epService2.Export();
if (task.paralist == null)
{
task.paralist = new System.Collections.ArrayList();
}
task.paralist.Add(book2);
break;
case "exportExample":
YwTxjsService epService1 = new YwTxjsService();
XSSFWorkbook book1 = epService1.ExportExample();
if (task.paralist == null)
{
task.paralist = new System.Collections.ArrayList();
}
task.paralist.Add(book1);
break;
case "importSave":
result = DealImport(task, ref errmsg);
if (result == false)
{
return(false);
}
break;
case "import":
task.Width = 400;
task.Height = 250;
task.PagePath = "TxjsImport.aspx";
break;
case "ucreate":
task.Width = 600;
task.Height = 400;
task.PagePath = "TxjsEdit.aspx";
break;
case "uedit":
task.Width = 600;
task.Height = 400;
task.PagePath = "TxjsEdit.aspx";
task.PageParam = task.CommandArgument;
break;
case "ushow":
task.Width = 600;
task.Height = 400;
task.PagePath = "TxjsEdit.aspx";
task.PageParam = task.CommandArgument;
break;
case "udelete":
Delete(task, ref errmsg);
break;
case "save":
Save(task, ref errmsg);
break;
}
return(true);
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
public static DataSet LoadExcel(Stream stream, string fileName, int titleRowIndex, int dataRowIndex, bool ignoreCellException)
{
//打开文件
IWorkbook book = null;
IFormulaEvaluator evaluator = null;
string ext = Path.GetExtension(fileName);
if (YZStringHelper.EquName(ext, ".xlsx"))
{
book = new XSSFWorkbook(stream);
evaluator = new XSSFFormulaEvaluator(book);
}
if (book == null)
{
book = new HSSFWorkbook(stream);
evaluator = new HSSFFormulaEvaluator(book);
}
DataSet dataset = new DataSet();
for (int i = 0; i < book.NumberOfSheets; i++)
{
ISheet sheet = (ISheet)book.GetSheetAt(i);
DataTable table = new DataTable(sheet.SheetName);
dataset.Tables.Add(table);
if (sheet.LastRowNum != 0)
{
for (int r = 0; r <= sheet.LastRowNum; r++)
{
IRow row = (IRow)sheet.GetRow(r);
if (row == null) //存在row为null的情况
{
break; // continue;
}
DataRow dataRow = null;
bool containsValue = false;
for (int c = 0; c < row.LastCellNum; c++)
{
ICell cell = (ICell)row.GetCell(c); //存在cell为null的情况
object value = cell == null ? null : YZExcelHelper.GetCellValue(evaluator, cell, ignoreCellException);
if (!String.IsNullOrEmpty(Convert.ToString(value)))
{
containsValue = true;
}
string columnName = YZExcelHelper.ColumnIndexToName(c);
if (r == titleRowIndex)
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
dataColumn.Caption = Convert.ToString(value);
}
else if (r >= dataRowIndex)
{
if (!table.Columns.Contains(columnName))
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
}
if (dataRow == null)
{
dataRow = table.NewRow();
}
dataRow[columnName] = value;
if (c == row.LastCellNum - 1 && containsValue)
{
table.Rows.Add(dataRow);
}
}
}
}
}
}
return(dataset);
}
private IManualLayout GetEmptyLayout()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
return legend.GetManualLayout();
}
private List <AddVocabularyViewModel> GetVocabularyRows(IFormFile file)
{
var vocabularyRows = new List <AddVocabularyViewModel>();
const string folderName = "Upload";
var newPath = Path.Combine(_hostingEnvironment.WebRootPath, folderName);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
var sFileExtension = Path.GetExtension(file.FileName).ToLower();
var fileName = "VocabularyTemplate_" + DateTime.Now.ToFileTime() + sFileExtension;
var fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
ISheet sheet;
if (sFileExtension == ".xls")
{
var hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
var hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
var headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
var sentences = new List <SentenceViewModel>();
if (!string.IsNullOrWhiteSpace(row.GetCell(6).ToString()))
{
sentences.Add(new SentenceViewModel
{
SentenceExample = row.GetCell(6).ToString(),
});
}
if (!string.IsNullOrWhiteSpace(row.GetCell(7).ToString()))
{
sentences.Add(new SentenceViewModel
{
SentenceExample = row.GetCell(7).ToString(),
});
}
if (!string.IsNullOrWhiteSpace(row.GetCell(8).ToString()))
{
sentences.Add(new SentenceViewModel
{
SentenceExample = row.GetCell(8).ToString(),
});
}
var vocabularyRowDto = new AddVocabularyViewModel
{
Name = row.GetCell(0).ToString(),
Meaning = row.GetCell(1).ToString(),
MarathiMeaning = row.GetCell(2).ToString(),
Synonym = row.GetCell(3).ToString(),
Antonym = row.GetCell(4).ToString(),
Sentences = sentences,
SpeechPartId = 1,
TopicId = 1
};
vocabularyRows.Add(vocabularyRowDto);
}
}
return(vocabularyRows);
}
public static void DownLoad(List <LineResultInfo> resultInfos, string filePath)
{
//var mapperFirst = new Mapper();
//var data = LineResultInfoView.MapLineResultInfoView(resultInfos);
//var result = data.OrderBy(m => m.出库目的地).ThenBy(m => m.电缆型号).ToList();
//mapperFirst.Put(data, "线缆出库信息表", true);
//mapperFirst.Save(filePath);
//return;
IWorkbook wkbook = new XSSFWorkbook();
//2. 在该工作簿中创建工作表
ISheet sheet = wkbook.CreateSheet("线缆出库信息表");
// 向该工作表中插入行和单元格
var rowIndex = 0;
var colIndex = 0;
IRow firstRow = sheet.CreateRow(rowIndex++);
firstRow.CreateCell(0).SetCellValue("线缆出库信息一览表");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
IRow headRow = sheet.CreateRow(rowIndex++);
// 在该行中创建单元格
headRow.CreateCell(colIndex++).SetCellValue("出库目的地");
headRow.CreateCell(colIndex++).SetCellValue("电缆型号");
headRow.CreateCell(colIndex++).SetCellValue("生产厂家");
headRow.CreateCell(colIndex++).SetCellValue("线轮位置");
headRow.CreateCell(colIndex++).SetCellValue("是否整轮");
headRow.CreateCell(colIndex++).SetCellValue("出库长度");
headRow.CreateCell(colIndex++).SetCellValue("剩余长度");
//var lookUpLines = resultInfos.Where(m => m.CustomerId == "1").ToLookup(m => new { m.CustomerId });
var lookUpLines = resultInfos.ToLookup(m => new { m.CustomerId });
foreach (var lookUpCustomerLine in lookUpLines)
{//出库目的地
colIndex = 0;
IRow row = sheet.CreateRow(rowIndex);
var customerCell = row.CreateCell(colIndex++);
customerCell.SetCellValue(lookUpCustomerLine.Key.CustomerId);
var firstRowIndex = rowIndex;
var totalRowCount = 0;
var lookupXingHao = lookUpCustomerLine.ToLookup(m => new { m.XingHao });
var xinghaoFirst = true;
foreach (var xinghaoGroup in lookupXingHao)
{
var tmpCount = xinghaoGroup.Count();
totalRowCount += tmpCount;
if (xinghaoFirst)
{
xinghaoFirst = false;
var xingHaoCell = row.CreateCell(1);
xingHaoCell.SetCellValue(xinghaoGroup.Key.XingHao);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, firstRowIndex + totalRowCount - 1, 1, 1));
}
else
{
row = sheet.CreateRow(++rowIndex);
var xingHaoCell = row.CreateCell(1);
xingHaoCell.SetCellValue(xinghaoGroup.Key.XingHao);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, firstRowIndex + totalRowCount - 1, 1, 1));
}
var brandFirst = true;
//var newRowIndex = firstRowIndex + 1;
var tmpInx = 0;
var tmpList = xinghaoGroup.OrderBy(p => p.PaiHao).ThenBy(m => m.LunHao).ToList();
foreach (var lineResultInfo in tmpList)
{
tmpInx++;
colIndex = 2;
var paiLunHaoFlag = lineResultInfo.PaiHao.GetValueOrDefault() == 0 ||
lineResultInfo.LunHao.GetValueOrDefault() == 0;
var paiLunHaoCell = paiLunHaoFlag
? "库存不足"
: $"{lineResultInfo.PaiHao}排 {lineResultInfo.LunHao}轮";
if (brandFirst)
{
row.CreateCell(colIndex++).SetCellValue(lineResultInfo.Brand);
row.CreateCell(colIndex++).SetCellValue(paiLunHaoCell);
row.CreateCell(colIndex++).SetCellValue(lineResultInfo.IsWheel);
row.CreateCell(colIndex++).SetCellValue(lineResultInfo.Length.GetValueOrDefault());
row.CreateCell(colIndex++).SetCellValue(lineResultInfo.LeaveLength.GetValueOrDefault());
brandFirst = false;
}
else
{
//if (tmpInx != tmpCount)
// rowIndex++;
var newRow = sheet.CreateRow(++rowIndex);
newRow.CreateCell(colIndex++).SetCellValue(lineResultInfo.Brand);
newRow.CreateCell(colIndex++).SetCellValue(paiLunHaoCell);
newRow.CreateCell(colIndex++).SetCellValue(lineResultInfo.IsWheel);
newRow.CreateCell(colIndex++).SetCellValue(lineResultInfo.Length.GetValueOrDefault());
newRow.CreateCell(colIndex++).SetCellValue(lineResultInfo.LeaveLength.GetValueOrDefault());
}
}
}
sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, firstRowIndex + totalRowCount - 1, 0, 0));
rowIndex = firstRowIndex + totalRowCount;
}
var mapper = new Mapper(wkbook);
mapper.Save(filePath);
}
/// <summary>
/// 将DataTable中的数据导入Excel文件中
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void DataTable2Excel(DataSet ds, string filepath, string sheetName)
{
try
{
string FileExt = Path.GetExtension(filepath);
if (".xlsx".Equals(FileExt))
{
XSSFWorkbook workbook = new XSSFWorkbook();
foreach (DataTable table in ds.Tables)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
fs.Close();
}
}
else
{
HSSFWorkbook workbook = new HSSFWorkbook();
foreach (DataTable table in ds.Tables)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
fs.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//讀取Token值
string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim();
if (VeriftyToken(token))
{
//建立共用connection & transaction
SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
oConn.Open();
SqlCommand oCmd = new SqlCommand();
oCmd.Connection = oConn;
SqlTransaction myTrans = oConn.BeginTransaction();
oCmd.Transaction = myTrans;
//建立DataTable Bulk Copy用
DataTable dt = new DataTable();
dt.Columns.Add("Edu_CityNo", typeof(string)).MaxLength = 2;
dt.Columns.Add("Edu_CityName", typeof(string)).MaxLength = 10;
dt.Columns.Add("Edu_15upJSDownRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_15upJSDownRate", typeof(string)).MaxLength = 7;
dt.Columns.Add("Edu_15upHSRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_15upHSRate", typeof(string)).MaxLength = 7;
dt.Columns.Add("Edu_15upUSUpRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_15upUSUpRate", typeof(string)).MaxLength = 7;
dt.Columns.Add("Edu_ESStudentDropOutRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESStudentDropOutRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_JSStudentDropOutRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_JSStudentDropOutRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_ESStudentsYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESStudents", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_JSStudentsYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_JSStudents", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_HSStudentsYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_HSStudents", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESToHSIndigenousYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESToHSIdigenous", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESToHSIndigenousRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESToHSIndigenousRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_ESJSNewInhabitantsYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESJSNewInhabitants", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESJSNewInhabitantsRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESToJSNewInhabitantsRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_ESJSTeachersYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESJSTeachers", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESJSTeachersOfStudentRateYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESJSTeachersOfStudentRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_BudgetYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_Budget", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_BudgetUpRateYearDesc", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_BudgetUpRate", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_ESToHSAvgBudgetYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESToHSAvgBudget", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESJSPCNumYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESJSPCNum", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_ESJSAvgPCNumYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_ESJSAvgPCNum", typeof(string)).MaxLength = 20;
dt.Columns.Add("Edu_HighschoolDownRemoteAreasYear", typeof(string)).MaxLength = 3;
dt.Columns.Add("Edu_HighschoolDownRemoteAreas", typeof(string)).MaxLength = 50;
dt.Columns.Add("Edu_CreateDate", typeof(DateTime));
dt.Columns.Add("Edu_CreateID", typeof(string));
dt.Columns.Add("Edu_CreateName", typeof(string));
dt.Columns.Add("Edu_Status", typeof(string));
dt.Columns.Add("Edu_Version", typeof(int));
try
{
HttpFileCollection uploadFiles = Request.Files;//檔案集合
HttpPostedFile aFile = uploadFiles[0];
//判斷有沒有檔案
if (uploadFiles.Count < 1 || aFile.FileName == "")
{
throw new Exception("請選擇檔案");
}
//有檔案繼續往下做
if (uploadFiles.Count > 0)
{
string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName);
if (extension != ".xls" && extension != ".xlsx")
{
throw new Exception("請選擇xls或xlsx檔案上傳");
}
IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象
workbook = new XSSFWorkbook(aFile.InputStream);
ISheet sheet = workbook.GetSheetAt(0);//當前sheet
//簡易判斷這份Excel是不是教育的Excel
int cellsCount = sheet.GetRow(0).Cells.Count;
//1.判斷表頭欄位數
if (cellsCount != 21)
{
throw new Exception("請檢查是否為教育的匯入檔案");
}
//2.檢查欄位名稱
if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "15歲以上民間人口之教育程度結構-國中及以下" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "15歲以上民間人口之教育程度結構-高中(職)")
{
throw new Exception("請檢查是否為教育的匯入檔案");
}
//取得當前最大版次 (+1變成現在版次)
strMaxVersion = EN_DB.getMaxVersin() + 1;
//取得代碼檔
CodeTable_DB code_db = new CodeTable_DB();
DataTable dtCode = code_db.getCommonCode("02");
string cityNo = string.Empty;
//資料從第四筆開始 最後一筆是合計不進資料庫
for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++)
{
if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均")
{
DataRow row = dt.NewRow();
cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼
if (cityNo == "")
{
throw new Exception("第" + (j + 1) + "筆資料：" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱");
}
strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>";
row["Edu_CityNo"] = cityNo; //縣市代碼
row["Edu_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱
row["Edu_15upJSDownRateYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-國中及以下-資料年度
row["Edu_15upJSDownRate"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //15歲以上民間人口之教育程度結構-國中及以下
row["Edu_15upHSRateYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-高中(職)-資料年度
row["Edu_15upHSRate"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //15歲以上民間人口之教育程度結構-高中(職)
row["Edu_15upUSUpRateYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-大專及以上-資料年度
row["Edu_15upUSUpRate"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //15歲以上民間人口之教育程度結構-大專及以上
row["Edu_ESStudentDropOutRateYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //國小學生輟學率-資料年度
row["Edu_ESStudentDropOutRate"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //國小學生輟學率
row["Edu_JSStudentDropOutRateYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //國中學生輟學率-資料年度
row["Edu_JSStudentDropOutRate"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //國中學生輟學率
row["Edu_ESStudentsYear"] = sheet.GetRow(1).GetCell(6).ToString().Trim().Replace("年", ""); //國小總學生數-資料年度
row["Edu_ESStudents"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //國小總學生數
row["Edu_JSStudentsYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //國中總學生數-資料年度
row["Edu_JSStudents"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //國中總學生數
row["Edu_HSStudentsYear"] = sheet.GetRow(1).GetCell(8).ToString().Trim().Replace("年", ""); //高中(職)總學生數-資料年度
row["Edu_HSStudents"] = sheet.GetRow(j).GetCell(8).ToString().Trim(); //高中(職)總學生數
row["Edu_ESToHSIndigenousYear"] = sheet.GetRow(1).GetCell(9).ToString().Trim().Replace("年", ""); //國小-高中(職)原住民學生數-資料年度
row["Edu_ESToHSIdigenous"] = sheet.GetRow(j).GetCell(9).ToString().Trim(); //國小-高中(職)原住民學生數
row["Edu_ESToHSIndigenousRateYear"] = sheet.GetRow(1).GetCell(10).ToString().Trim().Replace("年", ""); //國小-高中(職)原住民學生數比例-資料年度
row["Edu_ESToHSIndigenousRate"] = sheet.GetRow(j).GetCell(10).ToString().Trim(); //國小-高中(職)原住民學生數比例
row["Edu_ESJSNewInhabitantsYear"] = sheet.GetRow(1).GetCell(11).ToString().Trim().Replace("年", ""); //國中小新住民人數-資料年度
row["Edu_ESJSNewInhabitants"] = sheet.GetRow(j).GetCell(11).ToString().Trim(); //國中小新住民人數
row["Edu_ESJSNewInhabitantsRateYear"] = sheet.GetRow(1).GetCell(12).ToString().Trim().Replace("年", ""); //國中小新住民學生比例-資料年度
row["Edu_ESToJSNewInhabitantsRate"] = sheet.GetRow(j).GetCell(12).ToString().Trim(); //國中小新住民學生比例
row["Edu_ESJSTeachersYear"] = sheet.GetRow(1).GetCell(13).ToString().Trim().Replace("年", ""); //國中小教師數-資料年度
row["Edu_ESJSTeachers"] = sheet.GetRow(j).GetCell(13).ToString().Trim(); //國中小教師數
row["Edu_ESJSTeachersOfStudentRateYear"] = sheet.GetRow(1).GetCell(14).ToString().Trim().Replace("年", ""); //國中小生師比(平均每位教師教導學生數)-資料年度
row["Edu_ESJSTeachersOfStudentRate"] = sheet.GetRow(j).GetCell(14).ToString().Trim(); //國中小生師比(平均每位教師教導學生數)
row["Edu_BudgetYear"] = sheet.GetRow(1).GetCell(15).ToString().Trim().Replace("年", ""); //教育預算-資料年度
row["Edu_Budget"] = sheet.GetRow(j).GetCell(15).ToString().Trim(); //教育預算
row["Edu_BudgetUpRateYearDesc"] = sheet.GetRow(1).GetCell(16).ToString().Trim().Replace("年", ""); //教育預算成長率-資料年度敘述
row["Edu_BudgetUpRate"] = sheet.GetRow(j).GetCell(16).ToString().Trim(); //教育預算成長率
row["Edu_ESToHSAvgBudgetYear"] = sheet.GetRow(1).GetCell(17).ToString().Trim().Replace("年", ""); //國小-高中(職)平均每人教育預算-資料年度
row["Edu_ESToHSAvgBudget"] = sheet.GetRow(j).GetCell(17).ToString().Trim(); //國小-高中(職)平均每人教育預算
row["Edu_ESJSPCNumYear"] = sheet.GetRow(1).GetCell(18).ToString().Trim().Replace("年", ""); //國中小教學電腦數-資料年度
row["Edu_ESJSPCNum"] = sheet.GetRow(j).GetCell(18).ToString().Trim(); //國中小教學電腦數
row["Edu_ESJSAvgPCNumYear"] = sheet.GetRow(1).GetCell(19).ToString().Trim().Replace("年", ""); //國中小平均每人教學電腦數-資料年度
row["Edu_ESJSAvgPCNum"] = sheet.GetRow(j).GetCell(19).ToString().Trim(); //國中小平均每人教學電腦數
row["Edu_HighschoolDownRemoteAreasYear"] = sheet.GetRow(1).GetCell(20).ToString().Trim().Replace("年", ""); //高級中等以下偏遠地區學校-資料年度
row["Edu_HighschoolDownRemoteAreas"] = sheet.GetRow(j).GetCell(20).ToString().Trim(); //高級中等以下偏遠地區學校
row["Edu_CreateDate"] = dtNow;
row["Edu_CreateID"] = LogInfo.mGuid; //上傳者GUID
row["Edu_CreateName"] = LogInfo.name; //上傳者姓名
row["Edu_Status"] = "A";
row["Edu_Version"] = strMaxVersion;
dt.Rows.Add(row);
}
}
if (dt.Rows.Count > 0)
{
strErrorMsg = "";
BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料
DoBulkCopy(myTrans, dt); //匯入
myTrans.Commit(); //最後再commit
}
}
}
catch (Exception ex)
{
strErrorMsg += "錯誤訊息:" + ex.Message + "<br>";
strErrorMsg += "(欄位名稱請參考上傳範例檔)";
myTrans.Rollback();
}
finally
{
oCmd.Connection.Close();
oConn.Close();
if (strErrorMsg == "")
{
/// Log
idl_db._IDL_Type = "ISTI";
idl_db._IDL_IP = Common.GetIPv4Address();
idl_db._IDL_Description = "檔案類別:教育 , 狀態：上傳成功";
idl_db._IDL_ModId = LogInfo.mGuid;
idl_db._IDL_ModName = LogInfo.name;
idl_db.addLog();
Response.Write("<script type='text/JavaScript'>parent.feedbackFun('教育匯入成功');</script>");
}
else
{
/// Log
idl_db._IDL_Type = "ISTI";
idl_db._IDL_IP = Common.GetIPv4Address();
idl_db._IDL_Description = "檔案類別:教育 , 狀態：上傳失敗";
idl_db._IDL_ModId = LogInfo.mGuid;
idl_db._IDL_ModName = LogInfo.name;
idl_db.addLog();
Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>");
}
}
}
}
/// <summary>
/// 导出多个工作簿
/// </summary>
/// <param name="dicSheet">工作簿名：数据表</param>
/// <param name="fullPathName">物理路径 + 文件名称 + 格式</param>
/// <returns></returns>
public static bool DataTableToExcel(Dictionary <string, DataTable> dicSheet, string fullPathName)
{
try
{
IWorkbook workbook = new HSSFWorkbook();
if (fullPathName.ToLower().Contains(".xlsx"))
{
workbook = new XSSFWorkbook();
}
foreach (var sheetitem in dicSheet.Keys)
{
var dt = dicSheet[sheetitem];
ISheet sheet = workbook.CreateSheet(sheetitem);
//标题样式
ICellStyle hStyle = workbook.CreateCellStyle();
hStyle.BorderBottom = BorderStyle.Thin;
hStyle.BorderLeft = BorderStyle.Thin;
hStyle.BorderRight = BorderStyle.Thin;
hStyle.BorderTop = BorderStyle.Thin;
//水平垂直居中
hStyle.Alignment = HorizontalAlignment.Center;
hStyle.VerticalAlignment = VerticalAlignment.Center;
//背景颜色
hStyle.FillForegroundColor = 9;
hStyle.FillPattern = FillPattern.SolidForeground;
////用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
headerRow.Height = 20 * 20;
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
//单元格字体
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
font.Color = 8;
font.Boldweight = (short)FontBoldWeight.Bold;
hStyle.SetFont(font);
cell.CellStyle = hStyle;
icolIndex++;
}
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//创建CellStyle与DataFormat并加载格式样式
IDataFormat dataformat = workbook.CreateDataFormat();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = dataformat.GetFormat("@");
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
DataRow.Height = 20 * 16;
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
}
//写Excel
using (FileStream file = new FileStream(fullPathName, FileMode.OpenOrCreate))
{
workbook.Write(file);
}
return(true);
}
catch (Exception)
{
return(false);
}
}