/// <summary>
/// open an excel file,then write the content to file
/// </summary>
/// <param name="FileName">file name</param>
/// <param name="findString">first cloumn</param>
/// <param name="replaceString">second cloumn</param>
private void WriteToExcel(List <List <string> > listError)
{
Workbook workbook = new Workbook();
//Load an excel file including pivot table
workbook.LoadFromFile(this.attrDir + "\\属性错误参考.xls");
//Modify data of data source
Worksheet datasheet = workbook.Worksheets[0];
int maxrow = datasheet.Rows.Count() + 1;
int startIndex = maxrow;
foreach (List <string> item in listError)
{
datasheet.Range[maxrow, 1].Text = item[0];
datasheet.Range[maxrow, 2].Text = item[1];
datasheet.Range[maxrow, 3].Text = item[2];
datasheet.Range[maxrow, 4].Text = item[3];
maxrow++;
}
CellRange rng = datasheet.Range["A" + startIndex + ":D" + (maxrow - 1)];
rng.ColumnWidth = 30;
rng.BorderAround(LineStyleType.Double, Color.Black);
rng.BorderInside(LineStyleType.Thin, Color.Black);
rng.Style.Font.Size = 11;
rng.Style.HorizontalAlignment = HorizontalAlignType.Center;
rng.Style.HorizontalAlignment = HorizontalAlignType.Center;
workbook.Save();
}
public ActionResult BillReport(DateTime begintime, DateTime endtime)
{
var keyValue = Common.GuId();
int number = 3;
var dataItems = (Dictionary <string, object>) new ItemsDetailApp().GetDataItemList();
var companyId = OperatorProvider.Provider.GetCurrent().CompanyId;
var payList = _payOrderApp.GetList(begintime, endtime, companyId);
string tmpRootDir = Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString()); //获取程序根目录
string template = tmpRootDir + "/Areas/PaymentManage/Template/通用打印模板.xls".Replace(@"/", @"\"); //转换成绝对路径
Workbook workbook = new Workbook();
workbook.LoadFromFile(template);
//将第一张工作表保存为图片
Worksheet sheet = workbook.Worksheets[0];
sheet.Rows[0].Cells[0].Text = new OrganizeApp().GetForm(OperatorProvider.Provider.GetCurrent().CompanyId).F_FullName + "销售记录";
sheet.Rows[1].Cells[1].Text = begintime.ToString("yyyy-MM-dd");
sheet.Rows[1].Cells[3].Text = endtime.ToString("yyyy-MM-dd");
//Add style of specified range
CellStyle style = workbook.Styles.Add("Style");
style.Font.Size = 10;
if (payList.Count > 0)
{
sheet.InsertRow(4, payList.Count);
foreach (var item in payList)
{
sheet.Rows[number].Cells[0].Text = item.F_UserCard ?? "-";
sheet.Rows[number].Cells[1].Text = item.F_MeterCode ?? "-";
sheet.Rows[number].Cells[2].Text = item.F_CustomerName ?? "-";
sheet.Rows[number].Cells[3].Text = item.F_CustomerAddress ?? "-";
sheet.Rows[number].Cells[4].Text = ((Dictionary <string, string>)dataItems["DeviceType"])[item.F_MeterType];
sheet.Rows[number].Cells[5].Text = item.F_PayTotal.ToString("0.00");
sheet.Rows[number].Cells[6].Text = item.F_Money == null ? "0" : ((decimal)(item.F_Money)).ToString("0.00");
sheet.Rows[number].Cells[7].Text = item.F_PaymentTime.Value.ToString("yyy-MM-dd");
sheet.Rows[number].Cells[8].Text = item.F_CreatorUserName ?? "-";
for (int i = 0; i <= 8; i++)
{
sheet.Rows[number].Cells[i].RowHeight = 15;
sheet.Rows[number].Cells[i].Style = style;
}
number++;
}
CellRange range = sheet.Range["A4:I" + (3 + payList.Count).ToString()];
range.BorderInside(LineStyleType.Thin, Color.Black);
range.BorderAround(LineStyleType.Thin, Color.Black);
}
//string imageUrl = tmpRootDir + "/Areas/PaymentManage/Template/Report/" + keyValue + ".jpg".Replace(@"/", @"\");
//string imageUrl2 = tmpRootDir + "/Areas/PaymentManage/Template/Report2/" + keyValue + ".jpg".Replace(@"/", @"\");
//sheet.SaveToImage(imageUrl);
string filpath = tmpRootDir + "/Areas/PaymentManage/Template/Report/" + keyValue + ".cshtml".Replace(@"/", @"\");
sheet.SaveToHtml(filpath);
// ImageHelper.CaptureImage(imageUrl, imageUrl2, 723, 90 + payList.Count * 22, 53, 73);
TempData["keyValue"] = keyValue;
return(View());
}
/// <summary>
/// 添加边框
/// </summary>
/// <param name="cells"></param>
public static CellRange StyleLine(this CellRange cells)
{
//cells.Style.Borders.LineStyle = LineStyleType.Medium;
cells.BorderInside(LineStyleType.Thin);
cells.BorderAround(LineStyleType.Thin);
cells.Borders.KnownColor = ExcelColors.Black;
return(cells);
}
/// <summary>
/// If the supplied excel File does not exist then Create it
/// </summary>
/// <param name="FileName"></param>
private void CreateExcelFile()
{
if (System.IO.File.Exists(this.attrDir + "\\属性错误参考.xls"))
{
System.IO.File.Delete(this.attrDir + "\\属性错误参考.xls");
}
//create
object Nothing = System.Reflection.Missing.Value;
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
CellRange rng = sheet.Range["A1:D1"];
rng.ColumnWidth = 30;
rng.BorderAround();
rng.Style.Font.Size = 14;
rng.Style.HorizontalAlignment = HorizontalAlignType.Center;
rng.Style.HorizontalAlignment = HorizontalAlignType.Center;
//rng.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
sheet.Range["A1"].Text = "检查类型";
sheet.Range["B1"].Text = "检查项";
sheet.Range["C1"].Text = "错误数量";
sheet.Range["D1"].Text = "参考sql";
workbook.SaveToFile(this.attrDir + "\\属性错误参考.xls");
}
private bool GetArchContenWriteXlsPrint(int archid)
{
DataTable ArchConten = Common.GetPrintConten(archid, ClsPrintConten.PrintContenTable, ClsPrintConten.PrintContenCol, ClsPrintConten.PrintContenPagesn);
if (ArchConten == null || ArchConten.Rows.Count <= 0)
{
string str = "ID号:" + archid + "目录获取失败!";
WriteLog(str);
return(false);
}
int countpage = Common.GetArchPages(archid);
try {
Workbook work = new Workbook();
Worksheet wsheek = null;
work.LoadFromFile(Path.Combine(Application.StartupPath, "目录.xls"));
wsheek = work.Worksheets[0];
string strsn = ClsPrintConten.PrintContenSn;
//获取起始行和列
int rowsn = 0;
int colsn = 0;
if (strsn.Trim().Length > 1)
{
rowsn = Convert.ToInt32(strsn.Remove(0, 1));
colsn = ClsInfo.ToNum(strsn.Substring(0, 1)) + 1;
}
int arow = 0;
int bcol = 0;
int dz = 0;
for (int i = 0; i < ArchConten.Rows.Count; i++)
{
dz = 0;
for (int j = 0; j < ArchConten.Columns.Count; j++)
{
string str = ArchConten.Rows[i][j].ToString();
if (j == 4)
{
str = str.PadLeft(3, '0');
}
if (j == 1)
{
string s = str.Substring(0, 1);
if (s.Contains("\\") || s.Contains("/"))
{
str = str.Substring(1, str.Length - 1);
}
s = str.Substring(str.Length - 1, 1);
if (s.Contains("\\") || s.Contains("/"))
{
str = str.Substring(0, str.Length - 1);
}
}
if (rowsn > 0)
{
wsheek.Range[rowsn + i, colsn].Text = (i + 1).ToString();
}
if (ClsPrintConten.printContenXls.Count > 0)
{
if (dz < ClsPrintConten.printContenXls.Count)
{
arow = Convert.ToInt32(ClsPrintConten.printContenXls[dz].Remove(0, 1));
bcol = ClsInfo.ToNum(ClsPrintConten.printContenXls[dz].Substring(0, 1)) + 1;
}
if (dz < ClsPrintConten.PrintContenPage.Count && ClsPrintConten.PrintContenPage[dz] == "True")
{
if (ClsPrintConten.PrintContenPageMode == 2)
{
int p = Convert.ToInt32(str);
int p1 = 0;
try {
if (i != ArchConten.Rows.Count - 1)
{
p1 = Convert.ToInt32(ArchConten.Rows[i + 1][j].ToString());
if (p == p1)
{
str = p + "-" + p1;
}
else
{
str = p + "-" + (p1 - 1);
}
}
else
{
p1 = countpage;
if (p == p1)
{
str = p + "-" + p1;
}
else
{
str = p + "-" + (p1 - 1);
}
}
} catch {
string str1 = "ID号:" + archid + "目录页码不正确";
WriteLog(str1);
return(false);
}
}
else
{
if (i == ArchConten.Rows.Count - 1)
{
//int p = Convert.ToInt32(str);
int p1 = countpage;
str = str.PadLeft(3, '0') + "-" + p1.ToString().PadLeft(3, '0');
}
}
wsheek.Range[arow + i, bcol].Text = str;
}
//else if (dz < ClsPrintConten.PrintContenDz.Count && ClsPrintConten.PrintContenDz[dz] == "True")
// wsheek.Range[arow + i, bcol].Text = str;
//else if (arow == i && j == bcol) {
// wsheek.Range[arow + i, bcol].Text = str;
//}
else if (ClsPrintConten.PrintContenDz[dz] == "False")
{
string s = ClsPrintConten.printContenXls[dz].ToString();
if (str.Trim().Length > 0)
{
wsheek.Range[s].Text = str.PadLeft(4, '0');
}
}
else if (ClsPrintConten.PrintContenDz[dz] == "True")
{
wsheek.Range[arow + i, bcol].Text = str;
}
}
dz += 1;
}
}
rowsn = wsheek.LastRow;
string fontname = wsheek.Rows[6].Cells[3].Style.Font.FontName;
double fontsize = wsheek.Rows[6].Cells[3].Style.Font.Size;
CellRange range = wsheek.Range["A6" + ":H" + rowsn];
range.BorderInside(LineStyleType.Thin, ExcelColors.Black);
range.BorderAround(LineStyleType.Thin, ExcelColors.Black);
range.Style.Font.Size = fontsize;
range.Style.Font.FontName = fontname;
range.HorizontalAlignment = HorizontalAlignType.Center;
range.VerticalAlignment = VerticalAlignType.Center;
range.Style.WrapText = true;
range.AutoFitRows();
for (int i = 6; i < rowsn; i++)
{
double row = wsheek.Rows[i].RowHeight;
if (row < 50)
{
wsheek.Rows[i].SetRowHeight(50, true);
}
wsheek.Range["E" + (i + 1) + ":F" + (i + 1)].Merge();
}
wsheek.PageSetup.PrintTitleRows = "$1:$5";
work.PrintDocument.Print();
return(true);
} catch (Exception e) {
string str = "ID号:" + archid + ":" + e;
WriteLog(str);
return(false);
}
}
public static void Builde()
{
// 从文件中读取名字
// 第一行为标题行
//string path = Environment.CurrentDirectory;
FileStream stream = new FileStream("names.txt", FileMode.Open);
StreamReader reader = new StreamReader(stream);
List <string> names = new List <string>();
while (reader.Peek() >= 0)
{
names.Add(reader.ReadLine());
}
reader.Close();
string title = names[0];
names.RemoveAt(0);
//创建xlsx文档，使用Spire.XLS
Workbook workbook = new Workbook();
workbook.CreateEmptySheet();
Worksheet worksheet = workbook.Worksheets[0];
//首先确定要生成数据表的行数和列数
WriteLine("Please input the DateTime you want create?");
//初始的时间是程序运行时间的上一个月。
int year = DateTime.Now.Year;
int month = DateTime.Now.AddMonths(-1).Month;
int tempYear = year;
int tempMonth = month;
do
{
Write($"Please input the year:(default:{year})");
try
{
tempYear = Convert.ToInt32(ReadLine());
}
catch (Exception)
{
tempYear = year;
}
Write($"Please input the month:(default:{month})");
try
{
tempMonth = Convert.ToInt32(ReadLine());
}
catch (Exception)
{
tempMonth = month;
}
} while ((tempYear <= 0) || (tempMonth <= 0) || (tempMonth > 12));
year = tempYear;
month = tempMonth;
//获取当月的天数
int days = DateTime.DaysInMonth(year, month);
//将要填充的数据先放到一个Object类型的二维数组当中
int row = names.Count + 2;
int col = days + 1;
object[,] myData = new object[row, col];
//填充值班数据
for (int j = 1; j < row; j++)
{
for (int i = 1; i < col; i++)
{
myData[j, i] = "";
}
}
myData[0, 0] = "姓名";
myData[row - 1, 0] = "备注";
myData[row - 1, 1] = "加班☆；值班△；休假□；请事（病）假★。";
//从json文件中读取节假日数据
string configPath = string.Format("holiday.json");
HolidayChecker checker = new HolidayChecker(configPath);
for (int i = 1; i <= days; i++)
{
myData[0, i] = $"{i}日";
worksheet.SetColumnWidth(i + 1, 3.5);
//修改颜色
DateTime currentDay = new DateTime(year, month, i);
DateType currentDayType = checker.CheckDayType(currentDay);
if ((currentDayType is DateType.Weekend) || (currentDayType is DateType.Holiday))
{
string range = String.Format("{0}1:{0}{1}", NumberCharsConvert.NumberToChars(i + 1), names.Count - 1);
worksheet.Range[range].Style.Color = (currentDayType is DateType.Weekend ? Color.Gray : Color.Red);
}
}
for (int j = 1; j <= names.Count; j++)
{
myData[j, 0] = names[j - 1];
worksheet.SetRowHeight(j + 1, 40);
}
worksheet.InsertArray(myData, 1, 1);
//整理表格格式
//设置列宽和行高
worksheet.SetRowHeight(1, 40);
// worksheet.SetRowHeight(names.Count + 2, 40);
// worksheet.Rows[row-1].RowHeight = 40;
//worksheet.Rows[row-1].Style.Color = Color.;
worksheet.SetColumnWidth(1, 6);
//设置边框
CellRange xlsRange = worksheet.Range[$"A1:{NumberCharsConvert.NumberToChars(col)}{row}"];
xlsRange.BorderInside(LineStyleType.Thin, Color.Black);
xlsRange.BorderAround(LineStyleType.Thin, Color.Black);
//设置对齐方式
xlsRange.Style.HorizontalAlignment = HorizontalAlignType.Center;
xlsRange.Style.VerticalAlignment = VerticalAlignType.Center;
//合并单元格
worksheet.Range[$"B{names.Count + 2}:{NumberCharsConvert.NumberToChars(days + 1)}{names.Count + 2}"].Merge();
worksheet.Range[$"B{names.Count + 2}"].HorizontalAlignment = HorizontalAlignType.Left;
//设置打印页面格式为A4
worksheet.PageSetup.PaperSize = PaperSizeType.PaperA4;
//set header&footer
worksheet.PageSetup.CenterHeader = $"{title}{year}年{month}月考勤登记表";
worksheet.PageSetup.LeftFooter = "制表人： 政工复核： 审核人：";
//保存文件
workbook.SaveToFile(@"..\..\" + $"{year}年{month}月值（加）班考勤表.xlsx", ExcelVersion.Version2016);
}
/// <summary>
/// Создание страницы с модулями IO
/// </summary>
private static void CreateModulesPage(string prjName,
ref Workbook workBook)
{
string sheetName = "Модули ввода-вывода";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
var modulesCount = new Dictionary <string, int>();
var modulesColor = new Dictionary <string, System.Drawing.Color>();
var asInterfaceConnection = new Dictionary <string, object[, ]>();
object[,] res = ExcelDataCollector.SaveIOAsConnectionArray(prjName,
modulesCount, modulesColor, asInterfaceConnection);
workSheet.InsertArray(res, 1, 1);
int finalRows = res.GetLength(0) + 2;
//Форматирование страницы
workSheet.Range.BorderInside(LineStyleType.Thin);
workSheet.Range.BorderAround(LineStyleType.Medium);
workSheet.Range.Style.VerticalAlignment = VerticalAlignType
.Center;
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.IsWrapText = false;
CellRange rangeCurrent = workSheet.Range["A1:A1"];
CellRange rangeStart = rangeCurrent;
int totalCountRows = workSheet.Range.Rows.Length;
int i = 1;
string arr2 = rangeCurrent.Text as string;
do
{
int startColumn = rangeCurrent.Column;
int startRow = rangeCurrent.Row;
rangeCurrent = workSheet.Range[startRow + 1, startColumn,
startRow + 1, startColumn];
string arr1 = rangeStart.Value as string;
arr2 = rangeCurrent.Value as string;
if (arr1 != arr2)
{
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column].Merge();
CellRange moduleNameRange = workSheet.Range[rangeStart.Row,
rangeStart.Column + 1, rangeStart.Row,
rangeStart.Column + 1];
string moduleName = moduleNameRange.Value as string;
if (modulesColor.ContainsKey(moduleName))
{
moduleNameRange.Style.Color = modulesColor[moduleName];
}
if (Int32.TryParse(arr1, out _))
{
workSheet.Range[rangeStart.Row, rangeStart.Column + 1,
rangeCurrent.Row - 1, rangeCurrent.Column + 1]
.Merge();
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column + 5]
.BorderAround(LineStyleType.Thick);
}
else
{
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column + 5]
.Borders.LineStyle = LineStyleType.None;
}
rangeStart = rangeCurrent;
}
i++;
}while (i <= totalCountRows);
// Форматирование по ширине содержимого.
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.Range.EntireColumn.IsWrapText = true;
CellRange column = workSheet.Range[$"B2:B{finalRows}"];
column.Style.Rotation = 90;
// 6.43 - 50 пикселей
column.ColumnWidth = 6.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"A2:A{finalRows}"];
//26.43 - 190 пикселей
column.ColumnWidth = 26.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"C2:C{finalRows}"];
// 2.14 - 20 пикселей
column.ColumnWidth = 6.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
int totalStart = totalCountRows + 3;
int totalEnd = totalStart;
int idx = 0;
int total = 0;
//Выделение отдельным блоком модулей AS-interface
if (asInterfaceConnection.Count != 0)
{
var ASInterface = new object[
asInterfaceConnection.Count * 130, 4];
idx = 0;
ASInterface[idx, 0] = "AS-interface/IO-Link";
idx++;
foreach (string key in asInterfaceConnection.Keys)
{
ASInterface[idx, 0] = key;
idx++;
int startColumn = 2;
object[,] connections = asInterfaceConnection[key];
int rows = connections.GetLength(0);
int cols = connections.GetLength(1);
for (int ii = 0; ii < rows; ii++)
{
bool notNull = true;
for (int jj = 0; jj < cols; jj++)
{
if (connections[ii, jj] != null)
{
ASInterface[idx, startColumn + jj] =
connections[ii, jj];
}
else
{
notNull = false;
}
}
if (notNull)
{
idx++;
}
}
}
totalEnd = totalStart + idx;
workSheet.InsertArray(ASInterface, totalStart, 1);
column = workSheet.Range[$"A{totalStart}:A{totalEnd}"];
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"C{totalStart}:C{totalEnd}"];
column.HorizontalAlignment = HorizontalAlignType.Center;
totalStart = totalEnd + 2;
}
//Создание сводной таблицы используемых модулей на основании словаря
object[,] modulesTotal = new object[modulesCount.Count + 1, 2];
idx = 0;
total = 0;
// Заполнение таблицы
foreach (string key in modulesCount.Keys)
{
modulesTotal[idx, 0] = key;
modulesTotal[idx, 1] = modulesCount[key];
total += modulesCount[key];
idx++;
}
modulesTotal[idx, 0] = "Всего:";
modulesTotal[idx, 1] = total;
//Форматирование таблицы
totalEnd = totalStart + modulesCount.Count;
rangeCurrent = workSheet.Range[$"A{totalStart}:B{totalEnd}"];
workSheet.InsertArray(modulesTotal, totalStart, 1);
rangeCurrent.Style.Rotation = 0;
rangeCurrent.VerticalAlignment = VerticalAlignType.Center;
rangeCurrent.HorizontalAlignment = HorizontalAlignType.Right;
rangeCurrent.BorderInside(LineStyleType.Thin);
rangeCurrent.BorderAround(LineStyleType.Medium);
rangeCurrent = workSheet.Range[$"A{totalStart}:A{totalStart}"];
rangeStart = rangeCurrent;
workSheet.Range.EntireRow.AutoFitRows();
// Окрас ячеек
i = totalStart;
arr2 = rangeCurrent.Text as string;
do
{
int startColumn = rangeCurrent.Column;
int startRow = rangeCurrent.Row;
rangeCurrent = workSheet.Range[startRow + 1, startColumn,
startRow + 1, startColumn];
string arr1 = rangeStart.Value as string;
arr2 = rangeCurrent.Value as string;
if (arr1 != arr2)
{
CellRange moduleNameRange = rangeStart;
string moduleName = moduleNameRange.Value as string;
if (modulesColor.ContainsKey(moduleName))
{
moduleNameRange.Style.Color = modulesColor[moduleName];
}
rangeStart = rangeCurrent;
}
i++;
}while (i <= totalEnd);
rangeCurrent = null;
rangeStart = null;
}
